MySQL utf8->utf8mb4

JerryZhang 2017/06/02

线上业务用的都是 utf8 编码,当有人输入了非 utf8 字符时,比如 emoji 表情,数据库会报错:Incorrect string value: '\\xF0\\x9F\\x90\\xA8' for column 'name' at row 1,这是因为 utf8 不能存储四字节的字符(似乎 utf8 只能存储 1-3 个字节,具体没研究过)。

MySQL 5.5.3 之后就已经支持 utf8mb4,现在大部分线上的业务 MySQL 都是 5.6 了,自然都是支持的。具体步骤如下:

将 MySQL-Server 默认编码改为 utf8mb4

找到 MySQL 的配置文件,一般在 /etc/mysql/my.cnf 或者 /etc/my.cnf,如果找不到的话可以试一下 mysql --help | grep cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

保存之后需要重启 MySQL 服务: /etc/init.d/mysql restart

然后查看是否生效:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

character_set_system 总是 utf8,不能被修改,具体请看:https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_system

对于已经存在的业务,需要手动修改「数据库」和「表」的编码:

# 数据库
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 数据库中的每个表
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 表中的字符列
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

有一点需要注意的是,将 utf8 转换成 utf8mb4 的过程中,列或者索引中的字节长度没有发生变化(但是 utf8mb4 占的字节比 utf8 多,3字节变成了4字节),也就是说可以存储的字节变少了。这可能是线上业务升级最为繁琐的一部分了,也存在风险。

对于新的项目,只需要在创建数据库的时候指定 utf8mb4 即可:create database xxx charset='utf8mb4'

客户端在链接的时候需要同样是需要指定编码,比如 Django 应在 setting 中添加:

DATABASES = {
    'default': {
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
            'charset': 'utf8mb4',
        }
    }
}

更多请看:How to support full Unicode in MySQL databases