关于 SQL，感觉自己学了假书

我相信关系型数据库应该是每个计算机学子必修的大课，由于这门课的实用价值比操作系统、编译原理高得多，我当时还是比较认真地去学的。但我相信只要上过这课的同学就能感觉出来，这门课掺杂了太多”数据模型的三要素、概念模型与逻辑模型的区别“等理论知识，也掺杂了太多”请用 SQL 查询选了张老师至少一门专业课且没有不及格课程的男同学的按生日年份分组的平均身高“这样的堪比脑筋急转弯的 SQL 应用题，乃至于真到实践环节时，脑袋里的东西既尾大不掉，又捉襟见肘。

由于一些原因，我并没有在工作中真正的用关系型数据库做过什么东西，毕竟 Mongo 太好用了，毕竟 Redis 太好用了，毕竟 Elasticsearch 也太好用了。而 SQL 逐渐成了一项宝宝会但宝宝不说的东西，只在心里面想：“SQL 我还能不会用哒？”

最近打脸了，我还真感觉自己不大懂 SQL，甚至感觉自己学了假书，我发现有一些相当重要的问题和技巧，即使还没有完全沉淀成知识放进书本，也应当放到台面上说说才对，而不至于让我接二连三得踩雷、蹚浑水。于是，我把我自己碰到的让人掉下巴的东西理了理，可能对你有帮助。

SQL 也支持 JSON

是的，SQL 也开始拥抱 JSON 了。MySQL 从 5.7.8 开始，SQL Server 从 2016 开始，PostgreSQL 从 9.4 开始，都开始不同程度地支持 JSON 操作，甚至支持将 JSON 作为其原生的数据类型。这篇《MySQL 5.7 新特性 JSON 的创建，插入，查询，更新》充分展示了这件事诱人的魅力：

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
|  2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM lnmp WHERE category->>'$.name' = 'lnmp.cn';
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

JSON 这一数据类型，跟关系型数据库的表设计模式完全唱着反调，连第一范式（列的原子性，列不能够再分成其他几列）都满足不了了，为啥会被融合到 SQL 里呢？

我的理解是，现实世界中，指望一个实体的字段类型全都是简单类型是非常不现实的，举例来说，一个人的名字可以用一个 string 表示，但一个人的爱好可能有多个，于是便不得不用 list string 来表示，糟糕，SQL 里可没有能表示 list string 的类型，你不得不再建一张“爱好表”，再建一张“人员-爱好-关系表”，可问题是爱好只是作为一个人的附属信息仅供展示，需求里并没有“查询爱好为 XXX 的人员列表”这样的情况，多建出来的两张表纯粹为了规避数据类型的不健全，也导致查询一个人的完整资料时需要联合 N 多张表，当一个人还有太多这样 list string 的属性时，性能是非常堪忧的。你可能会说，那就不用这么麻烦了，就使用 string 来表示，用逗号分隔不同爱好不就行了？形如旅游,写作,宠物这样？我非常赞同你的想法，但我还想补充一下，规范成这样如何：["旅游", "写作", "宠物"]？

用逗号分隔也好，用 JSON 格式的数组也好，其需要都是希望能在一个字段里塞入复杂类型的数据。同理，当要表示一个实体的更多的背景信息，可能还需要不确定 key 值、不确定结构的 map 数据，这些数据也是仅供读而不供查询的，为其拆出数量庞大的表更加不现实，这个时候我会本能的将其格式化成 JSON 塞到一个字段里。

我的做法是在数据库里使用 blob 类型，对应代码里 DO 层的数据类型是 []byte，再 json.Unmarshal 成 DTO 层的真实的 []string、[]int、struct 等类型。但我很快就反悔了，不仅是反悔这样做，也反悔之前说的话：“这些字段只是用来读，不作为查询条件”——一些特定时候，我还是希望能将 JSON 作为查询条件的，我清楚这样做无异于挨个儿扫描，性能不佳，但大多数时候其他筛选条件已经将目标范围缩小到几十条甚至几条记录了，而基于 JSON 的条件只需完成最后的那一小截筛选工作，而不至于迫使我只得把备选记录全 select 出来，映射到业务代码里，再在业务代码里去筛选。

所以如果我将数据库里的字段类型改为 JSON，再设法解决“数据库 - ORM - DO - 业务代码”这一条线上的所有阻碍，将其打通后，相信可能很多恶心的查询需求都将变得优雅几分。

时间不止可以精确到秒

我一直以为 SQL 提供的关于时间的数据类型，无论是 TIME、DATETIME 还是 TIMESTAMP，都是只能精确到秒的，但要知道“秒”这个时间单位对于计算机来说还是相当大的时间单位，一秒钟插入上千条数据简直是小菜一碟，于是我不得不硬着头皮骗自己：这上千条数据是“同时”产生的，没办法，关于时间我只能追踪到秒级。

