MySQL 查询避坑指南

        在MySQL查询中，你可能会不经意的掉入某些坑中，甚至某些坑可能让你不死也掉半条命。下面罗列一下常见的坑。

1、 多表操作时不写别名

        掉坑现场重现：

mysql> select * from t1;
+------+
| Id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> select * from t3;
+------+
| ld   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)
mysql> delete from t1 where Id in(select Id from t2);
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where Id in(select Id from t3);
Query OK, 3 rows affected (0.03 sec)

        t1表有3条记录（记录值不重复），t2表没有记录，t3表有1条记录。所以第一个DELETE语句不会删除任何记录，这是符合预期的。

        可是第2个DELETE语句为什么删除了3条记录呢？t3表只有一条记录，t1的记录值也是不重复的，所以是不是应该最多只删除一条记录呢？

        眼神好的同学可能发现了，t3表压根就没有Id列，t3表的列名是ld。没错，问题就在这里了。在子查询中，列可以是父表的列，也可以是子表的列，当你不写列名所属表的时候，MySQL根据表结构来确定列所属的表，悲剧就发生在这里，由于没有写列所属的表，所以实际上的条件是t1.Id=t1.Id，这个自然就是把t1表的数据都删除了。如果这个情况发生的生产环境，其后果可想而知。

        我们在DB规范中，明确要求多表查询语句中，一定要写清楚列所属的表，它不单纯是让阅读清晰，更重要的是防止掉坑。

        注意，这个坑可不是专属于MySQL。

2、 不支持FULL JOIN

        DBA一直跟我说，MySQL不支持FULL JOIN，可是我觉得不太可能嘛，好歹MySQL也是一个发展了这么多年，用户群也非常大的成熟产品，不至于连这个都不支持。于是我自己写了个FULL JOIN的查询，看看下面的执行现场，妥妥的执行了嘛。

mysql> select * from t1 full join t3 on Id=ld;
Empty set (0.00 sec)

找DBA理论去，然后DBA让我自己看下执行计划（只截取了部分）：

+----+-------------+-------
| id | select_type | table
+----+-------------+-------
|  1 | SIMPLE      | t3
|  1 | SIMPLE      | full

        其他部分都不用看，只需要看table列中中显示的那个full，大家就能明白是怎么回事了吧。因为full join在MySQL中不支持，所以full不是保留的关键字，被识别成表的别名了，查询执行当然就不会出错，但实际上是一个inner join操作。

        当然，这是一个多表查询，如果按照DB规范，把列所属的表写上，那么查询会直接报错找不到表，那样会更早发现问题。

3、 REPLACE INTO的副作用

        REPLACE INTO通常用于处理：存在更新、不存在插入这种业务逻辑。在表中存在多个唯一时，它导致的结果可能不是我们想要的，参考如下：

mysql> create table t1(a int, b int, c int,unique(a),unique(b),unique(c));

mysql> insert into t1 values(1,1,1),(2,2,2),(3,3,3);

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0



mysql> replace into t1 values(1,2,3);

Query OK, 4 rows affected (0.02 sec)



mysql> select * from t1;

+------+------+------+

| a    | b    | c    |

+------+------+------+

|    1 |    2 |    3 |

+------+------+------+

1 row in set (0.00 sec)

        虽然我们只插入了一条记录，但是由于a、b、c都是要保证唯一的，所以我们插入的这条记录，凡是会导致违反唯一约束的记录都会删除，这就导致删除了3条记录。

        可以酌情考虑使用INSERT INTO ON DUPLICATE KEY UPDATE语句，它只会选择一个唯一约束作为记录是否存在的判断依据，并且如果UPDATE导致违反了其他唯一约束，会给出Duplicate的错误信息。不过，在表中有多个唯一约束时，你无法控制ON DUPLICATE KEY选择使用哪个唯一约束来确定记录是否存在。

4、INSERT INTO ON DUPLICATE KEY UPDATE加自增列的诡异现象

        先看看下面的演示脚本：

mysql> select * from t1 order by id desc;

+-----+------+

| id  | c1   |

+-----+------+

| 127 |    1 |

| 126 |    2 |

+-----+------+

2 rows in set (0.00 sec)



mysql> insert into t1(c1) values(2) as n on duplicate key update c1=n.c1;

ERROR 1062 (23000): Duplicate entry '2' for key 't1.c1'

        在这个演示脚本中，t1.id是自增列+主键，c1上有唯一约束。出错的insert 语句没有指定自增列，因此这个列自动生成最新的，不会成为ON DUPLICATE KEY的判断依据，而通过c1来确定记录是否存在的话，要么存在更新，要么不存在插入一条记录，但这里居然报duplicate的错误，这是不是很诡异呢？

        MySQL不支持将自增值设置为一个比表中最大值还小的值，所以应该可以排除将当前自增值调小，使新记录自增值变成127，导致以自增值作为记录是否存在的依据，使更新发生在id=127的记录，从而产生duplicate错误。为了验证问题，我们仍然通过SHOW CREATE TABLE t1来验证一下：

