NOT NULL列用IS NULL也能查到數(shù)據(jù)？
技術(shù)小能手?2018-05-07 09:17:13?瀏覽200?評(píng)論0
摘要：?測(cè)試表DDL CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB; 插入測(cè)試數(shù)據(jù)： yejr@imysql.
測(cè)試表DDL
CREATE TABLE `t1`(`id`int(11)DEFAULTNULL,`dt` datetime NOTNULLDEFAULT'0000-00-00 00:00:00')ENGINE=InnoDB;
插入測(cè)試數(shù)據(jù)：
yejr@imysql.com>insert intot1(id)select1;---不指定dt列的值yejr@imysql.com>insert into t1 select2,now();---指定dt列的值為now()yejr@imysql.com>insert intot1(id)select3;---不指定dt列的值
查詢(xún)數(shù)據(jù)：
yejr@imysql.com>select*from t1 where dt is null;+------+---------------------+|id|dt|+------+---------------------+|1|0000-00-0000:00:00||3|0000-00-0000:00:00|+------+---------------------+2rows in set(0.00sec)
有沒(méi)有覺(jué)得很奇怪，為什么查到了2條 dt 列值為 '0000-00-00 00:00:00' 的記錄？
先查看執(zhí)行計(jì)劃：
yejr@imysql.com>desc select*from t1 where dt is null\G***************************1.row***************************id:1select_type:SIMPLE table:t2 partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:5filtered:20.00Extra:Using where1row in set,1warning(0.00sec)yejr@imysql.com>show warnings\G***************************1.row***************************Level:Note Code:1003Message:/* 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')
發(fā)現(xiàn)?IS NULL?條件被轉(zhuǎn)換了，所以才能查到結(jié)果，這是為什么呢？ 我嘗試了調(diào)整SQL_MODE，發(fā)現(xiàn)并沒(méi)什么卵用，最后還是在官方文檔找到了答案：
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 ISNULL
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.
原文發(fā)布時(shí)間為：2018-05-5
本文作者：葉師傅春茶開(kāi)售啦
