MySQL中支持的字符集和排序规则

一、MySQL字符集概念

1.1 MySQL中的utf8和utf8mb4

我们常说 utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL 的开发者定义了两个概念：

utf8mb3 ：非标准的 utf8 字符集，只使用1～3个字节表示字符。

utf8mb4 ：标准的 utf8 字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情，那请使用 utf8mb4 。

1.2 字符集的查看

MySQL 支持好多好多种字符集，查看当前 MySQL 中支持的字符集可以用下边这个语句：

@localhost:[(none)]>SHOW CHARSET;

+----------+---------------------------------+---------------------+--------+

| 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 |

| hp8 | HP West European | hp8_english_ci | 1 |

| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |

| latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| ascii | US ASCII | ascii_general_ci | 1 |

| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |

| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |

| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| euckr | EUC-KR Korean | euckr_korean_ci | 2 |

| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| greek | ISO 8859-7 Greek | greek_general_ci | 1 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |

| macce | Mac Central European | macce_general_ci | 1 |

| macroman | Mac West European | macroman_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| binary | Binary pseudo charset | binary | 1 |

| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

+----------+---------------------------------+---------------------+--------+

41 rows in set (0.00 sec)

可以看到，我使用的这个 MySQL 版本一共支持 41 种字符集，其中的 Default collation 列表示这种字符集中一种默认的 比较规则 。大家注意返回结果中的最后一列 Maxlen ，它代表该种字符集表示一个字符最多需要几个字节。我把几个常用到的字符集的 Maxlen 列 ，大家可以熟悉下：

字符集名称 Maxlen

ascii 1

latin1 1

gb2312 2

gbk 2

utf8 3

utf8mb4 4

1.3 比较规则的查看

我们前边说过一种字符集可能对应着若干种比较规则， MySQL 支持的字符集就已经非常多了，所以支持的比较规则更多，我们先只查看一下 utf8 字符集下的比较规则：

@localhost:[(none)]> SHOW COLLATION LIKE 'utf8\_%';

+--------------------------+---------+-----+---------+----------+---------+

| Collation | Charset | Id | Default | Compiled | Sortlen |

+--------------------------+---------+-----+---------+----------+---------+

| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |

| utf8_bin | utf8 | 83 | | Yes | 1 |

| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |

| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |

| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |

| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |

| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |

| utf8_polish_ci | utf8 | 197 | | Yes | 8 |

| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |

| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |

| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |

| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |

| utf8_czech_ci | utf8 | 202 | | Yes | 8 |

| utf8_danish_ci | utf8 | 203 | | Yes | 8 |

| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |

| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |

| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |

| utf8_roman_ci | utf8 | 207 | | Yes | 8 |

| utf8_persian_ci | utf8 | 208 | | Yes | 8 |

| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |

| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |

| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |

| utf8_german2_ci | utf8 | 212 | | Yes | 8 |

| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |

| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |

| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |

| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |

+--------------------------+---------+-----+---------+----------+---------+

27 rows in set (0.00 sec)

这些比较规则的命名有一定规律的，具体规律如下：

比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以 utf8 开头的。后边紧跟着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较，utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。

名称后缀意味着该比较规则是否区分语言中的重音、大小写的，具体可以用的值如下：

后缀 英文释义 描述

_ai accent insensitive 不区分重音

_as accent sensitive 区分重音

_ci case insensitive 不区分大小写

_cs case sensitive 区分大小写

_bin binary 以二进制方式比较

比如 utf8_general_ci 这个比较规则是以 ci 结尾的，说明不区分大小写。

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则， SHOW COLLATION 的返回结果中的Default 列的值为YES 的就是该字符集的默认比较规则，比方说 utf8 字符集默认的比较规则就是

utf8_general_ci。

二、 字符集和比较规则的应用

2.1 MySQL 字符集和比较规则

MySQL 有4个级别的字符集和比较规则，分别是：

服务器级别

数据库级别

表级别

列级别

我们接下来仔细看一下怎么设置和查看这几个级别的字符集和比较规则。

服务器级别

MySQL 提供了两个系统变量来表示服务器级别的字符集和比较规则：

系统变量 描述

character_set_server 服务器级别的字符集

collation_server 服务器级别的比较规则

