MySQL删除语句报告Truncated incorrect datetime value

Question

这个语句工作正常:

SELECT * FROM table_name
WHERE DATE(date_event) < DATE(NOW() - INTERVAL 90 DAY);

在 DELETE 语句中使用相同的 WHERE 子句不起作用:

DELETE FROM table_name 
WHERE DATE(date_event) < DATE(NOW() - INTERVAL 90 DAY);

返回信息:

Error Code: 1292. Truncated incorrect datetime value: '2018-01-10T13:22:29.000000Z'

字段“date_event”的数据类型是 CHAR(27)。

使用下一个 SQL 语句测试 DATE() 函数工作正常:

SELECT DATE('2018-01-10T13:22:28.000000Z');

'2018-01-10'

这里是完整的 SQL 代码:

CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_event` char(27) NOT NULL COMMENT 'YYYY-MM-DDThh:mm:ss.ffffffZ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Test';

INSERT INTO table_name
(date_event) VALUES ('2018-01-10T13:22:28.000000Z');

SELECT * FROM table_name
WHERE DATE(date_event) < DATE(NOW() - INTERVAL 90 DAY);

DELETE FROM table_name
WHERE DATE(date_event) < DATE(NOW() - INTERVAL 90 DAY);

Answer 1

date_event 字段为Char(字符串类型)。我们需要将其转换为 MySQL datetime格式使用 Str_to_Date()功能:

'2018-01-10T13:22:28.000000Z' 基本上可以用格式说明符写成:'%Y-%m-%dT%T.%fZ '.

因此，您的删除 查询应如下所示 ( DB Fiddle DEMO ):

DELETE FROM table_name
WHERE STR_TO_DATE(date_event, '%Y-%m-%dT%T.%fZ') < DATE(NOW() - INTERVAL 90 DAY);

而且，您的Select 查询应该是:

SELECT * FROM table_name
WHERE STR_TO_DATE(date_event, '%Y-%m-%dT%T.%fZ') < DATE(NOW() - INTERVAL 90 DAY);

其他详细信息:

• %Y 4 位数字的年份
• %m 月份名称作为数值(00 到 12)
• %d 一个月中的第几天作为数值(01 到 31)
• %T 24 小时格式的时间 (hh:mm:ss)
• %f 微秒(000000 到 999999)

格式说明符的完整列表可见于:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format