本文转载自微信公众号《DBA随笔》，作者DBA随笔。转载本文请联系DBA随笔公众号。今天在网上遇到一个MySQL字符比较的问题。这很有趣。我专门研究过。估计没有人遇到过。我想在这里与大家分享。一、背景介绍：MySQL中有一张表。在根据where条件查询某条记录时，误输入了一个空格。我发现这个数据还是可以查到的。我建立了一个测试表并按如下方式恢复它：22:57:02>createtablet00(idintprimarykey,namevarchar(10));QueryOK,0rowsaffected(0.01sec)22:57:11>insertintot00values(1,'aaa'),(2,'bbb');QueryOK,2rowsaffected(0.00sec)Records:2Duplicates:0Warnings:022:57:22>select*fromt00where名称='aaa';+----+------+|编号|名字|+----+------+|1|aaa|+----+-----+1rowinset(0.00sec)22:57:32>select*fromt00wherename='aaa';+----+------+|编号|名字|+----+------+|1|aaa|+----+-----+1rowinset(0.00sec)插入(1,'aaa')这条记录，使用where='aaa'和'aaa'这两个条件来匹配，居然可以找到这条记录。一开始怀疑是8.0.19版本的MySQL实例配置有问题。换了一个低于5.5版本的MySQL实例，再次测试，问题依旧重现。好像不是版本问题，肯定是某种配置问题。晚上回到家，用自己搭建的一个8.0.22版本的MySQL实例重新执行上面的命令，惊奇的发现并没有重现。..称重。8.0.22版本测试结果为：23:35:30>>select*fromt0;+-----+-----+|编号|名称|+-----+------+|1|啊||2|bbb|+-----+-----+2rowsinset(0.01sec)23:35:34>>select*fromt0wherename='aaa';+-----+------+|编号|名字|+-----+-----+|1|aaa|+------+-----+1rowinset(0.00sec)23:35:46>>select*fromt0wherename='aaa';Emptyset(0.00sec)2.分析思路1）为什么'aaa'和'aaa'一样？首先，我在MySQL中使用命令检查两个字符串是否相同：###MySQL实例一23:39:09>select'aaa'='aaa';+------------------+|'aaa'='aaa'|+----------------+|1|+----------------+1rowinset(0.00sec)###MySQLinstance223:35:54>>select'aaa'='aaa';+--------------------+|'aaa'='aaa'|+--------------+|0|+----------------+1rowinset(0.00sec)从上面的结果可以看出，在这两个实例中，字符的比较规则是不同的。至此，有些同学可能已经知道答案了。但再往下看。2）比较规则有哪些不同？我们可以使用如下命令先看看utf8相关字符集下的比较规则，如下：23:45:18>showcollat??ionlike'utf8%';+------------------------+--------+-----+--------+----------+----------+--------------+|整理|字符集|编号|默认|编译|排序|Pad_attribute|+----------------------------+--------+-----+--------+------------+--------+------------+|utf8mb4_0900_ai_ci|utf8mb4|255|是|是|0|没有垫||utf8mb4_0900_as_ci|utf8mb4|305||是|0|没有垫||1|没有垫||utf8mb4_bin|utf8mb4|46||是|1|填充空间||utf8mb4_croatian_ci|utf8mb4|245||是|8|mb4|266||是|0|没有垫||utf8mb4_cs_0900_as_cs|utf8mb4|289||是|0|没有垫||utf8_unicode_ci||utf8_越南语_ci|编码器|215||是|8|填充空间|+----------------------------+----------+-----+-------+------------+--------+----------------+103rowsinset(0.00sec)在最后一列中，我们可以看到一个pad属性，它包含2个值，分别是nopad和padspace3）尝试在官方文档中查找这两个属性的含义。果然不出所料，我发现了一些线索：https://dev.mysql.com/doc/refman/8.0/en/char.html要确定排序规则的填充属性，请使用INFORMATION_SCHEMACOLLATIONS表，其中有一个PAD_ATTRIBUTE柱子。对于非二进制字符串（CHAR、VARCHAR和TEXT值），字符串归类垫属性确定比较字符串末尾尾随空格时的处理方式。NOPADtrcoat尾随空格在比较中很重要，就像任何其他字符一样。PADSPACE排序规则在比较中将尾随空格视为无关紧要；比较字符串时不考虑尾随空格。上面这段话的意思大概是：要确定collat??ion的填充属性，使用information_schema.collat??ions表，其中有一个pad_attribute列。对于非二进制字符串（char、varchar和text），字符串的填充属性决定了在比较字符串时如何处理尾随空格。NOPAD排序规则将尾随空格视为重要比较，更严格地说，就像任何其他字符一样；PADSPACE排序规则在比较时将尾随空格视为无意义的，并且在比较字符串时不考虑尾随空格，即没有空格的样本。这里我们可以根据实际比较规则查看对应的pad属性：先看示例一：###MySQL示例一00:01:31>showvariableslike'%colla%';+-----------------------------+--------------------+|变量名|值|+----------------------------+------------------+|排序规则连接|utf8_general_ci||排序规则数据库|utf8mb4_0900_ai_ci||整理服务器|utf8mb4_0900_ai_ci||default_collat??ion_for_utf8mb4|----------+--------------------+4行集合（0.01秒）00:01:45>选择collat??ion_name,character_set_name,pad_attributefrominformation_schema.collat??ionswherecollat??ion_namelike'utf8_general_ci';+----------------+-------------------+----------------+|排序规则名称|字符集名称|pad_attribute|+----------------+----------------------+--------------+|utf8_general_ci|编码器|填充空间|+------------------+--------------------+-------------+1rowinset(0.00sec)再来看实例二：###实例二mysql--root@localhost:(none)23:53:52>>showvariables喜欢'%colla%';+--------------------------------+--------------------+|变量名|值|+----------------------------+-------------------+|排序规则连接|utf8mb4_0900_ai_ci||排序规则数据库|utf8mb4_0900_ai_ci||整理服务器|utf8mb4_0900_ai_ci||default_collat??ion_for_utf8mb4|utf8mb4_0900_ai_ci|+----------------------------+---------------------集合中的+4行（0.00秒）00:03:47>>从information_schema.collat??ions中选择collat??ion_name、character_set_name、pad_attribute，其中collat??ion_namelike'utf8mb4_0900_ai_ci';+----------------------+--------------------+--------------+|排序规则名称|字符集名称|pad_attribute|+--------------------+--------------------+---------------+|utf8mb4_0900_ai_ci|utf8mb4|无PAD|+--------------------+--------------------+---------------+1rowinset(0.00sec)在这里，真相大白。例1的连接比较规则是utf8_general_ci，对应的padding规则是padspace属性，表示字符比较时末尾的空格。这并不重要，所以不管有没有空格，结果都是一样的；例2的连接比较规则是utf8mb4_0900_ai_ci，对应的padding规则是nopad属性，意思是字符比较过程中末尾的空格很重要，所以有无空格结果不同。3、如何让字符匹配更严格？1）修改连接比较规则为utf8mb4_0900_ai_ci。当然这个修改需要和默认的字符集相匹配。这个方案比较容易理解，我就不赘述了。2）使用like模糊匹配进行比较3）在where条件前，在上面2、3两种方法中加入binary关键字，看下面测试：00:19:13>select*fromt00;+----+------+|编号|名字|+----+------+|1|啊||2|bbb|+----+-----+2rowsinset(0.00sec)00:19:18>select*fromt00wherename='aaa';+----+------+|编号|姓名|+----+------+|1|aaa|+----+-----+1rowinset(0.00sec)00:19:28>select*fromt00wherename='aaa';+----+------+|编号|名字|+----+------+|1|aaa|+----+-----+1rowinset(0.00sec)###下面两种方案可以防止'aaa'匹配到'aaa'00:19:31>select*fromt00，其中名称如“aaa”；空集（0.00秒）00:19:57>select*fromt00wherebinaryname='aaa';空集（0.00秒）今天的文章到此结束。
