MySQL编码问题十有八九不是数据库单方面的事，而是配置文件、连接串、表结构三者没对齐导致的；把服务器上的my.cnf按本文步骤设置成utf8mb4，再配合编码工具统一客户端连接，乱码基本能连根拔起。
服务器上MySQL编码配置文件到底改哪里
很多朋友登录服务器第一件事就是打开my.cnf，但改完重启发现乱码依旧，行业共识认为，MySQL编码配置文件通常不止一个生效位置，得先确认你改对了文件。
查找服务器上MySQL读取的配置文件路径
执行以下命令可以看到MySQL启动时依次读取了哪些配置文件：
mysqld --verbose --help | grep -A 1 "Default options"
输出结果中列出的路径顺序就是加载顺序,比如/etc/my.cnf、/etc/mysql/my.cnf、~/.my.cnf等。后读取的文件会覆盖先读取的同名参数，所以检查时一定要看最终生效的是哪一份，实际运维中，Debian/Ubuntu系统习惯把配置拆到/etc/mysql/mysql.conf.d/和/etc/mysql/conf.d/两个目录下，而CentOS/RHEL系统则集中在/etc/my.cnf，如果你在多个地方都改了，就等着看哪边覆盖哪边吧。
my.cnf中必改的四项编码参数
在[mysqld]段下，需要确保这四个参数全部指向utf8mb4：
[mysqld] character_set_server = utf8mb4 collation_server = utf8mb4_unicode_ci character_set_filesystem = utf8mb4 skip-character-set-client-handshake
其中skip-character-set-client-handshake这一项很关键，它让服务器忽略客户端传来的字符集信息，强制使用服务端统一设置，不过这个参数比较霸道，如果你有老程序还在用latin1连接，加了它反而会出问题，稳妥做法是不加这一项，转而让客户端主动指定编码。
改完配置后,重启MySQL服务并执行SHOW VARIABLES LIKE 'character_set%';验证，注意，此时已存在的库和表不会自动转换，还需要手动处理存量数据。
MySQL乱码问题往往出在连接层而不是服务器配置
我处理过不少线上乱码案例,发现一个规律：服务端配置得很规矩，但程序连上去还是乱码，这里有个高频坑，就是连接字符串里没有指定编码。
JDBC和PHP连接串的统一编码写法
Java应用在JDBC连接串上必须加上characterEncoding参数：
jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=utf8mb4&connectionCollation=utf8mb4_unicode_ci
PHP的PDO连接则这样写：
$pdo = new PDO('mysql:host=localhost;dbname=dbname;charset=utf8mb4', 'user', 'pass');
Python的pymysql则在connect时传charset='utf8mb4'参数。
命令行登录后立即执行set names utf8mb4
数据库连接建立后,服务器会根据客户端发送的握手包判断字符集，如果客户端没有明确指定，就可能回退到latin1，登录MySQL后马上执行：
SET NAMES utf8mb4;
这个命令同时设置了character_set_client、character_set_connection、character_set_results三个变量，在MySQL 8.0中，默认字符集已是utf8mb4，但在MySQL 5.7及更早版本中这个步骤能避免大多数乱码现象，为了一劳永逸，可以在my.cnf的[client]段和[mysql]段都加上default-character-set = utf8mb4，这样命令行连接时就不用手动敲set names了。
编码工具在排查MySQL字符集问题时的三个关键作用
做编码排查的时候,光靠眼睛看是不够的，得用工具把每个环节的字符集数据量化出来，这里说的编码工具不只是图形化客户端，还包括命令行里的小工具链。
用system variables和metadata排查编码不一致
查询服务器当前所有字符集相关变量：
SHOW VARIABLES LIKE 'character_set_%'; SHOW VARIABLES LIKE 'collation_%';
查看某个数据库的默认字符集：
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;
查看某张表的结构：
SHOW CREATE TABLE table_nameG
把这三层结果放在一起对比,就能快速定位是服务器层、库层还是表层的字符集设置不一致。
用hex函数定位乱码源头
乱码分两种：一种是存储时编码就错了，另一种是存储正确但读取时解码错了，用HEX()
函数可以把字符串转成十六进制，直接看存储层面的字节：
SELECT HEX(content) FROM articles WHERE id = 123;
如果十六进制结果是以E4B8AD等符合UTF-8编码规则的字节序列开头，说明存储没问题，问题出在读取连接层，如果十六进制是D6D0这样的GBK字节，说明写入时就用了GBK编码，这就需要先转码再存储，这个方法在排查“问号乱码”和“方块乱码”时有奇效。
编码转换工具处理存量乱码数据
对于已经乱掉的存量数据,可以用MySQL自带的CONVERT()函数处理：
UPDATE articles SET content = CONVERT(CAST(CONVERT(content USING latin1) AS BINARY) USING utf8mb4);
这个操作的原理是：先把utf8mb4字节流按latin1解码（还原成原始字节），再把这批字节按utf8mb4重新解码，这是处理“UTF-8被latin1读取导致乱码”的经典恢复手法，执行前务必先备份数据，并先用SELECT语句预览转换结果，确认无误后再执行UPDATE。
MySQL 5.7和8.0在编码配置文件上的差异对比
| 对比项 | MySQL 5.7 | MySQL 8.0 |
|---|---|---|
| 默认字符集 | latin1 | utf8mb4 |
| 配置文件是否需要显式设置 | 强烈建议设置 | 可少写但建议保留 |
| utf8mb4的默认排序规则 | utf8mb4_general_ci | utf8mb4_0900_ai_ci |
| 对emoji的支持 | 需设置utf8mb4 | 默认支持 |
MySQL 8.0中，utf8mb4_0900_ai_ci排序规则在性能和准确性上优于5.7时代的utf8mb4_general_ci，但在MySQL 5.7中如果强行指定这个排序规则会报错，跨版本迁移时，要特别注意排序规则的兼容性问题。
如果从5.7升级到8.0，建议在升级前把库和表的排序规则统一转换为utf8mb4_0900_ai_ci，避免升级过程中自动转换带来的锁表时间。
服务器上MySQL编码配置文件常见的三个坑
只改配置文件不重建表结构
之前讲过,character_set_server只影响新建的表，已有的表必须手动转换，批量转换工具可以这样生成SQL语句：
SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database';
把输出结果复制出来,一条条执行，注意大表执行时会锁表，建议在业务低峰期操作。
配置文件权限不对导致MySQL无法读取
my.cnf文件权限如果设置成777，MySQL会拒绝读取并报World-writable config file is ignored错误，正确权限是644，属主为root：
chown root:root /etc/my.cnf chmod 644 /etc/my.cnf
这个坑很隐蔽,因为MySQL启动不报错，只是默默忽略配置文件，导致所有修改都无效。
连接池缓存了旧连接
修改完配置文件、重启完服务，应用层连接池里的长连接可能还持有旧的字符集设置，跑Java应用的同学尤其要注意，Druid、HikariCP这些连接池都有连接存活时间配置，改完配置后，让连接池把旧连接全部淘汰，或者直接重启应用服务，否则排查了半天发现测试连接正常、线上连接还是乱码。
Q&A：MySQL编码配置的高频问题
服务器上MySQL编码配置文件修改后需要重启吗？
需要。character_set_server和collation_server是只读参数，不能用SET GLOBAL在线修改，执行SHOW VARIABLES LIKE 'character_set_server';确认修改结果，重启前检查[mysqld]段配置语法，可用mysqld --validate-config做语法校验。
设置utf8mb4后表结构还是latin1怎么处理？
需要手动转换存量表,用ALTER TABLE CONVERT TO CHARACTER SET utf8mb4逐表转换，或先修改库默认字符集再处理表，转换前备份全部数据，转换期间关注表锁定和空间占用情况。
编码转换工具能修复所有类型的乱码吗？
不能,编码转换工具能处理因字符集误读导致的乱码，比如UTF-8字节被按latin1显示、GBK内容混入UTF-8表等情况，但如果数据在存储时就已经被截断或丢弃了无法映射的字节，造成的损坏是不可逆的，任何工具都无法还原，服务器上MySQL编码配置文件和编码工具的共同目标，就是从根本上阻止乱码的产生，而不是事后修复。
首发原创文章，作者：王坚，如若转载，请注明出处：https://test.idctop.com/article/581769.html
