我怀疑这是bug ？NOT NULL列用 IS NULL 也能查到数据！

点击上方 "程序员小乐"关注, 星标或置顶一起成长

每天凌晨00点00分, 第一时间与你相约

每日英文

Time won't ease your pain but let you get used to it. 

时间不是让人忘了痛，而是让人习惯了痛。

每日掏心话

学会承受痛苦。有些事情，只适合烂在心里，适合无声无息的忘记。

责编：乐乐

链接：urlify.cn/n2i6vm

程序员小乐(ID:study_tech)第 960 次推文  图源：百度

往日回顾：分布式之分布式事务、分布式锁、分布式Session

     

   正文   

datetime列设置了NOT NULL约束，但查询条件IS NULL却能返回结果，奇怪吗？

测试表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' 的记录？

关注公众号程序员小乐回复关键字“offer”获取算法面试题和答案。

先查看执行计划：

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.

文档出自：12.3.2 Comparison Functions and Operators, https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html

啊，对了，MySQL版本是 5.7.21。

最后的结论告诉我们，遇到问题时，查询官档是有多么重要

欢迎在留言区留下你的观点，一起讨论提高。如果今天的文章让你有新的启发，欢迎转发分享给更多人。欢迎加入程序员小乐技术交流群，在后台回复“加群”或者“学习”即可。

猜你还想看

阿里、腾讯、百度、华为、京东最新面试题汇集

如何保证缓存与数据库的双写一致性？

拥抱并行流，提高程序执行速度

永远不要在代码中使用「User」这个单词！

关注订阅号「程序员小乐」，收看更多精彩内容

嘿，你在看吗？