|
|
|
|
移动端

聊一聊MySQL 排序规则

在日常工作中,对于MySQL排序规则,很少关注,大部分时候都是直接使用字符集默认的排序规则,常常忽视了排序规则的细节问题,了解排序规则有助于更好的理解MySQL字符比较和排序相关的知识

作者:LinuxThings 来源:Linux开发那些事儿|2020-10-15 06:56

本文转载自微信公众号「Linux开发那些事儿」,作者 LinuxThings 。转载本文请联系Linux开发那些事儿公众号。

在日常工作中,对于MySQL排序规则,很少关注,大部分时候都是直接使用字符集默认的排序规则,常常忽视了排序规则的细节问题,了解排序规则有助于更好的理解MySQL字符比较和排序相关的知识

简述

说起排序规则就离不开字符集,严格来说,排序规则是依赖于字符集的

字符集是用来定义MySQL存储不同字符的方式,而排序规则一般指对字符集中字符串之间的比较、排序制定的规则。一种字符集可以对应多种排序规则,但是一种排序规则只能对应指定的一种字符集,两个不同的字符集不能有相同的排序规则

上图中,Collation 列表示排序方式,Charset 列表示字符集,可以看出 utf8 字符集对应着许多的排序方式,排序方式那一列每一项的值都不一样,并且每一项都对应唯一一种字符集,在这里是 utf8 字符集

默认排序规则

  • 字符集的默认排序规则
    1. mysql> show character set like 'utf8%'
    2. +---------+---------------+--------------------+--------+ 
    3. | Charset | Description   | Default collation  | Maxlen | 
    4. +---------+---------------+--------------------+--------+ 
    5. | utf8    | UTF-8 Unicode | utf8_general_ci    |      3 | 
    6. | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 | 
    7. +---------+---------------+--------------------+--------+ 
    8. rows in set (0.00 sec) 

每种字符集都有一个默认的排序规则, 可以通过下面的SQL语句查询指定字符集的默认排序规则

上面的例子是查询字符集前缀包含utf8的默认排序方式,从中可以得知:

utf8字符集的默认排序方式是 utf8_general_ci 字符集中字符最大长度占3个字节

utf8mb4 字符集的默认排序方式是 utf8mb4_general_ci 字符集中字符最大长度占4个字节

  • 数据库的默认排序规则

MySQL服务器的默认字符集可以在 /etc/my.cnf 配置中的 [mysqld] 下修改

例如:现需要把MySQL服务器的默认字符集设置为 utf8, 默认排序规则设置为 utf8_general_ci, 只需要在 /etc/my.cnf 配置文件的 [mysqld] 下添加以下子项

  1. character-set-server=utf8 
  2. collation-server=utf8_general_ci 

创建数据库的时候如果没有指定字符集,会使用MySQL服务器默认字符集和默认排序规则

假如: 在下面例子中,MySQL服务器的默认字符集和默认排序规则分别是 utf8 和 utf8_general_ci

  1. mysql> create database at
  2. Query OK, 1 row affected (0.00 sec) 
  3. mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at'
  4. +-------------+----------------------------+------------------------+ 
  5. | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
  6. +-------------+----------------------------+------------------------+ 
  7. at          | utf8                       | utf8_general_ci        | 
  8. +-------------+----------------------------+------------------------+ 
  9. 1 row in set (0.00 sec) 

在上面的例子中,创建了 at 数据库,创建数据库的时候没有为数据库指定字符集和排序规则,此时会使用 MySQL服务器的默认字符集和排序规则

通过SQL语句查询 at 数据库的默认字符集和默认排序规则,结果和MySQL服务器的默认字符集和默认排序规则是一样的

排序规则命名以及名字后缀

  • 命名

排序规则的命名是以和它自身关联的字符集名字开头的,后面再接一个或多个后缀来表示指定字符集的一种排序规则

例如:utf8_general_ci 和 utf8_bin 就是 utf8字符集的两种排序规则, latin1_swedish_ci 是 latin1字符集的排序规则

注意: binary 字符集只有一种排序规则,并且它的排序规则名字和字符集名字一样, 也是 binary

  • 后缀

