MySQL中NULL的处理和特性

NULL Value

NULL 表示不存在的未知的值

NULL means "a missing unknown value" or "not having a value"

操作NULL

使用 is null 和 is not null 操作符及ifnull 函数

特性

• 对NULL使用算术运算符，例如= < > <>，都会返回NULL（无意义的结果）

  mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
  +----------+-----------+----------+----------+
  | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
  +----------+-----------+----------+----------+
  |     NULL |      NULL |     NULL |     NULL |
  +----------+-----------+----------+----------+
  

• 任何包含NULL 的表达式都会返回NULL，除非文档中针对表达式中涉及的运算符和函数另有说明

  An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression

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

• 在MySQL 中，0 和 NULL 被视为 false，其他值被视为true（布尔操作符返回1）

• InnoDB引擎支持在包含NULL的列上建立索引

• 在 group by order by distinct 中，两个NULL 是相等的

• 在 order by 中，升序排序时，NULL 排在第一位；降序排序时，NULL排在最后

• 在 where 中，算术运算符对NULL而言，返回的都是NULL，而NULL被视为false，因此不满足条件，不会返回

• 具有NOT NULL 约束的列，可以插入0 和 ""

  mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
  +-----------+---------------+------------+----------------+
  | 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
  +-----------+---------------+------------+----------------+
  |         0 |             1 |          0 |              1 |
  +-----------+---------------+------------+----------------+
  

• 聚合函数COUNT() MIN() SUM会忽略NULL

  特别的COUNT(*) ，计算的是行数，而非列值

• 字段类型是 TIMESTAMP 的列，插入NULL 会插入当前日期和时间

  5.7版本，实际测试插入的还是NULL

占用空间

针对 InnoDB引擎

• An SQL NULL value reserves one or two bytes in the record directory. An SQL NULL value reserves zero bytes in the data part of the record if stored in a variable-length column. For a fixed-length column, the fixed length of the column is reserved in the data part of the record. Reserving fixed space for NULL values permits columns to be updated in place from NULL to non-NULL values without causing index page fragmentation.

  https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

• A LENGTH of NULL is NULL, a LENGTH of an empty string is 0.

NULL和空字符串的区别

插入

mysql> INSERT INTO my_table (phone) VALUES (NULL); -- 插入 NULL 
mysql> INSERT INTO my_table (phone) VALUES (''); -- 插入 "" （空字符串）

• 插入 NULL 值，表示手机号是「未知状态」（不知道有没有手机号）
• 插入空字符串，表示「没有手机号」

查询

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';

参考

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