然而，这原来也是老黄历了，主流的数据库实现已经早早开始支持将时间精度提升到毫秒级甚至微秒级，不过默认是不启用的，这也是一般情况下我见不到它的原因，我可以现在演示以下我是如何被蒙蔽双眼的：

mysql> create table test1 (id int auto_increment, create_at_1 TIME, create_at_2 DATETIME, create_at_3 TIMESTAMP, primary key (id));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into test1 (create_at_1, create_at_2, create_at_3) values (NOW(), NOW(), NOW());
Query OK, 1 row affected (0.25 sec)

mysql> select * from test1;
+----+-------------+---------------------+---------------------+
| id | create_at_1 | create_at_2         | create_at_3         |
+----+-------------+---------------------+---------------------+
|  1 | 12:50:43    | 2019-06-23 12:50:43 | 2019-06-23 12:50:43 |
+----+-------------+---------------------+---------------------+
1 row in set (0.04 sec)

你也看到了，哪有毫秒？哪有微秒？你也会认为 SQL 的时间只支持精确到秒的是吧？

那要如何提高时间精度呢，答案是：TIME(6), DATETIME(6), TIMESTAMP(6)。朋友们呀，我第一次看到时真是瞎了狗眼哇，时间也能指定长度的哇？长度是 6 的时间是啥子鬼哇？

相信你已经猜到了，后面括号里的数字不是指“时间的长度”，而是指保留秒之后多少位，3 便是保留到毫秒，6 便是保留到微秒。

所以正确的打开姿势是这样：

mysql> create table test2 (id int auto_increment, create_at_1 TIME(6), create_at_2 DATETIME(6), create_at_3 TIMESTAMP(6), primary key (id));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into test2 (create_at_1, create_at_2, create_at_3) values (NOW(6), NOW(6), NOW(6));
Query OK, 1 row affected (0.38 sec)

mysql> select * from test2;
+----+-----------------+----------------------------+----------------------------+
| id | create_at_1     | create_at_2                | create_at_3                |
+----+-----------------+----------------------------+----------------------------+
|  1 | 12:58:45.857522 | 2019-06-23 12:58:45.857522 | 2019-06-23 12:58:45.857522 |
+----+-----------------+----------------------------+----------------------------+
1 row in set (0.04 sec)

我服气了。

括号里的数字，各种各样的意思

VARCHAR(6) 里的 6 表示字符串长度，TIME(6) 里的 6 表示精确到微秒，你是不是多少闻到些诡异的气息了？可这还没完，还有这样的：

INT(5)
BIGINT(20)
TINYINT(30)

是的，数字也能指定“长度”，但这里的长度可不是存储的位数长度，INT(5) 完全可以存储大于 5 位的数，TINYINT(30) 则不可能存得下 30 位长的大数。

多于这些括号里诡异的数字，我总结了一下，基本可以分成 5 种情况：

1. 整数型，如 INT(M)、BIGINT(M)、TINYINT(M)，M 表示数字打印时的长度；
2. 浮点数或定点数，如 DECIMAL(M,D)、FLOAT(M,D)、DOUBLE(M,D)，M 表示一共可以存多少位数（包括小数点前和小数点后），D 表示小数点之后可以存多少位数；
3. 时间类型，如 TIME(fsp)、DATETIME(fsp)、TIMESTAMP(fsp)，fsp 表示保留多少位秒级以下的数字，
4. 定长字符串类型，如 CHAR(M)、BINARY(M)，M 表示字符串的准确长度；
5. 变长字符串类型，如 VARCHAR(M)、VARBINARY(M)、TEXT(M)，M 表示字符串的最大长度。

其他的都好好说，唯独整数型的“表示数字打印时的长度”让人费解，这个作用未免也太鸡肋了，谁会在乎数据打印的长度哇，难不成 INT(5) 类型的 12 要展示成 00012 或 ___12 吗？况且数据已经映射到业务代码里了，这个信息早已丢失了，你还指望 golang 在 printf INT(5) 时，能自动 format 成 %5d 吗？那还见了鬼了。

所以 MySQL 明确已经在 8.0.17 里弃用了这个特性，所以为了给大伙儿看看这个特性的作用，我尝试在 5.7 上做实验演示，但不知道哪个环节弄错了，括号里的数字并没有起作用：

mysql> create table test3 (id int auto_increment, num_1 INT(20), num_2 INT(10), num_3 INT(5), primary key (id));
Query OK, 0 rows affected (0.58 sec)

mysql> insert into test3 (num_1, num_2, num_3) values (2147483647, 2147483647, 2147483647);
Query OK, 1 row affected (0.08 sec)