Create Table: CREATE TABLE `t1` (

  `id` tinyint NOT NULL AUTO_INCREMENT,

  `c1` int DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

        结果显示：AUTO_INCREMENT=127，所以下一个值为128，不可能回到127上。但再仔细看，你会发现id的数据类型是tinyint，它的上限就是127，也就是下一个值是128，这是不是应该会出现Out of range的错误？

        事实上不会，当显式往表中INSERT一个超过数据类型范围的值时，确实会报Out of range的错误；但如果让MySQL自己来生成这个值时，它发现产生的新值超过范围时，会使用最大值，所以它不会出现Out of range的错误。在这个示例中，生成的自增值超出范围了，所以回退到最大值127，这导致更新操作发生在id=127的记录上，并因此产生了诡异的Duplicate错误。

5、数据类型不一致

        在一个查询表达式中，涉及多个字段、变量、常量时，类型不一致是非常常见的，但这种不统一有时候会带来非常严重的影响。

        这是最近遇到的一次掉坑，查询语句非常简单，看不出什么毛病；满足条件的数据量只有200条，但执行时长超过30秒。

SELECT A.BNo, B.SId, A.Status, COUNT(0) AS Total

FROM A

INNER JOIN B ON B.ONo = A.SNo

INNER JOIN C ON B.SId = C.SId AND A.BNo = C.BNo

GROUP BY  A.BNo, B.SId, A.Status

        查看执行计划，驱动表为C（数据量最小的一个表），依次JOIN B、A得到最终结果，它们的Rows评估为100、948、1，有合适的索引，看起来应该没有什么问题。

        进行实际的数据验证，C JOIN B的结果有近800万（检查数据，发现数据的分布很不均衡，所以评估的数据量与实际数据量有很大差异的原因明确了）；C JOIN A的结果不足300条。正常来说，应该先JOIN小表，再去JOIN大表，目前的这个执行计划显然不太合理。最后检查表结构，发现C JOIN A的字段类型不一致，类型不一致导致需要先做类似转换，这通常会导致索引无效或低效。将字段类型统一之后，执行计划正常了，并且执行时间由原来的30秒+缩短到毫秒。

        除了数据类型，字符集的差异也会造成类似的影响，只是我们一般都是使用统一字符集，所以这种情况较少遇见。

6、 不确定性函数

        从一个表中，随机抽取10条记录，SQL语句怎么写？似乎很容易：ORDER BY RAND() LIMIT 10

        没毛病，也确实得到了想要的结果，但试试用这个语句从大表中取10条数据，你会发现很慢，每次都很慢，为什么呢？

        MySQL函数在内部被标记为确定性或非确定性。如果函数使用相同的参数值调用，返回的值可能不相同，则函数是非确定性的，RAND、UUID都是非确定性函数。

        在查询中使用非确定性函数，意味着对于每一行，函数都会被调用，所以对于ORDER BY RAND() LIMIT 10而言，由于第一行都会调用RAND，它的值是不定的，那么要ORDER BY，所有的行都要得到RAND的结果，也就是做了表扫描，慢就可以理解了。

7、 容错查询

        区别于那些严格要求，一有异常就抛出错的数据库系统，MySQL查询讲究尽量给出它理解的结果，通过Warning展示对查询的质疑。这种体系有时候会导致一些混乱。

        比如下面的两个查询：

mysql> select id from t1 where id='1a' union all select id from t2 where id='1a';

+------+

| id   |

+------+

| 1    |

| 1a   |

+------+

2 rows in set, 1 warning (0.00 sec)



mysql> select id from( select id from t1 union all select id from t2 )d where id='1a';

+------+

| id   |

+------+

| 1a   |

+------+

1 row in set (0.00 sec)

        在数据不变的情况下，从查询逻辑来说，上述两个查询应该返回相同的结果，但这两个查询返回了不同的查询结果，并且我们注意到第一个查询的输出中包含了1 warning。

        重新执行查询1，并且通过SHOW WARNINGS，我们得到了这样的信息：

+---------+------+----------------------------------------+

| Level   | Code | Message                                |

+---------+------+----------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: '1a' |

+---------+------+----------------------------------------+

        这表明查询1中出来了一个数据类型转换，将字符串1a转成了DOUBLE，在这个示例中，t1.id是int，t2.id是varchar，熟悉数据库的同学大致都知道，当varchar和int中比较的时候，int的类型优先级高于varchar，也就是varchar会转为int之后再做比较，所以对于查询1，t1.id=’1a’这个操作导致1a转成数字，但1a显然不是一个数字，MySQL在这里做了和谐处理，将1a中的前置数字提取出来作为数字了，但这个理解可能是不正确的，所以给了一个warning。

        如果是按照同样的规则来解析查询2，你会发现仍然解释不通，在UNIO ALL的时候，两个表的数据类型不一致，按照数据类型优先级，也是varchar转int，这里应该有一个warning，并且UNION ALL的结果应该是两个值为1的记录，最后经过WHERE条件的时候，会因为数据类型不一致，再报一次warning。但查询2很神奇的是没有warning，并且记录值也不符合预期。原因在于UNION对于数据的转换规则又有所不同，在UNION查询中，它会综合评估列的类型和长度，确定一个合适的结果类型，在这个查询中，评估的结果是使用varchar数据类型，所以查询过程中只有一次int转varchar，而且这个转换是没有歧义的，所以查询不会有任何Warning。

        关于表达式数据类型转换规则和UNION对数据输出类型界定，可以参考MySQL官方文档上的相关说明：

MySQL :: MySQL 5.7 Reference Manual :: 12.3 Type Conversion in Expression Evaluation

MySQL :: MySQL 5.7 Reference Manual :: 13.2.9.3 UNION Clause

【本文在公司微信公众号云筑网技术团队及个人微信公共号ZJCXC上同步发表】