Can the not NULL column also find data with IS NULL?

Test Table DDL

CREATE TABLE ' t1 ' (
 ' id ' int () default NULL,
 ' DT ' datetime NOT NULL DEFAULT ' 0000-00-00 00:00:00 '
) engine=i Nnodb;

To insert test data:

yejr@imysql.com> INSERT into T1 (ID) select 1; ---do not specify a value for the DT column

yejr@imysql.com> insert INTO T1 select 2, now ();---the value of the specified DT column is now ()

yejr@imysql.com> Insert int o T1 (ID) Select 3; ---do not specify a value for the DT column

Query data:

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)

Did you find it strange why we found 2 dt column value ' 0000-00-00 00:00:00 ' record.

Review the execution plan first:

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 ')

It was found that the is NULL condition was converted so that the result could be traced. I tried adjusting the sql_mode, found no eggs, and finally found the answer in the official document:

For date and DATETIME columns that are declared as isn't NULL, you can find the special DATE ' 0000-00-00 ' by using a stateme NT 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.

Obtaining Auto-increment Values, and the description for the Flag_auto_is_null option at Connector/odbc Connection Par Ameters.
The original release time is: 2018-05-5 This article author: Leaf Master Spring Opens the sale this article from the cloud Habitat community partner "old Leaf Teahouse", understands the related information may concern "the old leaf teahouse".