MySQL迁移到真·UTF8全过程

为什么要做?

写这篇总结的起因是我觉得应该深入的学习一下PHP了,结果找到了PHP最佳实践中文版,其中关于PHP和UTF8一节引起了我的兴趣。我之前还一直以为我们的数据库用的是完整的UTF8,看完之后我惊呆了。

原来我们的以为的utf8并不是真正意义上的UTF-8,或者说不是完整的,因为它只包含了不大于3个8位字节组成的字符,而4个字节组成的字符则未能包含,如𝌆、💩等常用字符。

随着现在网络用语的发达,越来越多的表情符号层出不穷,真是为这种符号的存储发愁啊,所以保险起见,以后接触到的项目中,如果我的意见可以被接受,那么我将遵守这个规则——使用utf8mb4而不是utf8。

那如果已经使用了utf8了,怎么更新呢?下面是操作步骤。

备份

对数据库的修改操作毕竟是危险的,如果你的数据很重要,最好还是备份一下数据库。

升级MySQL服务器

utf8mb4是从MySQL5.5.3版本引入的(对应相同版本的MariaDB),因此如果想使用utf8mb4,至少应升级到5.5.3版本。

修改数据库、数据表和列

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

注意,不要直接复制粘贴上面的代码,根据实际的数据进行操作。

检查列和索引键的长度

这一步很不容易想到,当我们从utf8更新到utf8mb4时,字节的长度并没有发生变化,但字符长度却变化了。 TINYTEXT类型可以容纳255个字节,在utf8编码时,可以包含255/3=85个unicode字符,而在utf8mb4时,只可以包含255/4=63.75=63个字符。因此,当这个字段的长度有可能超过63个字符时,就需要把TINYTEXT修改成TEXT从而容纳更多的字符。

修改连接、客户端、服务端字符集

这一步就是修改my.conf(在不同的操作系统中有不同的命名)。

[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

这时就可以查看更新的结果了 更新结果

修复和优化所有表

PREPARE TABLE table_name;
OPTIMIZE TABLE table_name;

如果不执行这两步可能出现数据无法更新的情况。好在用一条指令就能修复和优化所有数据库

mysqlcheck -uroot -p --auto-repaire --optimize --all-databases

这会弹出密码输入提示,然后对所有数据库执行修复和优化操作。