修改字符集的注意那些事儿

一 前言

最近有开发同学遇到emoji显示问题，表结构是utf8mb4字符集，但是不支持emoji表情字符。我们在解决字符集问题的时候也重新认识了修改字符集操作的影响。

二 实验

环境准备

CREATE TABLE `t1` ( `id` int(11) NOT NULL auto_increment, `c1` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into t1(c1) values('有赞是一家SAAS服务提供商'); insert into t1(c1) values('有赞是一家SAAS服务提供商?.');

2.1 仅仅修改默认字符集

官方文档说明

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name; The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for columns that you add to a table later (for example, with ALTER TABLE ... ADD column).

修改表的默认字符集只对表的新增字段有效，对老的字段无效，不会修改老的数据的字符集类型。

初始化表数据的时候 我们向字符集类型utf8的c1字段插入了emoji字符。查询出来是乱码。

test [RW] 02:33:01 >select * from t1; +----+--------------------------------------+ | id | c1 | +----+--------------------------------------+ | 1 | 有赞是一家SAAS服务提供商 | | 2 | 有赞是一家SAAS服务提供商?. | +----+--------------------------------------+ 2 rows in set (0.00 sec) test [RW] 02:32:20 >alter table t1 default charset utf8mb4; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

修改表的默认字符集之后再次查询，c1字段依然显示乱码。

test [RW] 02:33:01 >select * from t1; +----+--------------------------------------+ | id | c1 | +----+--------------------------------------+ | 1 | 有赞是一家SAAS服务提供商 | | 2 | 有赞是一家SAAS服务提供商?. | +----+--------------------------------------+ 2 rows in set (0.00 sec)

添加新的字段，并且写入新的数据。

test [RW] 02:34:44 >alter table t1 add c2 varchar(20) not null default ''; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 test [RW] 02:34:53 >insert into t1(c1,c2) values('有赞','有赞是一家SAAS服务提供商'); Query OK, 1 row affected (0.00 sec) test [RW] 02:35:00 >insert into t1(c1,c2) values('有赞?','有赞是一家SAAS服务提供商?.'); Query OK, 1 row affected, 1 warning (0.00 sec) test [RW] 02:35:01 >select * from t1; +----+--------------------------------------+-----------------------------------------+ | id | c1 | c2 | +----+--------------------------------------+-----------------------------------------+ | 1 | 有赞是一家SAAS服务提供商 | | | 2 | 有赞是一家SAAS服务提供商?. | | | 3 | 有赞 | 有赞是一家SAAS服务提供商 | | 4 | 有赞? | 有赞是一家SAAS服务提供商? | +----+--------------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec)

此时查询 新增字段c2的可以正常显示emoji表情 ，但是老的c1 字段依然是乱码，查看表结构。

test [RW] 02:37:10 >show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(20) CHARACTER SET utf8 DEFAULT NULL, `c2` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

这里需要注意的是c1 字段并未修改为utf8mb4，和表的字符集不一致。给业务层的带来的潜在问题是表是utf8mb4,但是c1字段不支持emoji。请各位开发同学和dba 注意此风险。

2.2 修改表的字符集

ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

convert to charset 关键字会转换表的默认字符集以及字段的字符集，需要注意的是字段设计长度最好能满足转换后的大小 比如utf8 是按照3个字节计算的，utf8mb4 是按照4个字节计算的。

创建新的测试表并初始化数据

test [RW] 03:12:19 >CREATE TABLE `t2` ( -> `id` int(11) NOT NULL auto_increment, -> `c1` varchar(20) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.00 sec) test [RW] 03:12:21 >insert into t2(c1) values('有赞是一家SAAS服务提供商?.'); Query OK, 1 row affected, 1 warning (0.00 sec) test [RW] 03:12:37 >select * from t2; +----+--------------------------------------+ | id | c1 | +----+--------------------------------------+ | 1 | 有赞是一家SAAS服务提供商?. | +----+--------------------------------------+ 1 row in set (0.00 sec)

转换表和字段的字符集:

test [RW] 03:12:43 >ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

从 1 row affected 来看是copy表的ddl。大家在生产环境操作的时候必须注意对线上dml操作的影响。再次查看表结构

test [RW] 03:12:56 >show create table t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) test [RW] 03:13:01 >select * from t2; +----+--------------------------------------+ | id | c1 | +----+--------------------------------------+ | 1 | 有赞是一家SAAS服务提供商?. | +----+--------------------------------------+ 1 row in set (0.00 sec)

字段和表已经显示为utf8mb4字符集，但是数据内容并未做转换，其实还是utf8写入的数据。新增字段c2,并插入测试数据。

test [RW] 03:13:13 >alter table t2 add c2 varchar(20) not null default 'nil'; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 test [RW] 03:13:36 >insert into t2(c1,c2) values('SAAS?','星期六?'); Query OK, 1 row affected (0.00 sec) test [RW] 03:13:52 >select * from t2; +----+--------------------------------------+---------------+ | id | c1 | c2 | +----+--------------------------------------+---------------+ | 1 | 有赞是一家SAAS服务提供商?. | nil | | 2 | SAAS? | 星期六? | +----+--------------------------------------+---------------+ 2 rows in set (0.00 sec)

从上面的结果可以查看到修改字符集之后，新的c1,c2 字段数据均支持emoji.

三 总结

1.修改表的默认字符集，仅仅修改元数据，支持online ddl ，但是适应convert 转换表和字段的字符集的时候会锁表。

2.修改表的字符集和字段的字符集仅仅对新增数据有效。使用alert table defult charset 模式修改字符集可能带来字段字符集和表字符集不一样的情况。