Mysql不容易发现的陷阱--字符集转换
本文参考自 https://blog.csdn.net/bohu83/article/details/105320327
假设现在有两张表
CREATE TABLE `tradelog` ( `id` int(11) NOT NULL, `tradeid` varchar(32) DEFAULT NULL, `operator` int(11) DEFAULT NULL, `t_modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`), KEY `t_modified` (`t_modified`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `trade_detail` ( `id` int(11) NOT NULL, `tradeid` varchar(32) DEFAULT NULL, `trade_step` int(11) DEFAULT NULL, /*操作步骤*/ `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/ PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
- 第1步,是根据id在tradelog表里找到L2这一行;
- 第2步,是从L2中取出tradeid字段的值;
- 第3步,是根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断tradeid的值是否匹配。
进行到这里,你会发现第3步不符合我们的预期。因为表trade_detail里tradeid字段上是有索引的,我们本来是希望通过使用tradeid索引能够快速定位到等值的行。但,这里并没有。
如果你去问DBA同学,他们可能会告诉你,因为这两个表的字符集不同,一个是utf8,一个是utf8mb4,所以做表连接查询的时候用不上关联字段的索引。这个回答,也是通常你搜索这个问题时会得到的答案。
但是你应该再追问一下,为什么字符集不同就用不上索引呢?
我们说问题是出在执行步骤的第3步,如果单独把这一步改成SQL语句的话,那就是:
mysql> select * from trade_detail where tradeid=$L2.tradeid.value;
其中,$L2.tradeid.value的字符集是utf8mb4。
参照前面的两个例子,你肯定就想到了,字符集utf8mb4是utf8的超集,所以当这两个类型的字符串在做比较的时候,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较。