编码类型

# 440.编码类型

MySQL支持多种编码格式。在设置编码格式时，需要考虑到数据库、表和列的不同编码格式之间的适应性，以确保数据的正确性和完整性。

‍

# 什么是编码

在讲MySQL的编码之前，读者应该对编码有基本的认知，具体可以参考拙作：

‍

# 字符集与排序规则

在数据库当中都有字符集和排序规则的概念：

字符集：当前DBMS支持什么样的字符集
排序规则：规定了字符集中字符串之间的比较、排序（平时用的字符串运算符，order by就得用到排序规则）

在MySQL中，字符集和排序规则是区分开来的，需要单独设置字符集和排序规则。当然两者也是相关联的，除非特殊需求，只要设置其一即可。设置字符集，即设置了默认的排序规则。

在SQL Server中字符集和排序规则就是合在一起的

‍

# MySQL中字符集的分类

MySQL数据库的相关字符集设置相当灵活和复杂（灵活性太高，就会引起复杂性）。具体来说，MySQL的字符集有分层的、灵活的特点。

如果没有指定字段的字符集，那么就默认使用当前表的字符集；如果没有指定当前表的字符集，那么就会默认使用当前数据库的字符集。

要了解不同字符集的分类，我们先从MySQL的系统变量（字符集相关的系统变量）开始

 show variables like 'character_set%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.1\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

‍

字段说明：

character_set_client：客户端数据使用的字符集，也就是客户端发送给mysqld的语句或数据，所使用的字符集，这里是gbk
character_set_connection：连接层字符集。服务器处理请求时会将character_set_client字符集转为character_set_connection字符集。如果数据库列的字符集和character_set_connection字符集不同，那么需要进行字符集转换，最终得到结果数据。
character_set_database：当前数据库的字符集，可以给DBMS下不同数据库用不同的字符集。
character_set_filesystem：文件系统字符集。该变量用于解释引用文件名的字符串文字（例如source命令就用到了外部文件）
character_set_results：查询结果字符集。mysqld 在返回查询结果集或者错误信息到客户端时，使用的编码字符集
character_set_server：服务器字符集，默认的字符集。如果创建数据库时，不指定字符集，那么就会默认使用服务器的编码字符集。
character_set_system：系统元数据字符集，固定为UTF8字符集。它是系统元数据（表名、字段名等）存储时使用的编码字符集，该字段和具体存储的数据无关。

character_set_client、character_set_connection、character_set_results这3个参数值是由客户端每次连接进来设置的，和服务器端没关系。

在JDBC (opens new window)里，就设置过连接字符串，告知服务器客户端用的编码

‍

我们可以做个分类。在服务器内用的编码：

system：系统编码级别，就是当前操作系统使用的编码，固定值，无需关注
server：服务编码级别，即当前MySQL服务所使用的编码
database：数据库级别，如果新建数据库时未指定字符集编码，则使用server级别的设置
table：表级别。这个指定是MySQL独有的，而且只能通过SQL在建表或修改表时指定，在标准SQL中，没有可指定表编码的语法
column。列级别，设置列的编码。建表或修改列时设置，标准sql语法。

‍

与客户端通讯用的编码，通讯流程如下：

服务端使用系统变量character_set_client来处理客户端发来的语句
服务端会把客户端发来的语句（以character_set_client 编码）转换为character_set_connection编码
系统变量character_set_results用来把数据以该编码方式返回给客户端

‍

# 查看当前MySQL支持的字符集

MySQL不同版本支持的字符集有所不同，你可以使用命令show charset或show character set来查看当前MySQL版本支持的字符集。

show character set;

-- 结果：
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

第一列表示字符集、第二列表示字符集描述、第三列表示默认排序规则、第四列表示字符集的一个字符占用的最大字节数。

‍

也可以使用下面SQL语句查询，效果是一样的。

select * from information_schema.character_sets;

‍

# 字符集的选择

要尽可能选择utf8mb4这个字符集，而不选择utf8。

在MySQL中，utf8编码并不是真正意义上的utf8，而是utf8mb3，最多支持3个字节，一些生僻字和emoji是不支持。

后来，Unicode发布了一个完善的标准，为了兼容Unicode，MySQL新增了utf8mb4字符集，并且在官方文档醒目的提醒了大家说utf8mb3会在未来的版本中删除，并建议用户优先使用utf8mb4。