排序规则名字的后缀是有特殊意义的,根据后缀可以知道排序规则是否区分大小写,是否区分重音,是否是二进制等等,下面列出了部分后缀的说明

  1. _ci : 不区分大小写, Case-insensitive的缩写 
  2.  
  3. _cs : 区分大小写,Case-sensitive的缩写 
  4.  
  5. _ai : 不区分重音,Accent-insensitive的缩写 
  6.  
  7. _as : 区分重音,Accent-sensitive的缩写 
  8.  
  9. _bin : 二进制 

1: 不区分大小写

  1. mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'
  2. Query OK, 0 rows affected (0.02 sec) 
  3.  
  4. mysql> select 'a' = 'A'
  5. +-----------+ 
  6. 'a' = 'A' | 
  7. +-----------+ 
  8. |         1 | 
  9. +-----------+ 
  10. 1 row in set (0.00 sec) 

例子中排序规则为 utf8_unicode_ci 是不区分大小写的,所以字符 a 和字符 A 会被当做相同字符处理

2: 区分大小写

  1. mysql> SET NAMES 'latin1' COLLATE 'latin1_general_cs'
  2. Query OK, 0 rows affected (0.00 sec) 
  3.  
  4. mysql> select 'a' = 'A'
  5. +-----------+ 
  6. 'a' = 'A' | 
  7. +-----------+ 
  8. |         0 | 
  9. +-----------+ 
  10. 1 row in set (0.00 sec) 

例子中排序规则为 latin1_general_cs 是会区分大小写的,所以字符 a 和字符 A 会被认为是两个不同的字符

3: 二进制

  1. mysql> SET NAMES 'utf8' COLLATE 'utf8_bin'
  2. Query OK, 0 rows affected (0.00 sec) 
  3.  
  4. mysql>  select 'a' = 'A'
  5. +-----------+ 
  6. 'a' = 'A' | 
  7. +-----------+ 
  8. |         0 | 
  9. +-----------+ 
  10. 1 row in set (0.00 sec) 
  11.  
  12. mysql> select 'à' = 'a'
  13. +------------+ 
  14. 'à' = 'a'  | 
  15. +------------+ 
  16. |          0 | 
  17. +------------+ 
  18. 1 row in set (0.00 sec) 

上面例子使用的排序规则是 utf8_bin 从结果可以得知:utf8_bin 排序规则区分大小写,也区分重音字符

4: 是否区分重音

重音字符是类似 à、ě、ň 的字符,不区分重音是指字符 a和 à、e和ě 以及 n和ň 被认为是同一个字符

对于非二进制(后缀为 _bin)的排序规则, 如果排序规则名字后缀不包含 _ai 和 _as, 则排序规则名称中的 _ci 默认隐含了_ai, _cs默认隐含了_as

例如: utf8_unicode_ci排序规则是不区分大小写并且隐含不区分重音的

latin1_general_cs 排序规则是区分大小写并且隐含区分重音的

具体的请查看下面的例子

  1. mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'
  2. Query OK, 0 rows affected (0.02 sec) 
  3.  
  4. mysql> select 'à' = 'a'
  5. +------------+ 
  6. 'à' = 'a'  | 
  7. +------------+ 
  8. |          1 | 
  9. +------------+ 
  10. 1 row in set (0.00 sec) 
  11.  
  12. mysql>  SET NAMES 'latin1' COLLATE 'latin1_general_cs'
  13. Query OK, 0 rows affected (0.00 sec) 
  14.  
  15. mysql> select 'à' = 'a'
  16. +------------+ 
  17. 'à' = 'a'  | 
  18. +------------+ 
  19. |          0 | 
  20. +------------+ 
  21. 1 row in set (0.01 sec) 

查看排序规则

  • 查看数据库数据库的排序规则
  1. mysql>  select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at'
  2. +-------------+----------------------------+------------------------+ 
  3. | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
  4. +-------------+----------------------------+------------------------+ 
  5. at          | latin1                     | latin1_swedish_ci      | 
  6. +-------------+----------------------------+------------------------+ 
  7. 1 row in set (0.00 sec) 

上面的例子是查看 at 数据库的字符集和排序规则,从结果可以得知:at数据库的排序规则是latin1_swedish_ci

