时间：2022-05-11 09:23:11 | 栏目：Mysql | 点击：次
在MySQL中常见的utf8mb4排序规则有：
当设置表的默认字符集为utf8mb4字符集但未明确指定排序规则时：
由于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 5.6版本中，参数collation_server用于设置服务器级别的默认排序规则。
参数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在下列条件中生效：
1、准确性
2、性能
测试脚本
## 删除测试表 DROP TABLE IF EXISTS tb2001; DROP TABLE IF EXISTS tb2002; DROP TABLE IF EXISTS tb2003; ## 创建测试表 CREATE TABLE tb2001( id INT AUTO_INCREMENT PRIMARY KEY, c1 VARCHAR(100) COLLATE utf8mb4_unicode_ci, c2 VARCHAR(100) COLLATE utf8mb4_bin )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ; CREATE TABLE tb2002( id INT AUTO_INCREMENT PRIMARY KEY, c1 VARCHAR(100) COLLATE utf8mb4_general_ci, c2 VARCHAR(100) COLLATE utf8mb4_bin )ENGINE=INNODB DEFAULT CHARSET=utf8mb4; CREATE TABLE tb2003( id INT AUTO_INCREMENT PRIMARY KEY, c1 VARCHAR(100) COLLATE utf8mb4_0900_ai_ci, c2 VARCHAR(100) COLLATE utf8mb4_bin )ENGINE=INNODB DEFAULT CHARSET=utf8mb4; ## 插入测试数据 INSERT INTO tb2001(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0); INSERT INTO tb2002(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0); INSERT INTO tb2003(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0); ## 等值查询测试 SELECT * FROM tb2001 WHERE c1=0xF09F8D83; SELECT * FROM tb2002 WHERE c1=0xF09F8D83; SELECT * FROM tb2003 WHERE c1=0xF09F8D83; SELECT * FROM tb2001 WHERE c2=0xF09F8D83; SELECT * FROM tb2002 WHERE c2=0xF09F8D83; SELECT * FROM tb2003 WHERE c2=0xF09F8D83;
测试结果
mysql> SELECT * FROM tb2001 WHERE c1=0xF09F8D83; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 🍃 | 🍃 | | 2 | 🦊 | 🦊 | | 3 | 🌠 | 🌠 | +----+------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM tb2002 WHERE c1=0xF09F8D83; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 🍃 | 🍃 | | 2 | 🦊 | 🦊 | | 3 | 🌠 | 🌠 | +----+------+------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM tb2003 WHERE c1=0xF09F8D83; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 🍃 | 🍃 | +----+------+------+ 1 row in set (0.00 sec) mysql> mysql> SELECT * FROM tb2001 WHERE c2=0xF09F8D83; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 🍃 | 🍃 | +----+------+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb2002 WHERE c2=0xF09F8D83; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 🍃 | 🍃 | +----+------+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb2003 WHERE c2=0xF09F8D83; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 🍃 | 🍃 | +----+------+------+ 1 row in set (0.00 sec)
测试总结