[测试表DDL
CREATE TABLE t1 (
id int(11) DEFAULT NULL,
dt datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB;
插入测试数据：
yejr@imysql.com> insert into t1(id) select 1; --- 不指定dt列的值
yejr@imysql.com> insert into t1 select 2, now(); --- 指定dt列的值为now()
yejr@imysql.com> insert into t1(id) select 3; --- 不指定dt列的值 查询数据：
yejr@imysql.com> select * from t1 where dt is null; +------+---------------------+ | id | dt | +------+---------------------+ | 1 | 0000-00-00 00:00:00 | | 3 | 0000-00-00 00:00:00 | +------+---------------------+ 2 rows in set (0.00 sec) 有没有觉得很奇怪，为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录？
先查看执行计划：
yejr@imysql.com> desc select * from t1 where dt is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 20.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
yejr@imysql.com> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select yejr.t1.id AS id,yejr.t2.dt AS dt from yejr.t1 where (yejr.t1.dt = '0000-00-00 00:00:00')
发现 IS NULL 条件被转换了，所以才能查到结果，这是为什么呢？ 我尝试了调整SQL_MODE，发现并没什么卵用，最后还是在官方文档找到了答案：
For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
SELECT * FROM tbl_name WHERE date_column IS NULL This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.
See Obtaining Auto-Increment Values, and the description for the FLAG_AUTO_IS_NULL option at Connector/ODBC Connection Parameters.
原文发布时间为：2018-05-5 本文作者：叶师傅春茶开售啦 本文来自云栖社区合作伙伴“老叶茶馆”，了解相关信息可以关注“老叶茶馆”。请添加链接描述](http://click.aliyun.com/m/49386/)
