mysql 明明设置了not null default，为什么用where name is null还能查到null值

我来答

czxin788

2022-07-01

mysql

mysql 明明设置了not null default，为什么用where name is null还能查到数据null值

见下面的例子

mysql> create table t (id int primary key, name varchar(20) not null default 'kong');
Query OK, 0 rows affected (0.56 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>  select * from t;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> alter table t add column create_time datetime not null default '0000-00-00 00:00:00';
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

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)

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)

很不解呀，明明create_time列有值0000-00-00 00:00:00，但是select * from t where create_time is null 却认为有null值。

请问这是怎么回事

我来答

添加附件

问题补充

9条回答

默认

最新

严少安

当前 sql_mode 是什么？
select @@sql_mode;

可以用，
alter table t add column create_time datetime not null default CURRENT_TIMESTAMP;

有用 1

chengang

官方文档有这样一句话。
MySQL permits you to store a “zero” value of ‘0000-00-00’ as a “dummy date.” In some cases, this is more convenient than using NULL values, and uses less data and index space. To disallow ‘0000-00-00’, enable the NO_ZERO_DATE mode.

“Zero” date or time values used through Connector/ODBC are converted automatically to NULL because ODBC cannot handle such values.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

有用 2

czxin788

mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

有用 0

czxin788

意思是说，mysql会强制把zero date转为null，因为odbc不能处理zero date，会吧zero date自动转换为null

有用 0

严少安

2022-07-01

datetime 类型的取值范围是，The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 而 '0000-00-00 00:00:00' 是非法数值。

严少安

2022-07-01

而非法数值就会判定为null

czxin788

看了一下执行计划

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)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                          |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ 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)