更多内容可以参考：数据库与编码 (opens new window)

‍

# 排序规则

一般来说排序规则不用太多关注，这里简单讲讲。

排序规则的命名方式：

以对应的字符集开头，比如排序规则 utf8_general_ci 对应 utf8 字符集的排序规则
当排序规则特指某种语言时，则中间的部分就为这种语言的名字，比如big5_chinese_ci 对应的就是中国（台湾比较常用big5）

以自己的特定属性结尾，代表是否大小写敏感，重音敏感以及是否是二进制的。例如 utf8_general_ci 的 ci 就表示大小写不敏感

结尾符	代表含义
_ai （accent insensitive）	重音不敏感
_as （accent sensitive）	重音敏感
_ci （case insensitive）	大小写不敏感
_cs （case sensitive）	大小写敏感
_bin	二进制

‍

排序规则是如何指定的？

当仅指定了字符集而没有指定排序规则时，则会使用该字符集的默认排序规则
当仅指定了排序规则而没有字符集时，则在该排序规则名称上含有的字符集会被使用
当数据库创建时没有指定这两项，则使用server级别的字符集和排序规则

‍

每个指定的字符集都会有一个或多个支持的排序规则，可以通过两种方式查看，一种是查看information_schema.collations表，另一种是通过【show collation】命令查看

show collation where charset ='utf8mb4';

-- 结果（部分）：
mysql> show collation where charset ='utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
...........

1
2
3
4
5
6
7
8
9
10
11
12

‍

# 查看某个数据库的字符集

语法：show variables like 'character_set_database'; 举例：

mysql> use shop;
mysql> show variables like 'character_set_database';


-- 结果：
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

‍

或者查看建库语句：show create database <数据库名>;

show create database <数据库名>;

-- 例如：
show create database shop;

-- 结果：可以看到注释里有说明，用的是默认编码utf8mb4 
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| shop     | CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12

‍

要查看当前MySQL实例下面所有数据库的字符集和排序规则，可以使用下面脚本：

 SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA;


-- 结果：
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| mysql              | utf8mb4                    | utf8mb4_0900_ai_ci     |
| information_schema | utf8mb3                    | utf8mb3_general_ci     |
| performance_schema | utf8mb4                    | utf8mb4_0900_ai_ci     |
| sys                | utf8mb4                    | utf8mb4_0900_ai_ci     |
| sakila             | utf8mb4                    | utf8mb4_0900_ai_ci     |
| world              | utf8mb4                    | utf8mb4_0900_ai_ci     |
| shop               | utf8mb4                    | utf8mb4_0900_ai_ci     |
| test               | utf8mb4                    | utf8mb4_0900_ai_ci     |
+--------------------+----------------------------+------------------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

‍

# 查看表的字符集

‍

同理，可以看建表语句：

show create table <表名>;


-- 例如：
show create table Product;

-- 结果：可以看到虽然之前创建表的时候，没有指定字符集，但默认使用的是utf8mb4 
+---------+-------------+
| Table   | Create Table  |
+---------+----------+
| Product | CREATE TABLE `product` (
  `product_id` char(4) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `product_type` varchar(32) NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

‍

# 指定字符集和排序规则

在创建时指定：

-- 创建数据库时指定字符集编码和排序规则
CREATE DATABASE  test2
 	CHARACTER SET utf8mb4
 	COLLATE utf8mb4_general_ci; 


-- 创建表时指定字符集编码和排序规则
create table tt2(
	addr varchar(50)
) character set utf8mb4 collate utf8mb4_general_ci;


-- 创建表时指定某个列的字符集编码和排序规则
create table tt4(
	name varchar(50) character set utf16 collate utf16_general_ci,
	addr varchar(50)
)character set utf8mb4 collate utf8mb4_general_ci;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

‍

修改字符集：

-- 数据库级别
ALTER DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci


-- 更改表的编码级别
ALTER TABLE testtable CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_general_ci; 


-- 列级别
ALTER TABLE testtable MODIFY `colname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1
2
3
4
5
6
7
8
9
10

‍

# 总结

一般来说，使用utf8mb4即可。

‍

# 参考资料

MySQL字符集与排序规则总结 - 潇湘隐者 - 博客园 (opens new window)

第六节：MySQL字符集和排序规则详解 - Yaopengfei - 博客园 (opens new window)

上次更新: 2024/1/23 16:20:41

← 表注释与字段注释