MySQL中DATETIME字段出现0000-00-00的异常排查问题

一、0000日期是怎么插入的 #

• 这些0000-00-00 00:00:00是插入早于MySQL DATETIME最小值的带时区日期时，被系统自动转换而来的。
• MySQL的DATETIME类型有效范围是1000-01-01 00:00:00到9999-12-31 23:59:59。你插入的'0003-01-26 00:00:00+00:00'远早于最小值，当时插入的会话未开启NO_ZERO_DATE和严格模式（STRICT_TRANS_TABLES/STRICT_ALL_TABLES），MySQL不会直接报错，而是自动把这个无效日期转换为0000-00-00 00:00:00存入表中。
• 现在你的会话开启了NO_ZERO_DATE模式，所以直接插入或查询零日期都会报错，但之前生成的零日期已经留在表内。

二、如何找到对应行 #

有两种可行的定位方法：

方法一：临时关闭NO_ZERO_DATE模式查询 #

先临时修改当前会话的SQL模式，关闭NO_ZERO_DATE：

SET sql_mode = REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');

之后即可直接查询零日期对应的行：

SELECT * FROM articles WHERE date_published = '0000-00-00 00:00:00';

方法二：用范围查询（无需修改SQL模式） #

这些零日期本质是早于DATETIME最小值的无效值，直接用范围条件就能定位：

SELECT * FROM articles WHERE date_published < '1000-01-01 00:00:00';

这个查询不会触发零日期相关报错，能直接找出所有包含无效早日期的行。

内容的提问来源于stack exchange，提问作者albertjan