关于mysql:关于MYSQL-null值的一些探讨

11次阅读

共计 4689 个字符，预计需要花费 12 分钟才能阅读完成。

对于 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. 各有什么优劣点）。

上面咱们先看看起因：

我先建一张表（设置 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)

咱们进行一些查问：

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)

理解 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 值的一些了解，如有不对之处，请斧正。

欢送关注我的集体公众号沟通与交换，能够微信搜寻“码农漫谈”

正文完