utf8mb4常见排序规则

utf8mb4常见排序规则有：

• utf8mb4_0900_ai_ci
• utf8mb4_unicode_ci
• utf8mb4_general_ci

utf8mb4_unicode_ci和utf8mb4_general_ci对比
1、准确性

• utf8mb4_unicode_ci排序规则基于标准unicode进行排序和比较，能处理特殊的字符，能在各种语音中精确排序。
• utf8mb4_general_ci排序规则没有基于标准unicode，无法处理部分特殊字符。

2、性能

• utf8mb4_general_ci排序规则在排序性能上相对较好
• utf8mb4_unicode_ci排序规则为处理特殊字符实现复杂的排序算法，性能略差
• 在大部分场景下，两者没有明显的性能差异

utf8mb4_0900_ai_ci和utf8mb4_general_ci对比

• 在MySQL 5.7版本使用utf8mb4字符集时默认使用utf8mb4_general_ci排序规则
• 在MySQL 8.0版本使用utf8mb4字符集时默认使用utf8mb4_0900_ai_ci排序规则

由于utf8mb4_0900_ai_ci排序规则时MySQL 8.0引入的排序规则，因此将MySQL 8.0版本的表导入到MySQL 5.7或MySQL 5.6版本时，会存在字符集无法识别的问题。

[Err] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

MySQL版本与默认排序规则

当设置表的默认字符集为utf8mb4字符集但未明确指定排序规则时：

• 在MySQL 5.7版本中，默认排序规则为utf8mb4_general_ci。
• 在MySQL 8.0版本中，默认排序规则为utf8mb4_0900_ai_ci。

在MySQL 5.6版本中，参数collation_server用于设置服务器级别的默认排序规则。

• 如果服务启动时未指定参数collation_database的值，则默认继承参数collation_server的值。
• 如果创建数据库时未指定排序规则，则默认使用参数collation_database的值。

参数character_set_database和collation_database在MySQL 5.7版本中被遗弃并将在后续版本中移除。

MySQL 新增参数default_collation_for_utf8mb4用于控制使用utf8mb4字符集时的默认排序规则，取值为utf8mb4_0900_ai_ci或utf8mb4_general_ci

参数default_collation_for_utf8mb4在下列条件中生效：

• 使用SHOW COLLATION and SHOW CHARACTER SET 命令时。
• 在创建库或修改库指定utf8mb4但未指定编码规则时。
• 在创建表或修改表指定utf8mb4但未指定编码规则时。
• 在增加列或修改列指定utf8mb4但未指定编码规则时。
• 其他使用utf8mb4但未指定编码规则时。

三种排序规则等值查询对比

测试方法

## 创建测试表
CREATE TABLE tb2001(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE tb2002(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci;

CREATE TABLE tb2003(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

## 插入测试数据
INSERT INTO tb2001(c1)VALUES(0xF09F8D83),(0xF09FA68A),(0xF09F8CA0);
INSERT INTO tb2002(c1)VALUES(0xF09F8D83),(0xF09FA68A),(0xF09F8CA0);
INSERT INTO tb2003(c1)VALUES(0xF09F8D83),(0xF09FA68A),(0xF09F8CA0);

## 等值查询测试
SELECT 0xF09F8D83;
SELECT * FROM tb2001 WHERE c1=0xF09F8D83;
SELECT * FROM tb2002 WHERE c1=0xF09F8D83;
SELECT * FROM tb2003 WHERE c1=0xF09F8D83;

测试结果

mysql> SELECT 0xF09F8D83;
+------------+
| 0xF09F8D83 |
+------------+
|