MySQL NULL 相关问题

[yahuian]

本文 MySQL version 8.0，InnoDB 引擎

特性

• 创建表时，字段默认允许 NULL（这应该是关于 NULL 的一切问题的起源 hhh），显示增加 NOT NULL 声明才会禁止该字段值为 NULL

• NULL 和空字符，零值不一样，定义一个字段 NOT NULL 不代表不能插上 “” 0

• 不能使用 = < > != 等算数比较运算符，需要使用 IS NULL 和 IS NOT NULL

• NULL 和任意值比较或者操作结果都是 NULL

mysql> SELECT NULL, 1=NULL, 1+NULL, CONCAT('Invisible',NULL);
+------+--------+--------+--------------------------+
| NULL | 1=NULL | 1+NULL | CONCAT('Invisible',NULL) |
+------+--------+--------+--------------------------+
| NULL |   NULL |   NULL | NULL                     |
+------+--------+--------+--------------------------+

• 默认情况使用 DISTINCT, GROUP BY, ORDER BY 时，所有的 NULL 值都会视为相等（可以通过 innodb-stats-method 参数修改）

• ORDER BY ASC 时 NULL 排在最前面，ORDER BY DESC 时 NULL 排在最后面

• 类似 COUNT(), MIN(), SUM() 等聚合函数会忽略 NULL 值（注意 COUNT (*) 例外，它只计算行数，而不计算单个列的值。）

mysql> SELECT * FROM demo;
+----+-------+
| id | phone |
+----+-------+
|  1 | 1     |
|  2 | NULL  |
+----+-------+

mysql> SELECT COUNT(*) FROM demo;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

mysql> SELECT COUNT(phone) FROM demo;
+--------------+
| COUNT(phone) |
+--------------+
|            1 |
+--------------+

• 对于添加了 AUTO_INCREMENT 属性的字段，即使你设置了 NULL，MySQL 依旧会使用下一个数字插入

• Under certain conditions, if you insert NULL into a TIMESTAMP column, the current date and time is inserted; this behavior depends in part on the server SQL mode (see Section 5.1.11, “Server SQL Modes”) as well as the value of the explicit_defaults_for_timestamp system variable.

• 使用 InnoDB 引擎时，可以给包含 NULL 值的列添加索引，对于存在 NULL 的列，索引依旧是有效的，不用担心，此外一些场景 MySQL 内部对 IS NULL/IS NOT NULL 查询会做优化的，具体可以看下面参考资料

结论

引入 NULL 会有一些小问题或者额外的写法，所以

• 如果没有必须设置 NULL 的情况（设置了唯一索引，但是允许该字段不填，这时必须使用 NULL），一般都设置为 NOT NULL

• 不用特意设置 DEFAULT 默认值，在创建时可以由程序指定 “”, 0 等零值（某些 ORM 可能会自动忽略零值，此时插入数据会报错，需要注意一下）

参考

https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html

https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_method

[yahuian]

• 联合唯一索引中如何某个字段允许为 NULL，则唯一性会失效

create table `user` (
  `id` bigint unsigned not null auto_increment primary key,
  `name` varchar(100) not null comment '名称',
  `deleted_at` datetime null comment '删除时间',
  unique index `user-name-deleted_at-unique-index`(`name`,`deleted_at`)
) comment = '用户';