如何修改 MySQL 中某一列的字符集？

各级别的字符集

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

• 服务器级别
• 数据库级别
• 表级别
• 列级别

执行如下SQL语句：

show variables like 'character%';

• character_set_server：服务器级别的字符集
• character_set_database：当前数据库的字符集
• character_set_client：服务器解码请求时使用的字符集
• character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results：服务器向客户端返回数据时使用的字符集

服务器级别

character_set_server ：服务器级别的字符集。

可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。

比如可以在配置文件中这样写：

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci # 对应的默认的比较规则

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

数据库级别

character_set_database ：当前数据库的字符集。

在创建和修改数据库的时候可以指定该数据库的字符集和比较规则。

具体语法如下：

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

ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

表级别

也可以在创建和修改表的时候指定表的字符集和比较规则。

语法如下：

CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]]

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

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

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

语法如下：

CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);

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

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

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

并不能表示汉字字符。

小结：这4个级别字符集和比较规则的联系如下。

• 如果创建或修改列时没有显式的指定字符集

  和比较规则，则该列默认用表的字符集和比较规则

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

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。

比方说我们向表t中插入一条记录：

mysql> INSERT INTO t(col) VALUES('小明');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+--------+
| s
|
+--------+
| 小明
|
+--------+
1 row in set (0.00 sec)

首先列col使用的字符集是gbk，一个字符'我'在gbk中的编码为0xCED2，占用两个字节，两个字符的实际数据就占用4个字节。如果把该列的字符集修改为utf8的话，这两个字符就实际占用6个字节。

字符集的修改

修改步骤

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集

指向的是 utf8mb3。网站开发人员在数据库设计

的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

操作1：查看默认使用的字符集。

show variables like 'character%';
-- 或者
show variables like '%char%';

上面指令在MySQL5.7中执行时如果保存有中文会报错，因为MySQL 5.7 默认的客户端和服务器都用了 latin1 ，不支持中文。

操作2：修改字符集。

vim /etc/my.cnf

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8

操作3：重新启动MySQL服务。

systemctl restart mysqld

注意：
原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

已有库&表字符集的变更

MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。

修改已创建数据库的字符集：

alter database dbtest1 character set 'utf8';

修改已创建数据表的字符集：

alter table t_emp convert to character set 'utf8';

注意：
原有的数据如果是用非utf8编码

的话，数据本身编码不会发生改变。已有数据需要导出或删除，然后重新插入。

字符集与比较规则

utf8与utf8mb4

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

• utf8mb3

  ：阉割过的utf8字符集，只使用1～3个字节表示字符

• utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符

比较规则

MySQL版本一共支持41种字符集，其中的Default collation列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。后缀表示该比较规则是否区分语言中的重音、大小写。

具体如下：

最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节。

常用操作1：

-- 查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';
-- 查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

常用操作2：

-- 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
-- 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
-- 查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
-- 修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

常用操作3：

-- 查看表的字符集
show create table employees;
-- 查看表的比较规则
show table status from atguigudb like 'employees';
-- 修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

请求到响应过程中字符集的变化

这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

为了体现出字符集在请求处理过程中的变化，这里特意修改一个系统变量的值：

mysql> set character_set_connection = gbk;
Query OK, 0 rows affected (0.00 sec)

现在假设客户端发送的请求是下边这个字符串：

SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程，我们只分析字符'我'在这个过程中字符集的转换。
现在看一下在请求从发送到结果返回过程中字符集的变化：

1. 客户端发送请求所使用的字符集。
一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：

• 类Unix系统使用的是utf8
• Windows使用的是gbk

当客户端使用的是utf8字符集，字符'我'在发送给服务器的请求中的字节形式就是：0xE68891

提示：
如果使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集（所以在学习的时候还是尽量用命令行窗口）。

2. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是character_set_client，然后把这串字节转换为character_set_connection字符集编码的字符。

由于我的计算机上character_set_client的值是utf8，首先会按照utf8字符集对字节串0xE68891进行解码，得到的字符串就是'我'，然后按照character_set_connection代表的字符集，也就是gbk进行编码，得到的结果就是字节串0xCED2 。

3. 因为表t的列col采用的是gbk字符集，与character_set_connection一致，所以直接到列中找字节值为0xCED2的记录，最后找到了一条记录。

提示：
如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行一次字符集转换。

4. 上一步骤找到的记录中的col列其实是一个字节串0xCED2，col列是采用gbk进行编码的，所以首先会将这个字节串使用gbk进行解码，得到字符串'我'，然后再把这个字符串使用character_set_results代表的字符集，也就是utf8进行编码，得到了新的字节串：0xE68891，然后发送给客户端。

5. 由于客户端是用的字符集是utf8，所以可以顺利的将0xE68891解释成字符'我'，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

总结图示如下：