MySQL NOT NULL列用 WHERE IS NULL 也能查到資料的原因

czxin788發表於2022-07-01
MySqlNull

現象

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部落格 ” ，連結：http://blog.itpub.net/28916011/viewspace-2903819/，如需轉載，請註明出處，否則將追究法律責任。

相關文章