MySQL字符集中utf8mb4的排序规则utf8mb4_bin和utf8mb4_general_ci的区别和对查询数据的影响

啊山022324
461 阅读13分钟

问题描述

在业务适配阶段，应用开发反馈了一个问题，执行查询sql中根据生僻字等值条件查询，结果集出现了两个不同的生僻字的情况，导致应用报错。连接到数据库中执行SQL后，复现了这个情况。第一反应是在测试环境模拟表结构和数据回归测试一下这个情况。

测试和验证

回归测试及找到锚点

在mysql8.0.25的环境下进行了如下测试

表结构： image.png

CREATE TABLE t4 (

id int DEFAULT NULL,

c1 varchar(23) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据：

insert into t4 values(1,'𡉁'),(2,'𡉂');

操作：

select * from t4;

select * from t4 where c1='𡉂';

image.png 通过表结构中c1列的排序规则找到了该问题的锚点。

到MySQL官网上搜了下collation相关的资料，找到了排序规则的实现类型这篇文档，dev.mysql.com/doc/refman/… 里面的介绍中，发现对utf8mb4_general_ci排序规则有个介绍：此类排序规则不区分大小写和重音，不同的字符串具有不同的字符编码，但权重都是相同的，所以比较结果相同。看到这里的介绍以及操作例子，根因就基本就清楚了，就差验证了。

印证操作

验证步骤：


select c1,hex(c1),hex(weight_string(c1)) from t4;

image.png 发现这两个生僻字的权重是一样的，那么根据字符等值条件来查询时，mysql会将权重相同的字符串都查询出来（对字符串转成二进制后再比较做一个补充说明），所以结果集就会出现了两个。

这里对weight_string函数的解释：返回字符串的权重字符串。

HEX函数的解释：对于字符串参数str， HEX（）返回str的十六进制字符串表示形式，其中str中每个字符的每个字节被转换为两个十六进制数字。（因此，多字节字符将超过两位数字。）

至此，验证已经完成，也知道的具体的根因，就是在utf8mb4_gerneral_ci排序规则中，不区分大小写、重音，不同的字符串具有不同的字符编码，但权重有相同的。这两个生僻字的字符串权重是一样的，所以在等值查询中，结果集会出现两条数据。

验证最佳解决方法

明白根因后，咱们就得想办法解决该问题了，通过collation关键字搜索官方文档的排序规则相关文章，《二进制排序规则与 _bin 排序规则的比较》这一篇提到了utf8mb4字符集相关的排序规则介绍，链接：dev.mysql.com/doc/refman/…，进到该链接后，具体的介绍再见该链接：dev.mysql.com/doc/refman/…，点击如下图：

image.png

我把关键的部分介绍拿了出来：

image.png “大多数 Unicode 字符集都包含一个通用排序规则（名称中带有 _general 或缺少语言说明符）、一个二进制排序规则（名称中带有 _bin ）以及若干特定于语言的排序规则（由语言说明符指定）。例如，对于 utf8mb4，utf8mb4_general_ci 和 utf8mb4_bin 分别是其通用排序规则和二进制排序规则，而 utf8mb4_danish_ci 则是其特定于语言的排序规则之一。

大多数字符集都只有一个二进制排序规则。utf8mb4 是个例外，它有两个二进制排序规则：utf8mb4_bin 和（自 MySQL 8.0.17 起）utf8mb4_0900_bin。这两个二进制排序规则具有相同的排序顺序，但通过其 pad 属性和排序权重特性进行区分。请参阅排序规则 pad 属性和字符排序权重。”

特地部署了在mysql8.0.16和mysql8.0.17来印证utf8mb4_0900_bin排序规则：

mysql8.0.16版本

image.png 可以看到mysql 8.0.16版本中是不支持utf8mb4_0900_bin的。

mysql8.0.17版本

image.png

可以看到mysql 8.0.17版本中已经支持utf8mb4_0900_bin了。

对排序规则的一些字母缩写解释介绍，官网链接：dev.mysql.com/doc/refman/…

image.png

该排序规则基于 UCA 9.0.0 和 CLDR v30，不区分重音和大小写。这些特性在排序规则名称中用 _0900、_ai 和 _ci 表示。例外：utf8mb4_la_0900_ai_ci 不基于 CLDR，因为 CLDR 中未定义古典拉丁语。如下：

排序规则中 _0900表示：该排序规则基于 UCA 9.0.0。

排序规则中 _ai 表示：accent-insensitive 首字母的缩写，不区分重音。

排序规则中 _ci 表示：case-insensitive 首字母的缩写，不区分大小写。

排序规则中 _as表示：accent-sensitive 首字母的缩写，区分重音。

排序规则中 _cs表示：case-sensitive 首字母的缩写，区分大小写。

接下来是根据排序规则utf8mb4_bin和utf8mb4_0900_bin中的权重进行操作验证：

  • utf8mb4_bin

表结构：

image.png

CREATE TABLE t5 (

id int DEFAULT NULL,

c1 varchar(23) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据：

insert into t5 values(1,'𡉁'),(2,'𡉂');

操作：

select * from t5;

select * from t5 where c1='𡉂';

image.png

查询生僻字的字符权重：


select c1,hex(c1),hex(weight_string(c1)) from t5;

image.png 权重是不一样的，也callback了上面官网文档对utf8mb4_bin排序规则的介绍。

  • utf8mb4_0900_bin

表结构：

image.png

CREATE TABLE t6 (

id int DEFAULT NULL,

c1 varchar(23) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据：

insert into t6 values(1,'𡉁'),(2,'𡉂');

操作：

select * from t6;

select * from t6 where c1='𡉂';

image.png

查询生僻字的字符权重：


select c1,hex(c1),hex(weight_string(c1)) from t6;

image.png

权重是不一样的，也callback了上面官网文档对utf8mb4_bin排序规则的介绍。但是，还有一个PAD属性没验证完呢，那得继续往下看了！

延申出来的PAD属性问题

关于PAD属性的介绍

MySQL 排序规则具有 pad 属性，其值为 PAD SPACE 或 NO PAD：

  • 大多数 MySQL 排序规则的 pad 属性均为 PAD SPACE。
  • 基于 UCA 9.0.0 及更高版本的 Unicode 排序规则的 pad 属性为 NO PAD；请参阅第 12.10.1 节“Unicode 字符集”。

对于非二进制字符串（CHAR、VARCHAR 和 TEXT 值），字符串排序规则的 pad 属性决定了在比较字符串末尾的尾随空格时的处理方式：

  • 对于 PAD SPACE 排序规则，尾随空格在比较中不重要；字符串比较时不考虑尾随空格。
  • NO PAD 排序规则将尾随空格视为重要字符，就像任何其他字符一样。

可以使用两个 utf8mb4 二进制排序规则来演示不同的行为，其中一个是 PAD SPACE，另一个是 NO PAD。该示例还展示了如何使用 INFORMATION_SCHEMA COLLATIONS 表来确定排序规则的 pad 属性。

此处的“比较”不包含 LIKE 模式匹配运算符，对于该运算符，尾随空格无论排序规则如何都有效。

对于二进制字符串（BINARY、VARBINARY 和 BLOB 值），所有字节在比较中都有效，包括尾随空格。

官网链接：dev.mysql.com/doc/refman/…

见Trailing Space Handling in Comparisons该部分的内容。

utf8mb4_bin和utf8mb4_0900_bin的PAD属性测试操作 排序规则utf8mb4_bin和utf8mb4_0900_bin的区别在于这儿（dev.mysql.com/doc/refman/…）：

image.png

image.png utf8mb4_bin 的 pad 属性为 PAD SPACE，而 utf8mb4_0900_bin 的 pad 属性为 NO PAD。因此，涉及 utf8mb4_0900_bin 的操作不会添加尾随空格，涉及utf8mb4_bin的操作会添加尾随空格，并且涉及带有尾随空格的字符串的比较对于这两个排序规则可能有所不同：

对于t5（c1列的排序规则：utf8mb4_bin）和t6（c1列的排序规则：utf8mb4_0900_bin）表来说：

image.png 从如上的结果集看到，utf8mb4_bin使用PAD SPACE，会忽略末尾空格，而utf8mb4_0900_bin使用NO PAD，严格区分空格。

注意：utf8mb4_0900_bin这个排序规则在从MySQL 8.0.17才开始有的。

对空格忽略的这种情况，如果不是很熟悉字符集排序规则的用法，就有可能会踩到坑，这个是客观存在的事实，只是咱们没有遇到而已，不代表不存在。一旦出现的时候，那就有：不是啊！哥们，我末尾的空格呢？你怎么给我吃掉了？

再延申出字母大小写的问题

以上是翻阅官网资料中发现的一种对空格严格区分的解法，其实在MySQL8.0中utf8mb4字符集的默认排序规则中，也可以解决不同生僻字相同权重的问题。即在MySQL8.0中，utf8mb4字符集的默认排序规则是utf8mb4_0900_ai_ci，该排序规则的演示情况如下：

CREATE TABLE t9 (

id int DEFAULT NULL,

c1 varchar(23) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t9 values(1,'𡉁'),(2,'𡉂');

select * from t9;

查询权重：

select c1,hex(c1),hex(weight_string(c1)) from t9;

验证数据：

select * from t9 where c1='𡉂';

验证是否严格区分空格：

select * from t9 where c1='𡉂 ';

image.png 但是！！！utf8mb4_0900_ai_ci该排序规则不区分大小写，演示如下：


insert into t9 values(3,'mysql'),(4,'MySQL');

查询权重（发现权重一致！！！）：

select c1,hex(c1),hex(weight_string(c1)) from t9;

验证数据：

select * from t9 where c1='mysql';

image.png 相关的介绍见官网链接：dev.mysql.com/doc/refman/…

把关键的点贴出来了：

“对于非二进制字符串（CHAR、VARCHAR、TEXT），字符串搜索使用比较操作数的排序规则。对于二进制字符串（BINARY、VARBINARY、BLOB），比较使用操作数中字节的数值；这意味着对于字母字符，比较区分大小写。”

“默认字符集和排序规则为 utf8mb4 和 utf8mb4_0900_ai_ci，因此非二进制字符串比较默认不区分大小写。这意味着，如果您使用 col_name LIKE 'a%' 进行搜索，您将获得所有以 A 或 a 开头的列值。要使此搜索区分大小写，请确保其中一个操作数具有区分大小写或二进制排序规则。例如，如果您正在比较一个列和一个字符串，它们都具有 utf8mb4 字符集，则可以使用 COLLATE 运算符使其中一个操作数具有 utf8mb4_0900_as_cs 或 utf8mb4_bin 排序规则。”

对于排序规则utf8mb4_0900_as_cs的演示如下：


CREATE TABLE t10 (

id int DEFAULT NULL,

c1 varchar(23) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t10 values(1,'𡉁'),(2,'𡉂'),(3,'mysql'),(4,'MySQL');

select * from t10;

验证权重：

select c1,hex(c1),hex(weight_string(c1)) from t10;

select * from t10 where c1='𡉂';

验证是否严格区分空格：

select * from t10 where c1='𡉂 ';

验证是否区分大小写：

select * from t10 where c1='mysql';

image.png

这里补充utf8mb4_general_ci、utf8mb4_bin、utf8mb4_0900_bin在大小写的权重排序情况。

  • utf8mb4_general_ci

字母不区分大小写

image.png

insert into t4 values(3,'mysql'),(4,'MySQL');

select * from t4;

select * from t4 where c1='mysql';

show create table t4\G

select c1,hex(c1),hex(weight_string(c1)) from t4;
  • utf8mb4_bin

字母区分大小写

image.png

insert into t5 values(3,'mysql'),(4,'MySQL');

select * from t5;

select * from t5 where c1='mysql';

show create table t5\G

select c1,hex(c1),hex(weight_string(c1)) from t5;
  • utf8mb4_0900_bin

字母区分大小写

image.png

insert into t6 values(3,'mysql'),(4,'MySQL');

select * from t6;

select * from t6 where c1='mysql';

show create table t6\G

select c1,hex(c1),hex(weight_string(c1)) from t6;

在翻阅官网collation相关资料中，还有一个重要的提示：

"utf8mb3 字符集已弃用，预计会在未来的 MySQL 版本中被移除。请使用 utf8mb4 字符集。utf8 目前是 utf8mb3 的别名，但现已弃用，utf8 预计随后会成为 utf8mb4 的引用。从 MySQL 8.0.28 开始，在信息模式表的列中以及 SQL SHOW 语句的输出中，utf8mb3 也会代替 utf8 显示。"

"为避免 utf8 含义的歧义，请考虑在字符集引用中明确指定 utf8mb4。"

还需要注意的一点是，在MySQL5.7版本中utf8mb4字符集的默认排序规则为utf8mb4_general_ci，如果有要求需要迁移到MySQL8.0时，在处理生僻字方面需要注意字符集的排序规则，不能按照原字符集的排序规则来定义了。通过充分业务的验证后，选择最适合的字符集排序规则作为定义，在生僻字存储时建议使用utf8mb4_0900_bin排序规则进行定义。那为啥不选择用utf8mb4_0900_as_cs做排序规则呢，它也符合生僻字不同权重也不同的情况，也符合字母区分大小写的情况，因为官网的这句话，utf8mb4_0900_bin它更快！（实质上utf8mb4_0900_bin从以上的权重返回结果来看，utf8mb4_0900_bin所占字节更少，那么检索的速度就会更快，具体性能问题涉及面不止一点两点，这里就不深入了）

image.png 官网链接：dev.mysql.com/doc/refman/…

总结

  • 生僻字中在使用字符集排序规则时，建议使用utf8mb4_0900_bin，如果没有该排序规则，utf8mb4_bin也可以避免生僻字权重一样，查询结果集不符合预期的情况。
  • 使用字符集排序规则时，需要注意区分它们的PAD属性，以文中测试操作为例，能用utf8mb4_0900_bin就用这个，utf8mb4_bin有可能会在特殊的场景下存在不符合预期的情况。
  • 针对区分字母大小写的情况，可以选择utf8mb4_0900_as_cs或utf8mb4_0900_bin排序规则。但是utf8mb4_0900_bin它更快！
  • 综上三个点，针对生僻字、尾随空格、字母大小写排序的情况，建议使用utf8mb4_0900_bin排序规则。
avatar
文章
阅读
粉丝