我们看一下这两个系统变量的值：

@localhost:[(none)]>SHOW VARIABLES LIKE 'character_set_server';

+----------------------+--------+

| Variable_name | Value |

+----------------------+--------+

| character_set_server | latin1 |

+----------------------+--------+

1 row in set (0.00 sec)

@localhost:[(none)]>SHOW VARIABLES LIKE 'collation_server';

+------------------+-------------------+

| Variable_name | Value |

+------------------+-------------------+

| collation_server | latin1_swedish_ci |

+------------------+-------------------+

1 row in set (0.00 sec)

可以看到在我的计算机中服务器级别默认的字符集是latin1 ，默认的比较规则是 latin1_swedish_ci 。

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server]

character_set_server=gbk

collation_server=gbk_chinese_ci

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

数据库级别

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名

[[DEFAULT] CHARACTER SET 字符集名称]

[[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名

[[DEFAULT] CHARACTER SET 字符集名称]

[[DEFAULT] COLLATE 比较规则名称];

其中的 DEFAULT 可以省略，并不影响语句的语义。比方说我们新创建一个名叫 charset_demo_db 的数据库，在创建的时候指定它使用的字符集为 gb2312 ，比较规则为 gb2312_chinese_ci ：

@localhost:[(none)]>CREATE DATABASE charset_demo_db

-> CHARACTER SET gb2312

-> COLLATE gb2312_chinese_ci;

Query OK, 1 row affected (0.01 sec)

如果想查看当前数据库使用的字符集和比较规则，可以查看下面两个系统变量的值（前提是使用 USE 语句选择当前默认数据库，如果没有默认数据库，则变量与相应的服务器级系统变量具有相同的值）：

系统变量 描述

character_set_database 当前数据库的字符集

collation_database 当前数据库的比较规则

我们来查看一下刚刚创建的 charset_demo_db 数据库的字符集和比较规则：

@localhost:[test]>use charset_demo_db;

Database changed

@localhost:[charset_demo_db]>SHOW VARIABLES LIKE 'character_set_database';

+------------------------+--------+

| Variable_name | Value |

+------------------------+--------+

| character_set_database | gb2312 |

+------------------------+--------+

1 row in set (0.00 sec)

@localhost:[charset_demo_db]>SHOW VARIABLES LIKE 'collation_database';

+--------------------+-------------------+

| Variable_name | Value |

+--------------------+-------------------+

| collation_database | gb2312_chinese_ci |

+--------------------+-------------------+

1 row in set (0.00 sec)

可以看到这个 charset_demo_db 数据库的字符集和比较规则就是我们在创建语句中指定的。需要注意的一点是：character_set_database 和 collation_database 这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。数据库的创建语句中也可以不指定字符集和比较规则，比如这样：

CREATE DATABASE 数据库名;

1

这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)

[[DEFAULT] CHARACTER SET 字符集名称]

[COLLATE 比较规则名称]]

ALTER TABLE 表名

[[DEFAULT] CHARACTER SET 字符集名称]

[COLLATE 比较规则名称]

比方说我们在刚刚创建的 charset_demo_db 数据库中创建一个名为 t 的表，并指定这个表的字符集和比较规则：

@localhost:[charset_demo_db]>CREATE TABLE t(

-> col VARCHAR(10)

-> ) CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 0 rows affected (0.04 sec)

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。假设我们的创建表 t 的语句是这么写的：

CREATE TABLE t(

col VARCHAR(10)

);

因为表 t 的建表语句中并没有明确指定字符集和比较规则，则表 t 的字符集和比较规则将继承所在数据库charset_demo_db 的字符集和比较规则，也就是 gb2312 和 gb2312_chinese_ci 。

列级别

需要注意的是，对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(

列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],

其他列...

);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

比如我们修改一下表 t 中列 col 的字符集和比较规则可以这么写：

@localhost:[charset_demo_db]>ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。比方说表 t 的字符集是 utf8 ，比较规则是 utf8_general_ci ，修改列 col 的语句是这么写的：

ALTER TABLE t MODIFY col VARCHAR(10);

ALTER TABLE t MODIFY col VARCHAR(10);

那列 col 的字符集和编码将使用表 t 的字符集和比较规则，也就是 utf8 和 utf8_general_ci 。

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。