MySQL 字符集与排序规则

背景

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。因此，MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法

几个重要术语

• 字符集：为字母和符号的集合
• 编码：为某个字符集成员的内部表示
• 排序规则：为规定字符如何比较的指令。（排序规则也称为”校对顺序”）

为什么校对这么重要

• 排序英文其实没有想象的那么简单。例如APE、apex和Apple，它们处于正确的排列顺序吗？这还依赖于你是否想区分大小写。使用区分大小写的校对顺序，这些词会有另一种排序方式。这不仅影响排序（如用order by排序数据），还影响搜索（例如，寻找apple的where子句是否能找到APPLE）
• 在使用诸如发文或德文这样的特殊字符时，情况更复杂，在使用不基于拉丁文的字符集（日文、希伯来文、俄文等）时，情况更复杂

一、字符集

先说字符，字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、字母、数字等，比如“啊、a、1、+、！、&”等均表示一个字符。

在UTF8编码中，一个字母、数字、符号占一个字节，中文占三个字节，emoji表情和一些比较复杂的文字、繁体字则占四个字节。其中一个字节由8个位组成，位为数据存储的最小单位，每个二进制数字0或者1就是1个位。

换算公式：1byte(字节) = 8bit(位)，1KB(千字节) = 1024byte(字节)，1MB(兆) = 1024KB(千字节)。

字符集是一套符号和编码，是多个字符的集合。常见的字符集有UTF8、Unicode、GBK、GB2312、ASCCI

。在MySQL中常用的字符集有UTF8和UTF8MB4这两种，一般而言我们选择UTF8MB4，而不选择UTF8，因为MySQL中的UTF8并不是真正的UTF8字符集，它只支持三个字节，emoji表情和复杂的文字无法存储，并不能代表全部的UTF8，在5.5.3版本后新增的支持四个字节的UTF8MB4才是真正的UTF8编码，为了更好的兼容性，推荐使用UTF8MB4字符集。

此外，对于CHAR类型数据，UTF8MB4会多消耗一些空间，比如CHAR(10)，UTF8会保留30字节长度，UTF8MB4会保留40字节长度，根据MySQL官方建议，使用VARCHAR替代CHAR。同时UTF8MB4也是UTF8的超集，所以UTF8可以直接切换至UTF8MB4。

查看所支持的字符集

SHOW CHARACTER SET;

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
......
+----------+---------------------------------+---------------------+--------+

查看当前字符集

SHOW VARIABLES LIKE 'character%';

二、排序规则

排序规则指定后，它会影响我们使用 ORDER BY语句查询的结果顺序，会影响到 WHERE条件中大于小于号的筛选结果，会影响 DISTINCT、GROUP BY、HAVING 语句的查询结果。另外，mysql 建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都和排序规则有关。

命名规则：

• 排序规则的命令通常是以对应的字符集的名字为开头，并以自己的特定属性结尾，比如排序规则utf8_general_ci和latin1_swedish_ci就分别是对应utf8和latin1字符集的排序规则。
• 当排序规则特指某种语言时，则中间的部分就为这种语言的名字，比如utf8_turkish_ci和utf8_hungarian_ci就代表UTF8字符集中的土耳其语和匈牙利语。

UTF8MB4常用的排序规则有

• utf8mb4_unicode_ci: 基于标准Unicode来排序和比较，能够在各种语言之间精确排序。且在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。但是在绝大多数情况下不会发生此类复杂比较。
• utf8mb4_general_ci: 没有实现Unicode排序规则，在遇到某些特殊字符情况下，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。
• utf8mb4_bin: 将字符串的每个字符用二进制数据编译存储，区分大小写，可以存二进制的内容。

综合来说，utf8mb4_unicode_ci比较准确，utf8mb4_general_ci速度较快。utf8mb4_unicode_ci对于特殊字符的处理，在中文、英文应用中不会使用到，除非你的应用有德语、法语、俄语等，则需要使用utf8mb4_unicode_ci，否则一般选用utf8mb4_general_ci就可以了。

通常:

• bin结尾的是以二进制存储的，区分大小写，
• cs结尾的是区分大小写的
• ci结尾的不区分大小写。
• ai结尾的重音不敏感
• as结尾的重音敏感

查看所支持的校对列表

SHOW COLLATION;

+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
......
+--------------------------+----------+-----+---------+----------+---------+

查看当前校对列表

SHOW VARIABLES LIKE 'collation%';

指定字符集

排序规则设置可以分为：MySQL实例级别、库级别、表级别、列级别以及SQL指定。

优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置

也就是说，如果SQL语句中指定了排序规则，则以其指定为准，否则以下一级为准（也就是列级别），如果列级别没有指定，默认是继承表级别的设置，以此类推。

1、MySQL实例级别设置

实例级别的排序规则设置就是 MySQL 配置文件或启动指令中的 collation_connection 系统变量。

可以通过修改mysql的配置文件 my.ini来修改相应的排序规则，修改好后，重启mysql服务。

# 查看MySQL实例级别的字符集
show variables like '%character%';

# 查看MySQL实例级别的排序规则
show variables like '%collation%';

2、库级别设置

在创建数据库的时候指定数据集和排序规则,指定完之后创建表的时候就可以不指定了，如果不指定就是跟随库的默认设置。

CREATE DATABASE TESTDB 
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看库级别排序规则

show create database 数据库名;

3、表级别设置

在创建表的时候指定表的数据集和排序规则

CREATE TABLE user(
  `id` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查看表级别排序规则

show table status from 数据库名 like '表名';
-- testdb 为数据库名， user 为要查看的表名

4、列级别设置

在创建表的时候指定列的数据集和排序规则。
只有在字符才有，也就是字符类型，char、varchar、tinytext等等。。。

CREATE TABLE `test`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
);

查看列级别排序规则

show full columns from test;

2.3.5、SQL指定设置

SQL语句中指定排序规则,当然不能乱设置，假如表用的utf8的，你设置utf8mb4_unicode_ci，他是会直接查询报错的。

SELECT
	id,
  name 
FROM
	test 
ORDER BY
	name COLLATE utf8_unicode_ci;