关于MYSQL null值的一些探讨

关于MYSQL字段null值，我们都不会陌生，诸如我们会用下面类似的SQL语句建表，为了业务需要（或者某种便捷），会给某些字段默认值为NULL（或者说曾经有设置过默认值为NULL的情况）

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` longtext NOT NULL,
  `school` char(25) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

为什么我们会或多或少接触过NULL值列呢？请看下列解释：

Null is a special constraint of columns. The columns in table will be added null constrain if you do not define the column with "not null" key words explicitly when creating the table. Many programmers like to define columns by default

because of the conveniences(reducing the judgement code of nullibility) what consequently cause some uncertainty of query and poor performance of database.

划重点：NULL值是一种对列的特殊约束，我们创建一个新列时，如果没有明确的使用关键字not null声明该数据列，MYSQL会默认的为我们添加上NULL约束（也就是如果我们不指定not null, 都会默认为default null）。
但是关于NULL值，实际使用起来，往往觉得自己思考的不够。最近在给项目中的表默认NULL值的字段做改造，先说说做这个改造的原因（每次接手一个活，都会问几个问题：1.为什么要做这个？2.可以怎么做？3.哪种最好？4.各有什么优劣点）。

下面我们先看看原因：

1. 我先建一张表（设置name字段为default null），然后往表插入一些测试数据：

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` longtext NOT NULL,
  `school` char(25) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from test;
+----+---------+-------------+----------+
| id | name    | description | school   |
+----+---------+-------------+----------+
|  1 | NULL    |             | NULL     |
|  2 | NULL    |             |          |
|  3 | NULL    |             |          |
|  5 | NULL    |             |          |
|  6 | clark   |             |          |
|  7 | bluse   |             |          |
|  8 | raya    |             |          |
|  9 | spark   |             |          |
| 10 | NULL    |             |          |
| 11 | NULL    |             |          |
+----+---------+-------------+----------+
10 rows in set (0.00 sec)

2. 我们进行一些查询：

A. not like 场景：

mysql> select * from test where `name` not like 'clark%';
+----+--------+-------------+----------+
| id | name   | description | school   |
+----+--------+-------------+----------+
|  7 | bluse  |             |          |
|  8 | raya   |             |          |
|  9 | spark  |             |          |
+----+--------+-------------+----------+

B. not in 场景：

mysql> select * from test where `name` not in ('clark', 'raya');
+----+--------+-------------+----------+
| id | name   | description | school   |
+----+--------+-------------+----------+
|  8 | bluse  |             |          |
|  9 | spark  |             |          |
+----+--------+-------------+----------+
2 rows in set (0.00 sec)

C. 不等于场景：

mysql> select * from test where `name` <> 'clark';
+----+--------+-------------+----------+
| id | name   | description | school   |
+----+--------+-------------+----------+
|  7 | bluse  |             |          |
|  8 | raya   |             |          |
|  9 | spark  |             |          |
+----+--------+-------------+----------+
3 rows in set (0.00 sec)

根据上述场景，我们发现一个现象，我们无论是用不等于、还是用not like, 或者用not in ，都没有办法把name=NULL值的记录查出来，为什么会这样呢？因为NULL代表一个不确定的值，就算是两个NULL值比较也不一定相等。

mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

可能有同学要提出疑问了，null is null不就相等吗？

mysql> select null is null;
+--------------+
| null is null |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

确实，null is null 返回的是true, 但是其实可以理解上述语句的语义问的是"一个未知的东西是不是未知的东西"，那结论是肯定的。

MYSQL 其实只提供了三种操作符对null 值进行运算："is null", "is not null", "<=>"，外加一个ifnull()函数。相信is null和is not null我们都太熟悉了，这里就不介绍了，这里着重介绍一下"<=>"(太空船操作符)

先上官方的说明：<=> ：NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.（NULL安全相等，这个操作符执行一个相等比较就像"="操作符一样，但是不同的是，如果两个计算对象都是NULL的话，返回1, 如果一个计算对象为NULL的话，返回0）。下面我们用这个"<=>"操作符来测试下：

mysql> select * from test where name <=> null;
+----+------+-------------+----------+
| id | name | description | school  |
+----+------+-------------+----------+
|  1 | NULL |             | NULL     |
|  2 | NULL |             |          |
|  3 | NULL |             |          |
|  5 | NULL |             |          |
| 10 | NULL |             |          |
| 11 | NULL |             |          |
+----+------+-------------+----------+
6 rows in set (0.00 sec)

2. 了解null值带来的问题后，接下来是怎么做？要改哪些类型字段呢？

经过一些思考和讨论后，结论是仅改varchar, char, text类型的字段，将varchar, char, text字段设置成not null default ''。先简单说明下，为什么其他字段不用改，首先date或者datetime, 如果给默认值的话，不允许给空字符串（字段类型约束如此），如果要给默认值只能给'0000-00-00或0000-00-00 00:00:00'，想想这个默认值，长得丑，处理起来也不方便，再者从业务场景来说，也不存在查询一个时间为空的场景（当然我说的是我自己的项目）。int或者float 类型字段如果给默认值，不给null的话，通常给0之类的，但是在业务场景下，需要区别一个真正的用户是没填还是真的填写了0，所以也不宜给默认值，当然int和float类型要避免null的话，也是可以做的，比如用一个-1或-999不常用的值来代替null未填写的含义，不过这样的话，需要业务上去做兼容。

对于varchar, char, text这三种类型字段，可能会有人质疑text给默认值会有问题？我测试了下，下面给一下测试结果：

当我给text字段设置一个非空默认值时：

mysql> alter table test modify column `description` text not null default 'hello';
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'description' can't have a default value

当我给text字段设置一个空默认值时：

mysql> alter table test modify column `description` text not null default '';
Query OK, 10 rows affected, 1 warning (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 1

当然可能跟sql_mode有关（所以说不讲条件前提，说text类型字段不能设置默认值为空字符串是不准确的），当sql_mode为STRICT_TRANS_TABLES , BLOB、TEXT、GEOMETRY、JSON 这些类型的字段是不能设置默认值的，我的sql_mode如下：

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.01 sec)

所以基于上述测试，只要目标实例的sql_mode跟上面相同的话，text字段设置空字符串默认值也是没有问题的．但是有一点需要注意的是：text字段无论是设置成default null　还是default '', 在phpmyadmin的structure的结果里，Default列都是会显示成"None"。

那null值除了查询带来些问题之外，还有哪些不好的地方呢？

1.干扰排序，分组，去重结果，排序时，升序，NULL排最前

2.使用集函数时带来的结果不符合预期

3.等等等等.....

既然NULL有这么多缺点，那它有什么优点呢？

1.节约硬盘空间

2.符合其语义的使用场景

以上就是我对MySQL NULL值的一些理解，如有不对之处，请指正。

---

欢迎关注我的个人公众号沟通与交流，可以微信搜索“码农漫谈”