方法2:直接查询 collation_database 变量值

  1. mysql> use at
  2. Database changed 
  3. mysql> show variables like 'collation_database'
  4. +--------------------+-------------------+ 
  5. | Variable_name      | Value             | 
  6. +--------------------+-------------------+ 
  7. | collation_database | latin1_swedish_ci | 
  8. +--------------------+-------------------+ 
  9. 1 row in set (0.00 sec) 

实例中 use at SQL语句切换到 at 数据库,然后使用 show variables like 'collation_database'; 语句查询 at 数据库的排序规则

  • 查看表的排序规则

方法1:根据数据库名和表名查看 INFORMATION_SCHEMA.TABLES 表中的 TABLE_COLLATION 字段,可以得到指定数据库中指定表的排序规则,具体的实例如下所示:

  1. mysql> SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test' and TABLE_NAME = 'tc'
  2. +--------------+------------+-----------------+ 
  3. | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | 
  4. +--------------+------------+-----------------+ 
  5. | test         | tc         | utf8_general_ci | 
  6. +--------------+------------+-----------------+ 
  7. 1 row in set (0.00 sec) 

方法2:执行 show create table 表名 语句查看

  1. mysql> show create table tc\G 
  2. *************************** 1. row *************************** 
  3.        Table: tc 
  4. Create TableCREATE TABLE `tc` ( 
  5.   `id` int(11) NOT NULL
  6.   `a` char(32) NOT NULL
  7.   `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  8.   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. 1 row in set (0.00 sec) 

上面的例子中, show create table tc\G 是查看 tc 表的创建语句,一般创建表的时候会指定排序规则,例子中没有显示指定表的排序规则,这是因为使用的是字符集的默认排序规则,tc 表的字符集是 utf8 , 默认的排序规则是 utf8_general_ci

  • 查看字段的排序规则

上面 查看表的排序规则 小节的 方法2 是查看表的创建语句,字段的排序规则也可以从表创建语句中查看到,如果没有显示指定字段的排序规则,创建表的语句中是看不到排序规则的,这表示该字段使用字符集的默认排序方式

  1. mysql> show create table tc\G 
  2. *************************** 1. row *************************** 
  3.        Table: tc 
  4. Create TableCREATE TABLE `tc` ( 
  5.   `id` int(11) NOT NULL
  6.   `a` char(32) NOT NULL
  7.   `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  8.   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. 1 row in set (0.00 sec) 

上面例子中,表 tc的字符集是utf8

字段 a 没有显示指定排序规则,则它使用的是utf8字符集的默认排序规则utf8_general_ci

字段 b 的排序规则是 utf8_bin

字段 c 的排序规则是 latin1_general_cs

修改排序规则

修改数据库的排序规则

数据库的默认排序规则可以通过 alter database 数据库名 collate 新的排序规则名字 SQL语句进行修改

  1. mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at'
  2. +-------------+----------------------------+------------------------+ 
  3. | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
  4. +-------------+----------------------------+------------------------+ 
  5. at          | utf8                       | utf8_general_ci        | 
  6. +-------------+----------------------------+------------------------+ 
  7. 1 row in set (0.00 sec) 
  8.  
  9. mysql> alter database at collate utf8_bin; 
  10. Query OK, 1 row affected (0.00 sec) 
  11.  
  12. mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at'
  13. +-------------+----------------------------+------------------------+ 
  14. | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
  15. +-------------+----------------------------+------------------------+ 
  16. at          | utf8                       | utf8_bin               | 
  17. +-------------+----------------------------+------------------------+ 
  18.  
  19.  
  20. mysql> alter database at collate latin1_swedish_ci ; 
  21. Query OK, 1 row affected (0.00 sec) 
  22.  
  23. mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at'
  24. +-------------+----------------------------+------------------------+ 
  25. | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
  26. +-------------+----------------------------+------------------------+ 
  27. at          | latin1                     | latin1_swedish_ci      | 
  28. +-------------+----------------------------+------------------------+ 
  29. 1 row in set (0.00 sec) 

上面的例子中,at 数据库初始的字符集和排序规则分别是 utf8 和 utf8_general_ci

SQL语句: alter database at collate utf8_bin; 把字符集和排序规则修改为 utf8 和 utf8_bin

SQL语句: alter database at collate latin1_swedish_ci; 把字符集和排序规则修改为 latin1 和 latin1_swedish_ci

由于latin1_swedish_ci排序规则 是属于latin1字符集,所以 at 数据库排序规则修改成 latin1_swedish_ci 之后,字符集相应的也会由 utf8 变成 latin1

  • 修改表的排序规则

通过 alter table 表名 collate 新的排序规则名字; 语句可以修改表的排序规则

注意:上面的语句修改表排序规则,对现有字段的排序规则没影响,只影响后续新添加字段的默认排序规则

  1. mysql> show create table tc\G 
  2. *************************** 1. row *************************** 
  3.        Table: tc 
  4. Create TableCREATE TABLE `tc` ( 
  5.   `id` int(11) NOT NULL
  6.   `a` char(32) NOT NULL
  7.   `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  8.   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. 1 row in set (0.00 sec) 
  11.  
  12. mysql> alter table tc collate latin1_swedish_ci; 
  13. Query OK, 0 rows affected (0.00 sec) 
  14. Records: 0  Duplicates: 0  Warnings: 0 
  15.  
  16. mysql> show create table tc\G 
  17. *************************** 1. row *************************** 
  18.        Table: tc 
  19. Create TableCREATE TABLE `tc` ( 
  20.   `id` int(11) NOT NULL
  21.   `a` char(32) CHARACTER SET utf8 NOT NULL
  22.   `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  23.   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
  24. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  25. 1 row in set (0.00 sec) 

上面例子中,tc表初始的字符集是utf8,默认排序规则是utf8_general_ci 执行 alter table tc collate latin1_swedish_ci;SQL语句把表的排序规则修改成latin1_swedish_ci

由于latin1_swedish_ci排序规则是属于latin1字符集的,所以此时表的字符集也会修改成latin1

修改表排序规则之前,字段a的排序规则是默认的utf8_general_ci,在创建表的语句中没有显示出来

修改排序规则之后,由于表默认的排序规则发生的变化,所以字段a会显示出它原本的字符集

  • 修改字段的排序规则
  1. mysql> show create table tc\G 
  2. *************************** 1. row *************************** 
  3.        Table: tc 
  4. Create TableCREATE TABLE `tc` ( 
  5.   `id` int(11) NOT NULL
  6.   `a` char(32) CHARACTER SET utf8 NOT NULL
  7.   `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  8.   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
  9.   `d` char(32) DEFAULT NULL 
  10. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  11. 1 row in set (0.00 sec) 
  12.  
  13. mysql> alter table tc modify b char(32) not null  collate latin1_general_cs; 
  14. Query OK, 0 rows affected (0.05 sec) 
  15. Records: 0  Duplicates: 0  Warnings: 0 
  16.  
  17. mysql> show create table tc\G 
  18. *************************** 1. row *************************** 
  19.        Table: tc 
  20. Create TableCREATE TABLE `tc` ( 
  21.   `id` int(11) NOT NULL
  22.   `a` char(32) CHARACTER SET utf8 NOT NULL
  23.   `b` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
  24.   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
  25.   `d` char(32) DEFAULT NULL 
  26. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  27. 1 row in set (0.00 sec) 

上面例子中,字段 b 原始的字符集和排序规则分别是 utf8、utf8_bin

语句 alter table tc modify b char(32) not null collate latin1_general_cs;把字段 b 的排序规则修改成 latin1_general_cs, 由于latin1_general_cs 排序规则是属于latin1字符集, 所以修改之后字段 b 的字符集和排序规则都发生了变化

小结

本文介绍了MySQL字符集的排序规则,由于篇幅原因,有些点没有涉及到,更多排序规则有关的细节可以自行查阅MySQL官方文档

【编辑推荐】

  1. Linux下MySQL 8.0安装配置
  2. ElasticSearch索引 VS MySQL索引
  3. 面试官:如何查询和删除MySQL中重复的记录?
  4. MySQL事务与MVCC如何实现的隔离级别
  5. 建议收藏备查!MySQL 常见错误代码说明
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

19人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

180人订阅学习

搭建数据中心实验Lab

搭建数据中心实验Lab

实验平台Datacenter
共5章 | ITGO(老曾)

112人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微