现象
mysql用where is null查出的结果，和我们预期不符，如下：
mysql> select * from t where create_time is null; +----+----------+---------------------+ | id | name | create_time | +----+----------+---------------------+ | 1 | zhangsan | 0000-00-00 00:00:00 | | 2 | lisi | 0000-00-00 00:00:00 | +----+----------+---------------------+ 2 rows in set (0.00 sec)
分析
该表结构为：
mysql> show create table t \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL, `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'kong', `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB 1 row in set (0.00 sec)
该表数据为：
mysql> insert into t values (1,'zhangsan');
Query OK, 1 row affected (0.10 sec)
mysql> insert into t values (2,'lisi');
Query OK, 1 row affected (0.11 sec
mysql> insert into t values ('4','wangwu','2022-07-01 10:00:00');
Query OK, 1 row affected (0.11 sec)
mysql> select * from t;
+----+----------+---------------------+
| id | name | create_time |
+----+----------+---------------------+
| 1 | zhangsan | 0000-00-00 00:00:00 |
| 2 | lisi | 0000-00-00 00:00:00 |
| 4 | wangwu | 2022-07-01 10:00:00 |
+----+----------+---------------------+
3 rows in set (0.00 sec)
用where is null查数据，竟然可以查到0000-00-00 00:00:00的数据，和我们预期不符：
mysql> select * from t where create_time is null; +----+----------+---------------------+ | id | name | create_time | +----+----------+---------------------+ | 1 | zhangsan | 0000-00-00 00:00:00 | | 2 | lisi | 0000-00-00 00:00:00 | +----+----------+---------------------+ 2 rows in set (0.00 sec)
分析该sql执行计划：
mysql> desc select * from t where create_time is null \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t 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)
看到该sql有warning，查看一下：
mysql> show warnings; SELECT `czx`.`t`.`id` AS `id`, `czx`.`t`.`name` AS `name`, `czx`.`t`.`create_time` AS `create_time` FROM `czx`.`t` WHERE ( `czx`.`t`.`create_time` = '0000-00-00 00:00:00') 1 row in set (0.00 sec)
可以看到，mysql把where create_time is null ，偷偷的转换成了，where create_time = '0000-00-00 00:00:00'，这是为什么呢？
带着疑问查阅mysql官方文档，得到如下答案：
大意是，对于日期类型，MySQL会把0000-00-00，当成null值处理。这么做的原因是，odbc不支持0000-00-00的日期数据。
官方文档还有如下说明：
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
大意是，MySQL允许存储0000-00-00作为假日期，这比用null更加方便，但是odbc会自动将zero date转为null。
结论
MySQL会将0000-00-00这样的日期当做null对待，所以用where is null查询可以显示他们。
如果不想要这样的现象，建议可以不在日期里面存储zero date，可以用当前时间处理默认值，比如：
alter table t add column create_time datetime not null default CURRENT_TIMESTAMP;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28916011/viewspace-2903819/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mysql 并行复制原理
请登录后发表评论
登录
全部评论
