MySQL字符集的不同级别和效果

2020年3月17日

摘要：MySQL字符集的不同级别和效果

MySQL字符集包括Charset（字符集）和collation（校对规则），字符集决定MySQL存储字符串的方式，校对规则决定比较字符串的方式。字符集和校对规则是一对多的关系，每个字符集至少对应一个校对规则。

那么字符集该如何设置呢？其共有4个级别，分别是服务器级、数据库级、表级和字段级，不同的级别作用也不相同，应该从业务出发进行合理设置。我们挨个来看看。

服务器级

每个MySQL服务都可以设置字符集和校对规则。通过在my.cnf中配置

[mysqld]
character-set-server=utf8mb4

或启动时配置参数

mysqld --charater-set-server=utf8mb4

或编译时指定

[root@database-one ~]# cmake . -DDEFAULT_CHARSET=utf8mb4

如果没有指定服务器字符集，那么默认使用latin1为字符集，使用该字符集的默认校对规则。

可以用命令查询服务器的字符集和校对规则。

root@database-one 20:47:  [(none)]> show variables like 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.47 sec)

root@database-one 20:47:  [(none)]> show variables like 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.00 sec)

数据库级

每个数据库也可以有自己的字符集和校对规则，可以在创建数据库的时候指定，也可以建完库后修改。需要注意的是，如果数据库里已经存在数据，修改字符集不会将已有的数据按新字符集重新进行处理。

设置数据库字符集时有几种情况：

指定了字符集和校对规则，按指定的使用。
指定了字符集没有指定校对规则，使用指定字符集的默认校对规则。
指定了校对规则没有指定字符集，使用与指定校对规则对应的字符集。
没有指定字符集和校对规则，使用所在服务器的字符集和校对规则。

推荐创建数据库时根据业务需求出发，选择并设置好字符集和校对规则。可以用命令查询数据库的字符集和校对规则。

root@database-one 21:09:  [(none)]> use gftest;
Database changed
root@database-one 21:09:  [gftest]> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

root@database-one 21:09:  [gftest]> show variables like 'collation_database';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

root@database-one 21:09:  [gftest]> use sakila;
Database changed
root@database-one 21:09:  [sakila]> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

root@database-one 21:10:  [sakila]> show variables like 'collation_database';
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set (0.01 sec)

从上面看到，gftest数据库使用的是utf8字符集，sakila数据库使用的是latin1字符集。

表级

每个表也可以有自己的字符集和校对规则，可以在创建表的时候指定，也可以建完表后修改。需要注意的是，如果表里已经存在数据，修改字符集不会将已有的数据按新字符集重新进行处理。

设置表字符集时有几种情况：

指定了字符集和校对规则，按指定的使用。
指定了字符集没有指定校对规则，使用指定字符集的默认校对规则。
指定了校对规则没有指定字符集，使用与指定校对规则对应的字符集。
没有指定字符集和校对规则，使用所在数据库的字符集和校对规则。

推荐创建表时根据业务需求出发，选择并设置好字符集和校对规则，而且建议同一业务的表尽量使用相同的字符集和校对规则，方便多表数据处理。可以用命令查询表的字符集和校对规则。

root@database-one 21:16:  [gftest]> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (7.18 sec)

root@database-one 21:17:  [gftest]> show create table testcsv \G
*************************** 1. row ***************************
       Table: testcsv
Create Table: CREATE TABLE `testcsv` (
  `i` int(11) NOT NULL,
  `c` char(10) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

列级

MySQL也可以在列级指定某个列使用特定的字符集和校对规则，主要用于同一个表里不同字段需要使用不同字符集的情况。但这种场景很少，而且建议尽量规避这样的用法，比如在表级设置能覆盖这些需求的超集字符集。

上面4种级别的字符集都是用于数据保存时的，其实客户端和服务器之间的交互也受到字符集和校对规则的影响。MySQL提供了character_set_client、character_set_connection和character_set_results三个参数，分别设置客户端、连接和返回结果的字符集。通常情况下，这3个字符集应该是相同的，才可以保证用户传入的数据可以正确的读出使用。使用set names命令可以同时修改这3个参数。

root@database-one 21:29:  [gftest]> show variables like 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

root@database-one 21:29:  [gftest]> show variables like 'character_set_connection';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_connection | utf8  |
+--------------------------+-------+
1 row in set (0.01 sec)

root@database-one 21:29:  [gftest]> show variables like 'character_set_results';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| character_set_results | utf8  |
+-----------------------+-------+
1 row in set (0.01 sec)

root@database-one 21:29:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
+--------+------+---------+------------+--------+
5 rows in set (0.02 sec)

root@database-one 21:30:  [gftest]> set names latin1;
Query OK, 0 rows affected (0.00 sec)

root@database-one 21:30:  [gftest]> show variables like 'character_set_client';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_client | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)

root@database-one 21:30:  [gftest]> show variables like 'character_set_connection';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_connection | latin1 |
+--------------------------+--------+
1 row in set (0.01 sec)

root@database-one 21:30:  [gftest]> show variables like 'character_set_results';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| character_set_results | latin1 |
+-----------------------+--------+
1 row in set (0.00 sec)

root@database-one 21:30:  [gftest]> select * from emp;
+-------+------+---------+------------+--------+
| ename | age  | sal     | hiredate   | deptno |
+-------+------+---------+------------+--------+
| ??    |   27 | 8400.00 | 2019-12-08 |     10 |
| ??    |   30 | 9100.00 | 2018-04-09 |     10 |
| ??    |   24 | 6000.00 | 2020-01-05 |     20 |
| ??    |   26 | 7200.00 | 2018-07-06 |     30 |
| ??    |   29 | 8700.00 | 2017-05-28 |     30 |
+-------+------+---------+------------+--------+
5 rows in set (0.00 sec)

root@database-one 21:31:  [gftest]> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到emp表的字符集为utf8，当连接字符集与表一致时，表中数据可以正常显示，当调整连接字符集与表不一致时，表中字符集不兼容列的数据就无法正常显示了。

最后，补充一下，MySQL的元数据默认是使用utf8字符集来保存的，可以查看character_set_system参数来确认。

root@database-one 21:34:  [gftest]> SHOW VARIABLES LIKE 'character_set_system';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_system | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

MySQL

| Tags: character, collation, MySQL, 字符集

Morinson的菜园子

morinson@163.com

MySQL字符集的不同级别和效果

发表评论取消回复

Morinson的菜园子

morinson@163.com

发表评论 取消回复

发表评论取消回复