mysql> insert into test3 (num_1, num_2, num_3) values (1, 1, 1);
Query OK, 1 row affected (0.16 sec)

mysql> select * from test3;
+----+------------+------------+------------+
| id | num_1      | num_2      | num_3      |
+----+------------+------------+------------+
|  1 | 2147483647 | 2147483647 | 2147483647 |
|  2 |          1 |          1 |          1 |
+----+------------+------------+------------+
2 rows in set (0.03 sec)

随它去吧，没人在乎。

没有 bool 类型

SQL 没有 bool 类型确实是让人跌眼镜的，我很奇怪为什么我此前一直未察觉。

但准确得说，应该是部分数据库产品没有严格意义上的 bool 类型。

何以为“严格意义上的 bool 类型”呢，PostgreSQL 从 9.4 开始，给出了一个答案：

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

首先，它叫 boolean 而不是其他东西，其次，它只占用一个 bit，最后，它打印出来是 t/f 而不是 1/0。

但对于 SQL Server 或 MySQL，这件事就是奢求了，一般都是建议拿 TINYINT(1)、BIT、ENUM('TRUE', 'FALSE') 来替代。

还是拿 MySQL 开刀，虽然它提供了数据类型 BOOL，但它其实是 TINYINT(1) 的别名，且会自动转换关键字 “TRUE/FALSE” 为对应的 1 或 0。虽说无伤大雅，但总感觉有一点怪怪的：

mysql> create table test4 (id int auto_increment, ok bool, primary key (id));
Query OK, 0 rows affected (0.96 sec)

mysql> insert into test4 (ok) values (TRUE);
Query OK, 1 row affected (0.04 sec)

mysql> insert into test4 (ok) values (FALSE);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test4 (ok) values (0);
Query OK, 1 row affected (0.04 sec)

mysql> insert into test4 (ok) values (1);
Query OK, 1 row affected (0.07 sec)

mysql> insert into test4 (ok) values (2);
Query OK, 1 row affected (0.23 sec)

mysql> insert into test4 (ok) values (3);
Query OK, 1 row affected (0.08 sec)

mysql> select * from test4;
+----+------+
| id | ok   |
+----+------+
|  1 |    1 |
|  2 |    0 |
|  3 |    0 |
|  4 |    1 |
|  5 |    2 |
|  6 |    3 |
+----+------+
6 rows in set (0.04 sec)

2 和 3 也能被插进到 BOOL 型的字段里，这不扯淡么这。

谜之 rows affected

前文演示的各种 SQL 操作时，数据库都有贴心得返回“XX row affected”这样的反馈，你有注意到过它们吗？反正大多数时候我只是把它们当成纯粹为了提升用户体验的友情提示，未曾真正利用过它们，也就没有好好理解过它们。

直到一个小 bug 的出现，我才意识到自己疏忽了。

有一个这个的需求：将某条记录的状态值更新为 A，但有个约束条件，就是如果状态已经处于 B 了，便不可以更新，需要提示用户操作错误。

当时想当然地写出了类似如下的 SQL（实际上是写 ORM 层的业务代码，不是直接写 SQL，这里为了方便阐述）：

update test5 set status = 'A' where id = 1 and status != 'B';

执行后，读取 rows affected，如果是 1，说明操作有效，如果为 0，则说明可能是没找到 id 等于 1 的记录，但这在当时的情景中是不可能的；那么就只能是另一种情况，id 等于 1 的记录的 status 是等于 B 的，这才导致 where 条件不匹配， rows affected 为 0，所以这个操作不合法，可以提示用户操作失败了。

很遗憾，这是个错误的做法，且我花费了大量时间才排查出问题所在。我给你演示一下什么情况下会有问题：

mysql> select * from test5;
+----+--------+
| id | status |
+----+--------+
|  1 | A      |
+----+--------+
1 row in set (0.03 sec)

mysql> update test5 set status = 'A' where id = 1 and status != 'B';
Query OK, 0 rows affected (0.03 sec)

如你所见，如果 status 已经为 A 了，再 set 成 A 是不会计入到 rows affected 中的，这里 rows affected 的实际含义是“筛选条件匹配且真实发生了数据更新的记录数”。既然如此，上述通过“rows affected 是否为 0”来判断用户操作是否合法自然是错误的，但你是疑虑，一个如此容易发现的问题，为何会困顿了我那么久呢，我再给你看个宝贝儿：

这是用 DataGrid 在同一张表上执行相同的的 SQL 操作的结果，注意看，反馈信息里显示的是 “1 row affected”。这大大误导了我，也导致我在排查问题时，并未怀疑自己对 row affected 的理解是否正确，从而绕了相当大的圈子。

