MySQL 排序规则简介

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

<font color=CornflowerBlue>简述</font>

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

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

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

<font color=CornflowerBlue>默认排序规则</font>

  • <font color=CornflowerBlue>字符集的默认排序规则</font>

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

mysql> show character set like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)

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

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

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

  • <font color=CornflowerBlue>数据库的默认排序规则</font>

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

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

character-set-server=utf8
collation-server=utf8_general_ci

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

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

mysql> create database at;
Query OK, 1 row affected (0.00 sec)
mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| at          | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

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

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

<font color=CornflowerBlue>排序规则命名以及名字后缀</font>

  • <font color=CornflowerBlue>命名</font>

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

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

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

  • <font color=CornflowerBlue>后缀</font>

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

_ci : 不区分大小写, Case-insensitive的缩写

_cs : 区分大小写，Case-sensitive的缩写

_ai : 不区分重音，Accent-insensitive的缩写

_as : 区分重音，Accent-sensitive的缩写

_bin : 二进制

<font color=CornflowerBlue>1: 不区分大小写</font>

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
Query OK, 0 rows affected (0.02 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

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

<font color=CornflowerBlue>2: 区分大小写</font>

mysql> SET NAMES 'latin1' COLLATE 'latin1_general_cs';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

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

<font color=CornflowerBlue>3: 二进制</font>

mysql> SET NAMES 'utf8' COLLATE 'utf8_bin';
Query OK, 0 rows affected (0.00 sec)

mysql>  select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select 'à' = 'a';
+------------+
| 'à' = 'a'  |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

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

<font color=CornflowerBlue>4: 是否区分重音</font>

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

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

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

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

具体的请查看下面的例子

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
Query OK, 0 rows affected (0.02 sec)

mysql> select 'à' = 'a';
+------------+
| 'à' = 'a'  |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql>  SET NAMES 'latin1' COLLATE 'latin1_general_cs';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'à' = 'a';
+------------+
| 'à' = 'a'  |
+------------+
|          0 |
+------------+
1 row in set (0.01 sec)

<font color=CornflowerBlue>查看排序规则</font>

  • <font color=CornflowerBlue>查看数据库数据库的排序规则</font>

方法1: INFORMATION_SCHEMA.SCHEMATA表中的 DEFAULT_COLLATION_NAME 字段表示数据库的排序规则，可以通过下面的SQL语句获得指定数据库的排序规则

mysql>  select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| at          | latin1                     | latin1_swedish_ci      |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

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

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

mysql> use at;
Database changed
mysql> show variables like 'collation_database';
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)

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

  • <font color=CornflowerBlue>查看表的排序规则</font>

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

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

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

mysql> show create table tc\G
*************************** 1. row ***************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) NOT NULL,
  `a` char(32) NOT NULL,
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

  • <font color=CornflowerBlue>查看字段的排序规则</font>

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

mysql> show create table tc\G
*************************** 1. row ***************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) NOT NULL,
  `a` char(32) NOT NULL,
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

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

字段 b 的排序规则是 utf8_bin

字段 c 的排序规则是 latin1_general_cs

<font color=CornflowerBlue>修改排序规则</font>

  • <font color=CornflowerBlue>修改数据库的排序规则</font>

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

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| at          | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

mysql> alter database at collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| at          | utf8                       | utf8_bin               |
+-------------+----------------------------+------------------------+


mysql> alter database at collate latin1_swedish_ci ;
Query OK, 1 row affected (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| at          | latin1                     | latin1_swedish_ci      |
+-------------+----------------------------+------------------------+
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

  • <font color=CornflowerBlue>修改表的排序规则</font>

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

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

mysql> show create table tc\G
*************************** 1. row ***************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) NOT NULL,
  `a` char(32) NOT NULL,
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table tc collate latin1_swedish_ci;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tc\G
*************************** 1. row ***************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) NOT NULL,
  `a` char(32) CHARACTER SET utf8 NOT NULL,
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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会显示出它原本的字符集

  • <font color=CornflowerBlue>修改字段的排序规则</font>
mysql> show create table tc\G
*************************** 1. row ***************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) NOT NULL,
  `a` char(32) CHARACTER SET utf8 NOT NULL,
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `d` char(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table tc modify b char(32) not null  collate latin1_general_cs;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tc\G
*************************** 1. row ***************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) NOT NULL,
  `a` char(32) CHARACTER SET utf8 NOT NULL,
  `b` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `d` char(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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 的字符集和排序规则都发生了变化

<font color=CornflowerBlue>小结</font>

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

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 218,525评论 6赞 507
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,203评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 164,862评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,728评论 1赞 294
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,743评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,590评论 1赞 305
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,330评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,244评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,693评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,885评论 3赞 336
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,001评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,723评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,343评论 3赞 330
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,919评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,042评论 1赞 270
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,191评论 3赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,955评论 2赞 355