1. 为什么你的MySQL存不了Emoji从编码原理说起先别急着在代码里过滤Emoji或者把它们替换成“”这种占位符。问题根本不在前端而在你数据库的字符集上。我做过的几个项目里凡是涉及用户昵称、评论内容、签名这类自由文本字段的几乎都遇到过“明明线上跑得好好的突然某天插入一条带表情的数据就报错了”的情况。报错信息通常长这样Incorrect string value: \xF0\x9F\x98\x80 for column nickname at row 1当时第一反应是数据有问题查了半天才发现不是数据脏是MySQL的utf8根本不是你以为的那个UTF-8。如果你也被这个坑绊过或者还没遇到过但想提前避开这篇文章应该能帮你把整条链路理清楚从字符集原理到实际迁移命令再到连接层和应用层的配套改动照着做就能把Emoji稳稳存进去。1.1 先复现一下最常见的报错现场我常用的复现方式特别简单建一张测试表默认字符集用utf8然后插入一条包含笑脸表情的记录CREATE TABLE test_emoji ( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(255) ) DEFAULT CHARSETutf8; INSERT INTO test_emoji (content) VALUES (这是一条测试);在MySQL 5.7或8.0的环境下只要连接字符集也是utf8大概率直接给你抛Incorrect string value错误。如果连接层用的还是latin1那更惨连中文都存不进去。这个报错里那串\xF0\x9F\x98\x80就是UTF-8编码下Emoji“”的十六进制表示一共四个字节。问题就出在这四个字节上。MySQL里那个叫utf8的字符集实际最多只支持三个字节。一个Emoji占四个字节超出了它的表达能力自然就写不进去。更准确地说MySQL的utf8其实是utf8mb3的别名只是叫了很多年“utf8”让大家误以为它就是完整的UTF-8实现。1.2 三字节和四字节的矛盾utf8 vs utf8mb4这里得把概念稍微掰开揉碎讲一下。Unicode是一个字符集它给每个字符分配了一个唯一的编号也就是“码点”比如“”的码点是U1F600。而UTF-8是一种编码方式负责把这个码点转换成字节序列方便存储和传输。UTF-8是变长编码常见的英文字符占1个字节大部分拉丁文、希腊文、中文等占2到3个字节而Emoji、部分生僻汉字、一些特殊符号则占4个字节。换句话说完整的UTF-8编码必须能处理4字节的序列而MySQL的utf8mb3天生只能处理1到3字节等于自废一条腿。utf8mb4就是来解决这个问题的其中的“mb4”就是“maximum bytes 4”的意思它才是真正完整的UTF-8。从MySQL 5.5.3开始引入之后一直作为推荐字符集存在。到了MySQL 8.0utf8mb4已经成了默认字符集但很多从5.7甚至5.6时代迁移过来的老库依然停留在老配置上。还有个细节容易被忽略字符集和排序规则是两回事。字符集决定能存哪些字符排序规则决定字符串怎么比较和排序。比如utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_0900_ai_ci这些都是排序规则选哪个会影响查询时的等值匹配和排序行为后面我会单独说怎么选。1.3 怎么确认当前字符集配置有问题动手改之前先查一下当前到底哪个环节的字符集不对。我一般用几条SQL直接看现状-- 查看当前连接的字符集和排序规则 SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;重点看这几个值character_set_client客户端发送语句时使用的字符集character_set_connectionMySQL收到语句后转换使用的字符集character_set_results结果集返回给客户端时使用的字符集character_set_database当前默认数据库的字符集character_set_serverMySQL实例级的默认字符集再看表和列的字符集SHOW CREATE TABLE test_emoji;如果显示DEFAULT CHARSETutf8而你的业务里确实会出现Emoji基本就可以判断需要迁移了。还有一种情况是数据库是utf8mb4但某个列还是utf8或者连接层指定了utf8这些都需要逐一排查千万别只看一处就下结论。2. 核心方案utf8mb4全面迁移实操把方案说清楚全链路都改成utf8mb4。包括存储层——数据库、表、列以及连接层——客户端连接配置和连接串参数。只要有一层漏掉都会出现写入失败或者乱码。下面按顺序来。2.1 迁移前的检查清单动手前先过一遍清单能省掉后面很多麻烦确认MySQL版本不低于5.5.3推荐5.7以上8.0最好确认所有应用连接串里的字符集参数是utf8mb4不光是MySQL命令行确认索引字段改了utf8mb4后不会超过索引长度上限特别是varchar字段确认排序规则统一避免表之间关联查询时出现字符集不一致的报错确认备份可用大表迁移前先在测试环境刷一遍这里重点说一下索引长度问题这是很多人改完字符集之后才发现的隐患。utf8mb4一个字符最多4字节而InnoDB在MySQL 5.6之前默认索引键长度上限是767字节之后有了innodb_large_prefix5.7.7开始默认开启8.0直接移除了该参数索引键长度上限扩展到3072字节。举个例子一个varchar(255)的字段如果原来用utf8最多占765字节没超上限。改成utf8mb4后最多占1020字节依然没超3072字节一般OK。但如果用varchar(255)建了多列联合索引三列就是3060字节逼近3072上限一不小心就报Specified key was too long。这个在后面“应用层与索引配套改造”里详细说。2.2 一步步完成数据库、表、列级字符集修改最直接的场景是把一个老库整体迁到utf8mb4。假设库名是test_emoji修改数据库级字符集ALTER DATABASE test_emoji CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意这行命令只会改数据库的默认字符集已经存在的表不会跟着变。所以还要对每一张表执行。批量生成ALTER语句的通用写法是这样SELECT CONCAT(ALTER TABLE , TABLE_NAME, DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;) FROM information_schema.TABLES WHERE TABLE_SCHEMA test_emoji;把查询结果复制出来执行就能把每张表的默认字符集改掉。但这还不够表里已有的列不一定继承了表的新字符集尤其那些建表时单独指定过字符集的列需要单独处理。修改列级字符集我常用的生成语句SELECT CONCAT(ALTER TABLE , TABLE_NAME, MODIFY , COLUMN_NAME, , COLUMN_TYPE, CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, IF(IS_NULLABLE NO, NOT NULL, ), IF(COLUMN_DEFAULT IS NOT NULL, CONCAT( DEFAULT \, COLUMN_DEFAULT, \), ), ;) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA test_emoji AND (CHARACTER_SET_NAME ! utf8mb4 OR COLLATION_NAME ! utf8mb4_unicode_ci);有两个容易踩的坑第一个MODIFY COLUMN会重建表在数据量很大的表上可能耗时很长建议低峰期执行或者用后续版本支持的新语法配合工具平滑变更。第二个上面拼接的语句没有处理字段上的自增、默认值、注释等属性生产环境不建议手写拼接SQL去改列推荐把表结构用工具导出成DDL手工调整后再应用或者用专门的在线DDL方案。对于一个数据量可控的表直接三条SQL就搞定ALTER TABLE test_emoji CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CONVERT TO会同时转换表里的所有列包括列级字符集。但它也会重写整张表所以大表还是那句老话评估好窗口期和工具。2.3 连接层字符集容易漏掉的一环存储层全改完了如果应用连接串没跟上依然会失败。很多项目改了表结构、没改连接参数结果本地测试通过、线上报错就是因为连接层的字符集还停在旧值。MySQL连接建立时客户端和服务器之间会协商字符集。如果应用没显式指定就用配置默认值。显式指定的方式分几种命令行客户端连接时加参数mysql -h host -P 3306 -u user -p --default-character-setutf8mb4服务端配置文件里统一指定编辑/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf具体路径看发行版[client] default-character-setutf8mb4 [mysql] default-character-setutf8mb4 [mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci这里有个细节[client]和[mysql]是两个不同的配置段[client]会影响所有客户端程序[mysql]只影响mysql命令行工具。JDBC、PHP、Python这些不走这些配置段它们看的是连接串参数。改完配置要重启MySQL服务然后重新登录执行SHOW VARIABLES LIKE character_set%;确认所有值都变成了utf8mb4。我碰到过改了配置文件忘记重启在那排查了半天的情况这种低级失误最容易让人心态炸裂。3. 应用层与索引配套改造存储层和连接层都改好之后应用层还有几个躲不开的改动点。别以为这事儿只是DBA的事写业务代码的同学同样要参与否则半路上还是会翻车。3.1 各语言连接串参数怎么配不同语言的数据库驱动设置字符集的方式不太一样但核心目标一致让客户端和服务器端都用utf8mb4通信。Java的JDBC连接串要显式加上characterEncodingutf8注意不是utf8mb4这里容易搞混。JDBC规范里characterEncodingutf8对应MySQL的utf8mb4驱动会自动映射。如果写成utf8mb4某些版本的驱动反而会报错jdbc:mysql://localhost:3306/test_emoji?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/ShanghaiPHP的PDO连接在DSN里加charset$pdo new PDO( mysql:hostlocalhost;dbnametest_emoji;charsetutf8mb4, $user, $pass );Python的PyMySQL或MySQLdb在connect时指定charsetimport pymysql conn pymysql.connect( hostlocalhost, useruser, passwordpass, databasetest_emoji, charsetutf8mb4 )Go语言的go-sql-driver/mysqlDSN里带charset参数db, err : sql.Open(mysql, user:passtcp(localhost:3306)/test_emoji?charsetutf8mb4parseTimeTruelocLocal)如果你的应用是C写的或者用的ODBC那就要看具体驱动文档。总体原则是连接层字符集必须和存储层一致如果你发现程序里写入的Emoji变成了???或者空字符串大概率是连接层的charset没有设置成utf8mb4。3.2 索引长度、排序规则与查询性能排序规则的选择直接影响查询结果。常见的几个选项utf8mb4_general_ci老的通用排序规则比较速度快但对Unicode字符的排序准确性一般utf8mb4_unicode_ci基于Unicode Collation Algorithm排序更准确早期版本性能稍逊于general_ci现在差距很小utf8mb4_0900_ai_ciMySQL 8.0新增基于Unicode 9.0标准支持音调符号和大小写不敏感匹配是8.0的默认选择选哪个没有绝对答案但有一个原则同库同实例内表与表之间的排序规则最好一致否则关联查询时会出现Illegal mix of collations的错误。这个错误常见于联表JOIN或UNION排查方法是在SQL里显式指定排序规则SELECT * FROM table_a JOIN table_b ON table_a.name table_b.name COLLATE utf8mb4_unicode_ci;再说索引长度。前面提到varchar(255)改utf8mb4后单列索引上限一般没问题但遇到下面几种情况要特别小心varchar字段用在唯一索引里而且长度超过190时5.7的默认限制下可能报1022错误键长度太长需要缩字段长度或改用前缀索引多个varchar字段组合成联合索引总字节数逼近3072TEXT字段建立索引时必须指定前缀长度举个实际例子某张表的唯一索引是(openid, nickname)openid是varchar(64)nickname是varchar(128)。改成utf8mb4后索引长度是(64128)*4768字节理论上还在5.7的3072字节限制内在旧版5.6上就可能超了767字节直接报错。碰到这种情况要么升级MySQL要么把nickname改成varchar(96)以下要么给nickname改前缀索引。这些细节要提前想好。另外字段升级到utf8mb4后字符在排序时可能和原来的结果不同。比如某些特殊符号在utf8mb4_unicode_ci下和utf8_general_ci下的排序权重不一样。如果你的业务里有关键依赖排序结果的功能——比如按昵称排序打比赛排名——迁移后要复核一遍排序结果是否符合预期。3.3 备份恢复与数据完整性校验迁移前备份和迁移后校验这两步不能省。备份时有个坑老版本的工具默认字符集可能不带utf8mb4导出的时候会把Emoji转义成十六进制或者直接丢失。我建议在mysqldump命令里显式指定字符集mysqldump -h host -u user -p --default-character-setutf8mb4 --single-transaction --quick test_emoji backup.sql恢复时也一样mysql -h host -u user -p --default-character-setutf8mb4 test_emoji backup.sql如果恢复后回显了一堆warning多半是字符集不一致引起的截断或替换。用SHOW WARNINGS直接查看具体告警内容。迁移完成后可以做一次数据完整性抽查。比如找几条原本包含Emoji的记录用HEX(column)去验证存储的字节序列是否正确。或者用下面的SQL反查脏数据-- 找出内容里包含4字节UTF-8字符的行 SELECT id, content FROM test_emoji WHERE CHAR_LENGTH(content) ! LENGTH(content);这个判断的原理是utf8mb4中中文字符在CHAR_LENGTH和LENGTH里分别按“字符数”和“字节数”计数普通中英文和数字是2到3字节Emoji是4字节。如果出现多字节字符LENGTH会比CHAR_LENGTH大两者差值越大说明多字节字符越多。这个方法很简单适合大面积扫描哪些记录包含Emoji。4. 常见问题与排查技巧实录写代码的都知道光给一个标准流程不够真正干活的时候遇到的全是反常规的幺蛾子。我自己实操中踩过不少坑挑几个高频的写出来供参考。4.1 典型报错对照速查表先整理一份速查表遇到问题直接对号入座报错信息常见原因解决思路Incorrect string value: \xF0\x9F\x98\x80存储层或连接层字符集不是utf8mb4逐层检查数据库、表、列、连接串的字符集Illegal mix of collationsJOIN或UNION两边的排序规则不一致在SQL中显式指定COLLATE或统一两表排序规则Specified key was too long索引字段字节数超过上限缩短字段长度、改用前缀索引或升级MySQL版本Data truncation: Data too long字段长度不够存储4字节字符确认字段字符集是utf8mb4且存储长度足够写入后显示为???或口口连接层字符集不对写入前被转换或丢失检查连接串charset参数重启应用命令行能插入程序里不能命令行写入了默认utf8mb4程序连接串没改检查应用侧连接参数确保和MySQL一致4.2 一个典型业务场景的排查过程之前线上有个用户说昵称改成“程序员”后一直都失败网页端提示“昵称格式不支持”。第一反应是前端做了输入校验把这串Emoji拦截了。让前端同学查了一圈没发现拦截逻辑。后端点开接口看日志MySQL报的错就是Incorrect string value: \xF0\x9F\x98\x8E。当时建表SQL里确实写的是DEFAULT CHARSETutf8所以第一步把表和列都改成utf8mb4然后直接跑用例——居然还报错。再查连接层发现应用里配置的是charsetutf8。这就很典型了存储层改了、连接层没跟上。把连接串改成utf8mb4后一次通过。这个案例我想说的核心观点是排查问题要按“客户端配置→连接串参数→MySQL连接变量→表结构列结构→实际存储数据”这条链路挨个查缺一个环节都可能有奇怪表现。不要只盯着Table的字符集看连接层的转换发生在写入前一旦连接层把字符转换成了非UTF-8编码底层存储再宽也没用。4.3 老系统迁移的平滑过渡建议老系统迁移最怕的是“跑了一半发现某张表有问题仓促回滚”。我做过一次全量迁移把几个核心库全部从utf8切到utf8mb4总结了几条可复用的经验第一分批迁移。按业务线把表分组先把不重要的配置表、日志表改了验证线上无异常后再动核心业务表。不要一个晚上把几百张表一起CONVERT出了问题不好定位是哪张表导致的。第二先改连接再改存储。个人经验是先让应用层连接串切到utf8mb4确认写入读取正常再改数据库表结构。理由是如果连接层还是utf8存储层改成utf8mb4并不会引发写入失败只是存不进Emoji反过来如果连接层先改成utf8mb4、存储层还是utf8写入Emoji时大概率直接报错反而暴露问题更快。考虑到风险还是推荐先改存储层后改连接层配合灰度发布。第三预留回滚方案。迁移前记录所有表的字符集情况出问题要能一键恢复。最简单的做法是把原有SHOW CREATE TABLE结果全部保存成文件万一要回滚按原DDL重建即可。第四数据库代理或中间件也会影响字符集。如果你的服务通过中间件连接MySQL中间件的字符集参数也要检查。这类组件的连接池往往会缓存旧的字符集设置改动后需要重启或刷新连接池才能生效。这个点特别容易漏。第五版本兼容性。MySQL 5.5早期版本虽然支持utf8mb4但连接层驱动或老版本客户端可能不支持表现就是客户端显示乱码但库里数据是对的。查证方式很简单用MySQL官方命令行客户端8.0版本去查那条数据如果显示正常说明数据库没问题问题在应用侧。5. 写在最后的实操心得这通篇讲下来核心就是一句MySQL里叫utf8的字符集不是完整的UTF-8要存Emoji就必须用utf8mb4而且全链路都得改齐。存储层、连接层、应用层三层都确认无误才能算真正落地。我自己现在建任何新库新表默认就是utf8mb4加utf8mb4_unicode_ci或utf8mb4_0900_ai_ci不管业务当下用不用Emoji。不为别的就为了省掉未来某天产品经理突然说要支持Emoji、然后你被迫改库的那顿折腾。字符集这东西一开始选宽了后面永远不亏。最后分享一个检查小技巧迁移后在应用里跑一遍核心写入场景——注册、发评论、编辑昵称——然后在数据库里用SELECT ... HEX(字段)抽查结果。比如写入一个“”之后查出来十六进制应该是F09F9880如果对不上说明链路里还有字符集转换在作怪从这一步去倒推是哪个环节被“拦截”了。这个方法花费不了几分钟但能帮你确认整条链路真的通了。
1. 为什么你的MySQL存不了Emoji从编码原理说起先别急着在代码里过滤Emoji或者把它们替换成“”这种占位符。问题根本不在前端而在你数据库的字符集上。我做过的几个项目里凡是涉及用户昵称、评论内容、签名这类自由文本字段的几乎都遇到过“明明线上跑得好好的突然某天插入一条带表情的数据就报错了”的情况。报错信息通常长这样Incorrect string value: \xF0\x9F\x98\x80 for column nickname at row 1当时第一反应是数据有问题查了半天才发现不是数据脏是MySQL的utf8根本不是你以为的那个UTF-8。如果你也被这个坑绊过或者还没遇到过但想提前避开这篇文章应该能帮你把整条链路理清楚从字符集原理到实际迁移命令再到连接层和应用层的配套改动照着做就能把Emoji稳稳存进去。1.1 先复现一下最常见的报错现场我常用的复现方式特别简单建一张测试表默认字符集用utf8然后插入一条包含笑脸表情的记录CREATE TABLE test_emoji ( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(255) ) DEFAULT CHARSETutf8; INSERT INTO test_emoji (content) VALUES (这是一条测试);在MySQL 5.7或8.0的环境下只要连接字符集也是utf8大概率直接给你抛Incorrect string value错误。如果连接层用的还是latin1那更惨连中文都存不进去。这个报错里那串\xF0\x9F\x98\x80就是UTF-8编码下Emoji“”的十六进制表示一共四个字节。问题就出在这四个字节上。MySQL里那个叫utf8的字符集实际最多只支持三个字节。一个Emoji占四个字节超出了它的表达能力自然就写不进去。更准确地说MySQL的utf8其实是utf8mb3的别名只是叫了很多年“utf8”让大家误以为它就是完整的UTF-8实现。1.2 三字节和四字节的矛盾utf8 vs utf8mb4这里得把概念稍微掰开揉碎讲一下。Unicode是一个字符集它给每个字符分配了一个唯一的编号也就是“码点”比如“”的码点是U1F600。而UTF-8是一种编码方式负责把这个码点转换成字节序列方便存储和传输。UTF-8是变长编码常见的英文字符占1个字节大部分拉丁文、希腊文、中文等占2到3个字节而Emoji、部分生僻汉字、一些特殊符号则占4个字节。换句话说完整的UTF-8编码必须能处理4字节的序列而MySQL的utf8mb3天生只能处理1到3字节等于自废一条腿。utf8mb4就是来解决这个问题的其中的“mb4”就是“maximum bytes 4”的意思它才是真正完整的UTF-8。从MySQL 5.5.3开始引入之后一直作为推荐字符集存在。到了MySQL 8.0utf8mb4已经成了默认字符集但很多从5.7甚至5.6时代迁移过来的老库依然停留在老配置上。还有个细节容易被忽略字符集和排序规则是两回事。字符集决定能存哪些字符排序规则决定字符串怎么比较和排序。比如utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_0900_ai_ci这些都是排序规则选哪个会影响查询时的等值匹配和排序行为后面我会单独说怎么选。1.3 怎么确认当前字符集配置有问题动手改之前先查一下当前到底哪个环节的字符集不对。我一般用几条SQL直接看现状-- 查看当前连接的字符集和排序规则 SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;重点看这几个值character_set_client客户端发送语句时使用的字符集character_set_connectionMySQL收到语句后转换使用的字符集character_set_results结果集返回给客户端时使用的字符集character_set_database当前默认数据库的字符集character_set_serverMySQL实例级的默认字符集再看表和列的字符集SHOW CREATE TABLE test_emoji;如果显示DEFAULT CHARSETutf8而你的业务里确实会出现Emoji基本就可以判断需要迁移了。还有一种情况是数据库是utf8mb4但某个列还是utf8或者连接层指定了utf8这些都需要逐一排查千万别只看一处就下结论。2. 核心方案utf8mb4全面迁移实操把方案说清楚全链路都改成utf8mb4。包括存储层——数据库、表、列以及连接层——客户端连接配置和连接串参数。只要有一层漏掉都会出现写入失败或者乱码。下面按顺序来。2.1 迁移前的检查清单动手前先过一遍清单能省掉后面很多麻烦确认MySQL版本不低于5.5.3推荐5.7以上8.0最好确认所有应用连接串里的字符集参数是utf8mb4不光是MySQL命令行确认索引字段改了utf8mb4后不会超过索引长度上限特别是varchar字段确认排序规则统一避免表之间关联查询时出现字符集不一致的报错确认备份可用大表迁移前先在测试环境刷一遍这里重点说一下索引长度问题这是很多人改完字符集之后才发现的隐患。utf8mb4一个字符最多4字节而InnoDB在MySQL 5.6之前默认索引键长度上限是767字节之后有了innodb_large_prefix5.7.7开始默认开启8.0直接移除了该参数索引键长度上限扩展到3072字节。举个例子一个varchar(255)的字段如果原来用utf8最多占765字节没超上限。改成utf8mb4后最多占1020字节依然没超3072字节一般OK。但如果用varchar(255)建了多列联合索引三列就是3060字节逼近3072上限一不小心就报Specified key was too long。这个在后面“应用层与索引配套改造”里详细说。2.2 一步步完成数据库、表、列级字符集修改最直接的场景是把一个老库整体迁到utf8mb4。假设库名是test_emoji修改数据库级字符集ALTER DATABASE test_emoji CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意这行命令只会改数据库的默认字符集已经存在的表不会跟着变。所以还要对每一张表执行。批量生成ALTER语句的通用写法是这样SELECT CONCAT(ALTER TABLE , TABLE_NAME, DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;) FROM information_schema.TABLES WHERE TABLE_SCHEMA test_emoji;把查询结果复制出来执行就能把每张表的默认字符集改掉。但这还不够表里已有的列不一定继承了表的新字符集尤其那些建表时单独指定过字符集的列需要单独处理。修改列级字符集我常用的生成语句SELECT CONCAT(ALTER TABLE , TABLE_NAME, MODIFY , COLUMN_NAME, , COLUMN_TYPE, CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, IF(IS_NULLABLE NO, NOT NULL, ), IF(COLUMN_DEFAULT IS NOT NULL, CONCAT( DEFAULT \, COLUMN_DEFAULT, \), ), ;) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA test_emoji AND (CHARACTER_SET_NAME ! utf8mb4 OR COLLATION_NAME ! utf8mb4_unicode_ci);有两个容易踩的坑第一个MODIFY COLUMN会重建表在数据量很大的表上可能耗时很长建议低峰期执行或者用后续版本支持的新语法配合工具平滑变更。第二个上面拼接的语句没有处理字段上的自增、默认值、注释等属性生产环境不建议手写拼接SQL去改列推荐把表结构用工具导出成DDL手工调整后再应用或者用专门的在线DDL方案。对于一个数据量可控的表直接三条SQL就搞定ALTER TABLE test_emoji CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CONVERT TO会同时转换表里的所有列包括列级字符集。但它也会重写整张表所以大表还是那句老话评估好窗口期和工具。2.3 连接层字符集容易漏掉的一环存储层全改完了如果应用连接串没跟上依然会失败。很多项目改了表结构、没改连接参数结果本地测试通过、线上报错就是因为连接层的字符集还停在旧值。MySQL连接建立时客户端和服务器之间会协商字符集。如果应用没显式指定就用配置默认值。显式指定的方式分几种命令行客户端连接时加参数mysql -h host -P 3306 -u user -p --default-character-setutf8mb4服务端配置文件里统一指定编辑/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf具体路径看发行版[client] default-character-setutf8mb4 [mysql] default-character-setutf8mb4 [mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci这里有个细节[client]和[mysql]是两个不同的配置段[client]会影响所有客户端程序[mysql]只影响mysql命令行工具。JDBC、PHP、Python这些不走这些配置段它们看的是连接串参数。改完配置要重启MySQL服务然后重新登录执行SHOW VARIABLES LIKE character_set%;确认所有值都变成了utf8mb4。我碰到过改了配置文件忘记重启在那排查了半天的情况这种低级失误最容易让人心态炸裂。3. 应用层与索引配套改造存储层和连接层都改好之后应用层还有几个躲不开的改动点。别以为这事儿只是DBA的事写业务代码的同学同样要参与否则半路上还是会翻车。3.1 各语言连接串参数怎么配不同语言的数据库驱动设置字符集的方式不太一样但核心目标一致让客户端和服务器端都用utf8mb4通信。Java的JDBC连接串要显式加上characterEncodingutf8注意不是utf8mb4这里容易搞混。JDBC规范里characterEncodingutf8对应MySQL的utf8mb4驱动会自动映射。如果写成utf8mb4某些版本的驱动反而会报错jdbc:mysql://localhost:3306/test_emoji?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/ShanghaiPHP的PDO连接在DSN里加charset$pdo new PDO( mysql:hostlocalhost;dbnametest_emoji;charsetutf8mb4, $user, $pass );Python的PyMySQL或MySQLdb在connect时指定charsetimport pymysql conn pymysql.connect( hostlocalhost, useruser, passwordpass, databasetest_emoji, charsetutf8mb4 )Go语言的go-sql-driver/mysqlDSN里带charset参数db, err : sql.Open(mysql, user:passtcp(localhost:3306)/test_emoji?charsetutf8mb4parseTimeTruelocLocal)如果你的应用是C写的或者用的ODBC那就要看具体驱动文档。总体原则是连接层字符集必须和存储层一致如果你发现程序里写入的Emoji变成了???或者空字符串大概率是连接层的charset没有设置成utf8mb4。3.2 索引长度、排序规则与查询性能排序规则的选择直接影响查询结果。常见的几个选项utf8mb4_general_ci老的通用排序规则比较速度快但对Unicode字符的排序准确性一般utf8mb4_unicode_ci基于Unicode Collation Algorithm排序更准确早期版本性能稍逊于general_ci现在差距很小utf8mb4_0900_ai_ciMySQL 8.0新增基于Unicode 9.0标准支持音调符号和大小写不敏感匹配是8.0的默认选择选哪个没有绝对答案但有一个原则同库同实例内表与表之间的排序规则最好一致否则关联查询时会出现Illegal mix of collations的错误。这个错误常见于联表JOIN或UNION排查方法是在SQL里显式指定排序规则SELECT * FROM table_a JOIN table_b ON table_a.name table_b.name COLLATE utf8mb4_unicode_ci;再说索引长度。前面提到varchar(255)改utf8mb4后单列索引上限一般没问题但遇到下面几种情况要特别小心varchar字段用在唯一索引里而且长度超过190时5.7的默认限制下可能报1022错误键长度太长需要缩字段长度或改用前缀索引多个varchar字段组合成联合索引总字节数逼近3072TEXT字段建立索引时必须指定前缀长度举个实际例子某张表的唯一索引是(openid, nickname)openid是varchar(64)nickname是varchar(128)。改成utf8mb4后索引长度是(64128)*4768字节理论上还在5.7的3072字节限制内在旧版5.6上就可能超了767字节直接报错。碰到这种情况要么升级MySQL要么把nickname改成varchar(96)以下要么给nickname改前缀索引。这些细节要提前想好。另外字段升级到utf8mb4后字符在排序时可能和原来的结果不同。比如某些特殊符号在utf8mb4_unicode_ci下和utf8_general_ci下的排序权重不一样。如果你的业务里有关键依赖排序结果的功能——比如按昵称排序打比赛排名——迁移后要复核一遍排序结果是否符合预期。3.3 备份恢复与数据完整性校验迁移前备份和迁移后校验这两步不能省。备份时有个坑老版本的工具默认字符集可能不带utf8mb4导出的时候会把Emoji转义成十六进制或者直接丢失。我建议在mysqldump命令里显式指定字符集mysqldump -h host -u user -p --default-character-setutf8mb4 --single-transaction --quick test_emoji backup.sql恢复时也一样mysql -h host -u user -p --default-character-setutf8mb4 test_emoji backup.sql如果恢复后回显了一堆warning多半是字符集不一致引起的截断或替换。用SHOW WARNINGS直接查看具体告警内容。迁移完成后可以做一次数据完整性抽查。比如找几条原本包含Emoji的记录用HEX(column)去验证存储的字节序列是否正确。或者用下面的SQL反查脏数据-- 找出内容里包含4字节UTF-8字符的行 SELECT id, content FROM test_emoji WHERE CHAR_LENGTH(content) ! LENGTH(content);这个判断的原理是utf8mb4中中文字符在CHAR_LENGTH和LENGTH里分别按“字符数”和“字节数”计数普通中英文和数字是2到3字节Emoji是4字节。如果出现多字节字符LENGTH会比CHAR_LENGTH大两者差值越大说明多字节字符越多。这个方法很简单适合大面积扫描哪些记录包含Emoji。4. 常见问题与排查技巧实录写代码的都知道光给一个标准流程不够真正干活的时候遇到的全是反常规的幺蛾子。我自己实操中踩过不少坑挑几个高频的写出来供参考。4.1 典型报错对照速查表先整理一份速查表遇到问题直接对号入座报错信息常见原因解决思路Incorrect string value: \xF0\x9F\x98\x80存储层或连接层字符集不是utf8mb4逐层检查数据库、表、列、连接串的字符集Illegal mix of collationsJOIN或UNION两边的排序规则不一致在SQL中显式指定COLLATE或统一两表排序规则Specified key was too long索引字段字节数超过上限缩短字段长度、改用前缀索引或升级MySQL版本Data truncation: Data too long字段长度不够存储4字节字符确认字段字符集是utf8mb4且存储长度足够写入后显示为???或口口连接层字符集不对写入前被转换或丢失检查连接串charset参数重启应用命令行能插入程序里不能命令行写入了默认utf8mb4程序连接串没改检查应用侧连接参数确保和MySQL一致4.2 一个典型业务场景的排查过程之前线上有个用户说昵称改成“程序员”后一直都失败网页端提示“昵称格式不支持”。第一反应是前端做了输入校验把这串Emoji拦截了。让前端同学查了一圈没发现拦截逻辑。后端点开接口看日志MySQL报的错就是Incorrect string value: \xF0\x9F\x98\x8E。当时建表SQL里确实写的是DEFAULT CHARSETutf8所以第一步把表和列都改成utf8mb4然后直接跑用例——居然还报错。再查连接层发现应用里配置的是charsetutf8。这就很典型了存储层改了、连接层没跟上。把连接串改成utf8mb4后一次通过。这个案例我想说的核心观点是排查问题要按“客户端配置→连接串参数→MySQL连接变量→表结构列结构→实际存储数据”这条链路挨个查缺一个环节都可能有奇怪表现。不要只盯着Table的字符集看连接层的转换发生在写入前一旦连接层把字符转换成了非UTF-8编码底层存储再宽也没用。4.3 老系统迁移的平滑过渡建议老系统迁移最怕的是“跑了一半发现某张表有问题仓促回滚”。我做过一次全量迁移把几个核心库全部从utf8切到utf8mb4总结了几条可复用的经验第一分批迁移。按业务线把表分组先把不重要的配置表、日志表改了验证线上无异常后再动核心业务表。不要一个晚上把几百张表一起CONVERT出了问题不好定位是哪张表导致的。第二先改连接再改存储。个人经验是先让应用层连接串切到utf8mb4确认写入读取正常再改数据库表结构。理由是如果连接层还是utf8存储层改成utf8mb4并不会引发写入失败只是存不进Emoji反过来如果连接层先改成utf8mb4、存储层还是utf8写入Emoji时大概率直接报错反而暴露问题更快。考虑到风险还是推荐先改存储层后改连接层配合灰度发布。第三预留回滚方案。迁移前记录所有表的字符集情况出问题要能一键恢复。最简单的做法是把原有SHOW CREATE TABLE结果全部保存成文件万一要回滚按原DDL重建即可。第四数据库代理或中间件也会影响字符集。如果你的服务通过中间件连接MySQL中间件的字符集参数也要检查。这类组件的连接池往往会缓存旧的字符集设置改动后需要重启或刷新连接池才能生效。这个点特别容易漏。第五版本兼容性。MySQL 5.5早期版本虽然支持utf8mb4但连接层驱动或老版本客户端可能不支持表现就是客户端显示乱码但库里数据是对的。查证方式很简单用MySQL官方命令行客户端8.0版本去查那条数据如果显示正常说明数据库没问题问题在应用侧。5. 写在最后的实操心得这通篇讲下来核心就是一句MySQL里叫utf8的字符集不是完整的UTF-8要存Emoji就必须用utf8mb4而且全链路都得改齐。存储层、连接层、应用层三层都确认无误才能算真正落地。我自己现在建任何新库新表默认就是utf8mb4加utf8mb4_unicode_ci或utf8mb4_0900_ai_ci不管业务当下用不用Emoji。不为别的就为了省掉未来某天产品经理突然说要支持Emoji、然后你被迫改库的那顿折腾。字符集这东西一开始选宽了后面永远不亏。最后分享一个检查小技巧迁移后在应用里跑一遍核心写入场景——注册、发评论、编辑昵称——然后在数据库里用SELECT ... HEX(字段)抽查结果。比如写入一个“”之后查出来十六进制应该是F09F9880如果对不上说明链路里还有字符集转换在作怪从这一步去倒推是哪个环节被“拦截”了。这个方法花费不了几分钟但能帮你确认整条链路真的通了。