为了方便下面叙述，我们暂且称呼“满足筛选条件的记录数”为 matched 数，称呼“满足筛选条件且真实发生了数据变更的记录数”为 changed 数。

那 rows affected 究竟是表示 matched 还是表示 changed 呢？答案是，都可以。

数据库 client 与 server 端建立连接时，可以指定各种可选参数，比如常见的 charset、parseTime 等，其中有个参数便是 clientFoundRows，来指示 update 操作返回的 rows affected 究竟是 matched 还是 changed。大多数语言的驱动，包括 golang 的 github.com/go-sql-driver/mysql，clientFoundRows 的默认值都是 false，可偏偏 java 的 connector/j 从 5.1 开始，默认将 clientFoundRows 设为 true，而 DataGrid 又是基于 java 开发的，这便导致上述两次实验时，出现两中不同的实验结果。

最后，我们放弃了基于 row affected 来实现业务逻辑，而使用了排它锁 SELECT … FOR UPDATE 实现了需求。

谜之 NULL

我猜你早已养成了习惯，在 SQL 的 where 条件里，你可能会写 where status = 'A' 或 where status != 'A'，但绝不会写 where status = NULL 或 where status != NULL 对不？这是正确的，如果你没有这个习惯，那赶紧改一下自己的代码，与 NULL 做比较的正确的写法应该是 where status is NULL 或 where status is not NULL。

如果你问我为什么，一个月前我会告诉你：我也不知道为什么，一直以来我都是背下来的，未曾考虑过为什么，甚至以为 where status = NULL 是错误的语法。

事实上这当然不是错误的语法，你甚至写 NULL = NULL 都是可以的，那你猜 NULL = NULL 返回的是 true 还是 false？答案是：既不是 true 也不是 false，而是 NULL。实际上无论 NULL 跟哪种数据、进行哪种算数比较，结果都是 NULL，而不是 true 或 false：

mysql> select 1 is NULL, 1 is not NULL;
+-----------+---------------+
| 1 is NULL | 1 is not NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.03 sec)

mysql> select 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL, NULL = NULL, NULL != NULL;
+----------+-----------+----------+----------+-------------+--------------+
| 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL | NULL = NULL | NULL != NULL |
+----------+-----------+----------+----------+-------------+--------------+
|     NULL |      NULL |     NULL |     NULL |        NULL |         NULL |
+----------+-----------+----------+----------+-------------+--------------+
1 row in set (0.03 sec)

官方的解释是，NULL 的含义是“一个缺失的、未知的值”，所以你只能准确地判断一个值是不是“未知的”，而将这个值与“未知的”值做算数比较时，结果都是“未知的”。

这是一个几乎完美的逻辑闭环，完全找不到缺陷，但这样一个似乎人畜无害的设计，却引发了另一个颇具争议的问题。

且看这样一种表：

create table `test6` (
  `id` int not null auto_increment,
  `first_name` varchar(8),
  `last_name` varchar(8),
  primary key (`id`),
  unique key (`first_name`, `last_name`)
);

没有什么特别之处，只是多了一个联合唯一索引，约束不能出现多条记录有相同的 first_name 和 last_name，就像这样：

mysql> insert into test6 (first_name, last_name) values ('a', 'b');
Query OK, 1 row affected (0.20 sec)

mysql> insert into test6 (first_name, last_name) values ('a', 'b');
ERROR 1062 (23000): Duplicate entry 'a-b' for key 'first_name'

但是问题来了，可能有的人没有不需要 last_name，就像这样：

mysql> insert into test6 (first_name) values ('a');
Query OK, 1 row affected (0.05 sec)

那么如果我再插一条只有 first_name 为 ‘a’ 的记录呢？

mysql> insert into test6 (first_name) values ('a');
Query OK, 1 row affected (0.22 sec)

mysql> select * from test6;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  3 | a          | NULL      |
|  4 | a          | NULL      |
|  1 | a          | b         |
+----+------------+-----------+
3 rows in set (0.04 sec)

如你所见，唯一索引似乎并没有起作用，('a', NULL) 和 ('a', NULL) 并不冲突。

这样的设计看起来匪夷所思，但你结合刚才对 NULL 的解释去想，就不难明白，由于 'a' = 'a' and NULL = NULL 的结果不是 true 而是 NULL，所以这两条记录在 MySQL 看来并不相等，也就不会受唯一索引的限制了。这样的设计有人说是缺陷，也有人说是特性，难有定音。但在我看来，我们没有能力干涉 MySQL 的设计，那么只能这样哄自己：

如果我们不知道这个东西，被弄得一头雾水甚至踩雷掉坑，那它对于我们来说就是缺陷；而如果我们知道这个东西，能巧妙规避甚至善加利用，那它对于我们来说，就是特性。