NOT NULL列用IS NULL也能查到数据?

  • NOT NULL列用IS NULL也能查到数据?已关闭评论
  • 3 views
  • A+
所属分类:MySQL

 

摘要: 测试表DDL CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB; 插入测试数据: yejr@imysql.

 

测试表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.

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
avatar