关于MYSQL null值的一些探讨

大家好，今天本人给大家带来文章《关于MYSQL null值的一些探讨》，文中内容主要涉及到MySQL，如果你对数据库方面的知识点感兴趣，那就请各位朋友继续看下去吧~希望能真正帮到你们，谢谢！

关于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

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.符合其语义的使用场景

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

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。