MySQL中utf8mb4排序规则示例

在mysql中常见的utf8mb4排序规则有：

utf8mb4_0900_ai_ci
utf8mb4_unicode_ci
utf8mb4_general_ci

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

在mysql 5.7版本中，默认排序规则为utf8mb4_general_ci。
在mysql 8.0版本中，默认排序规则为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 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但未指定编码规则时。

utf8mb4_unicode_ci和utf8mb4_general_ci对比

1、准确性

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

2、性能

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

补：mysql–utf8mb4与等值查询测试

测试脚本

## 删除测试表
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)

测试总结

使用utf8mb4字符集能存储特殊字符如表情符号。
utf8mb4_unicode_ci和utf8mb4_general_ci排序规则无法”正确匹配”特殊字符。
utf8mb4_0900_ai_ci排序规则能”正确匹配”特殊字符,但仅适用mysql 8.0版本。
utf8mb4_bin排序规则基于二级制方式匹配特殊字符，能”正确匹配”特殊字符，适用于mysql各个版本。

到此这篇关于mysql中utf8mb4排序规则示例的文章就介绍到这了,更多相关mysql utf8mb4排序内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

MySQL中utf8mb4排序规则示例

参数控制

utf8mb4_unicode_ci和utf8mb4_general_ci对比

补：mysql–utf8mb4与等值查询测试

相关推荐