专栏首页「3306 Pai」社区NOT NULL列用IS NULL也能查到数据?
原创

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

导读

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' 的记录?

先查看执行计划:

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。

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 新特性解读 | MySQL 8.0 索引特性1-函数索引

    函数索引顾名思义就是加给字段加了函数的索引,这里的函数也可以是表达式。所以也叫表达式索引。

    [3306 Pai ] 社区
  • InnoDB索引允许NULL对性能有影响吗

    结论4,定义列值允许为NULL并不会增加物理存储代价,但对索引效率的影响要另外考虑

    [3306 Pai ] 社区
  • 怎么让MySQL自动化平台再快10倍?

    我的前文《构建MySQL自动化平台思路》中提过的MySQL平台自第一期上线后,一直都是顺风顺水的。

    [3306 Pai ] 社区
  • Asp.NET Core2.0 项目实战入门视频课程_完整版

    看到这个标题,你开不开心,激不激动呢? 没错,.net core的入门课程已经完毕了。52ABP.School项目从11月19日,第一章视频的试录制,到今天完...

    角落的白板报
  • C语言使用libmodbus库的Modbus TCP协议读取设备中的数据

    libmodbus是一个跨平台的modbus C语言库,目前支持的平台有:Linux, Mac OS X, FreeBSD, QNX以及Windows,其官网是...

    ccf19881030
  • 二进制文件处理之尾部补0和尾部去0

    有时候我们需要在某个二进制文件的尾部增加一些字节,使文件大小对齐到某个边界,以便满足某些操作的需求。例如某个文件下一步的写入操作需要块对齐。

    zqb_all
  • OP_RETURN誓言墙

    我们可以在比特币交易中附加上一个OP_RETURN输出,OP_RETURN可以填入任意字符,可以是誓言,可以是证据,也可以是情人节表白,一经写入,就会永久保存在...

    申龙斌
  • 高效的选择:将键盘上的大小写锁定键 CapsLock 与退出键 Esc 交换位置

    如果你习惯使用 Shift 切换大小写,那么在你左手小指处的 caps lock 大小写锁定键几乎没有用武之地。

    Piper蛋窝
  • Windows 10 键盘 ALT 键与 WIN 键颠倒

    最近重新装了家里台式机电脑系统,但是发现键盘左侧和右侧的 ALT 与 WIN 键功能都颠倒了,搜索了很久资料也没有找到为什么会出现这种情况,但是解决办法还是有的...

    我与梦想有个约会
  • MySQL binlog event 详解

    我也是只菜鸡,blog写的不对或者不严谨的地方还请大伙指出来,我及时改正,免得误人子弟。

    二狗不要跑

扫码关注云+社区

领取腾讯云代金券