MySQL · 字符集

发表于 | 更新于 | 分类于 | 阅读次数：

简介

字符集：计算机中只能存储二进制数据，那字符串该如何存储字符串呢？是靠建立字符与二进制数据的映射关系，将一个字符映射成一个二进制数据的过程叫做编码，将一个二进制数据映射到一个字符的过程叫做解码，抽象出一个字符集的概念来描述某个字符范围的编码规则。

比较规则：英文名为 collation，用于比较两个字符的大小。

常用字符集

  • ASCII 字符集

    共收录 128 个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。

    1
    2
    'L' ->  01001100（十六进制：0x4C，十进制：76）
    'M' ->  01001101（十六进制：0x4D，十进制：77）

  • ISO 8859-1 字符集

    共收录 256 个字符，是在 ASCII 字符集的基础上又扩充了 128 个西欧常用字符（包括德法两国的字母），这个字符集也有一个别名 latin1。

  • utf8 字符集

    收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用 1～4 个字节，比方说这样：

    1
    2
    'L' ->  01001100（十六进制：0x4C）
    '啊' ->  111001011001010110001010（十六进制：0xE5958A）

    Tips： utf8 只是 Unicode 字符集的一种编码方案，Unicode 字符集可以用 utf8、utf16、utf32 这几种编码方案，utf8 使用 1～4 个字节编码一个字符。

查看字符集（character set/charset）

1
2
3
4
5
6
7
mysql> show charset like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

查看比较规则（collation）

1
2
3
4
5
6
7
mysql> show collation like 'utf8%general_ci';
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| utf8_general_ci    | utf8    | 33 | Yes     | Yes      |       1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes     | Yes      |       1 |
+--------------------+---------+----+---------+----------+---------+

utf8 字符集默认的比较规则是 utf8_general_ci，它是一种通用的比较规则，_ci 结尾，表示不区分大小写。

字符集和比较规则

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

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------+
| Variable_name            | Value                                              |
+--------------------------+----------------------------------------------------+
| character_set_client     | utf8                                               |
| character_set_connection | utf8                                               |
| character_set_database   | utf8                                               |
| character_set_filesystem | binary                                             |
| character_set_results    | utf8                                               |
| character_set_server     | latin1                                             |
| character_set_system     | utf8                                               |
| character_sets_dir       | /usr/local/mysql-5.7.23-el7-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------+
  • character_set_server： 默认的内部操作字符集
  • character_set_client： 服务器解码请求时使用的字符集
  • character_set_connection：连接层字符集
  • character_set_results： 服务器向客户端返回数据时使用的字符集
  • character_set_database： 当前选中数据库的默认字符集
  • character_set_system： 系统元数据(字段名等)字符集

服务器级别

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
# 控制参数
character_set_server
collation_server

# 配置文件设置
[client]
default-character-set  = utf8

[mysql]
default-character-set  = utf8

[mysqld]
character-set-server   = utf8
collation-server       = utf8_general_ci

# 查看方法
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | utf8   |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | utf8_general_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
# 控制参数
character_set_database
collation_database

# 创建语法
CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
    
# 查看方法
mysql> use test;
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

规则

  • 创建数据库时，指定了 CHARACTER SET 或 COLLATE，则以对应的字符集、比较规则为准。
  • 创建数据库时，没有指定字符集、排序规则，则以 character_set_server、collation_server 为准。
  • character_set_database 和 collation_database 这两个系统变量是只读的，不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

表级别

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
# 创建语法
CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]

# 查看方法
mysql> show table status from test\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 6
    Create_time: 2019-09-24 18:00:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment:

规则

  • 明确了 charset_name、collation_name，则采用 charset_name、collation_name。
  • charset_name、collation_name 均未明确，则采用数据库的字符集、比较规则设置。

列级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建语法
CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

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

# 查看方法
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test" AND TABLE_NAME="test" AND COLUMN_NAME="name";
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME  |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
+--------------------+-----------------+

规则

  • 明确了 charset_name、collation_name 均明确，则采用 charset_name、collation_name 为准。
  • charset_name、collation_name 均未明确，则以表的字符集、比较规则为准。

小结

  • 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
  • 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
  • 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

总结

  • 字符集是某个字符范围的编码规则。
  • 比较规则是针对某个字符集中的字符比较大小的一种规则。
  • MySQL 中一个字符集可以有若干种比较规则，其中有一个默认的比较规则。
  • MySQL 有四个级别的字符集和比较规则，优先级排序：列 > 表 > 库 > 服务器。