★ FEATURED ARTICLE 搞数据库的人十有八九都被“乱码”两个字折腾过。好好的中文入库之后变成了“???”或者读出来一团乱码再或者emoji表情存进去直接报错索引建出来跑了半个多小时结果还超长。这些问题绕来绕去最后都会落到同一个根源上字符集和校对规则。这篇就把MySQL里的字符集character set和校对规则collation彻底讲透从概念、配置、实操到排查思路全部覆盖。无论是刚入门的新手还是被线上问题折磨的运维老手都能在这里找到可以直接抄走的方案。1. 先搞懂字符集和校对规则是什么1.1 字符集计算机如何存储“文字”一句话解释字符集就是一张“文字和数字编码”的对照表。计算机只认二进制不认字母和汉字所以每个字符都必须映射成一组数字这个映射规则就是字符集。拿“中”这个字举例在GBK里它对应的是两个字节在UTF-8里是三个字节在UTF-16里可能是两个字节——同样的字在不同字符集下二进制内容完全不一样。如果你写入时用的是GBK读取时却按UTF-8去解释出来的就是乱码。把字符集理解成“翻译字典”就行字典不一样同一个词翻译的结果就不同。MySQL里常见的字符集有这些字符集单字符最大字节数说明latin11字节只支持西欧语言历史遗留老项目常见gbk2字节中文场景常用但不支持emojiutf8 / utf8mb33字节标准UTF-8能存中文但存不了emojiutf8mb44字节完整UTF-8表情、生僻字都能存现在绝对首选这里的重点就是utf8和utf8mb4的字节差异。MySQL里的utf8实际上是不完整的UTF-8只有3个字节所以遇到需要4字节编码的字符比如emoji就没法存。utf8mb4才是完整的四字节UTF-8。这一点在后面的实操里会反复用到。1.2 校对规则排序和比较的底层逻辑字符集决定了字符怎么存校对规则则决定了字符怎么比。具体来说它规定了排序ORDER BY、比较WHERE 、分组GROUP BY和去重DISTINCT时两个字符串到底算不算相同以及谁排在前面。MySQL里同一个字符集可以搭配多个校对规则命名一般遵守一个套路字符集_区域_后缀。比如utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_bin。后缀部分最关键它们是含义的浓缩_ciCase Insensitive大小写不敏感。查询WHERE name abc能匹配到ABC。_csCase Sensitive大小写敏感。abc和ABC是两个值。_binBinary按二进制字节直接比较。它是严格的大小写敏感而且连重音符号都区分。_aiAccent Insensitive重音不敏感主要影响拉丁语系的字符。有的规则里还带0900这种数字这表示排序算法基于Unicode 9.0标准一般只在MySQL 8.0里出现。举个例子utf8mb4_unicode_ci和utf8mb4_general_ci都表示大小写不敏感但内部算法不同。unicode_ci严格遵循Unicode排序标准多语言支持更规范general_ci是MySQL早期自己搞的一套简化逻辑性能略好但排序准确性差一点。MySQL 8.0里默认的utf8mb4_0900_ai_ci则是在性能和准确性上做了更好平衡的版本。1.3 为什么两个概念必须成对出现存储靠字符集比较靠校对规则但这两者在建表时经常被同时指定很多人容易搞混。打个比方字符集像一本“字典”记录了字和编码的对应关系校对规则像“查字典的顺序规则”比如是按笔画排还是按拼音排。字典不同同一个字的读音就不同顺序规则不同同样的字排出来顺序也不同。MySQL里每个字符集都有一个默认的校对规则。比如utf8mb4的默认规则在MySQL 5.7是utf8mb4_general_ci在MySQL 8.0则是utf8mb4_0900_ai_ci。如果不显式指定系统就会套用默认规则。这本身不是问题但只要同一个数据库里存在使用不同字符集或不同校对规则的字段那么JOIN、UNION、比较等操作就可能报Illegal mix of collations错误。这个坑后面会专门讲。2. 查看与设置字符集这些命令必须掌握2.1 一条命令查看系统所有字符集变量在MySQL命令行里输入SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;执行之后会看到一堆变量很多新手容易看晕这里把最核心的几个解释清楚系统变量作用character_set_server服务器默认字符集建库时如果不指定就用它character_set_database当前数据库默认字符集character_set_client客户端发送SQL语句时使用的字符集character_set_connectionMySQL收到SQL后转换用的字符集character_set_results查询结果返回给客户端时使用的字符集这里面的逻辑是客户端发来一串以character_set_client编码的SQLMySQL先把它转成character_set_connection再执行最后把结果按character_set_results返回给客户端。这三层任何一个不一致乱码就会出现。顺便还可以看下具体库、表、列使用的是哪套SHOW CREATE DATABASE db_name; SHOW CREATE TABLE table_name;这两条命令会输出建表语句里面直接写着DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci之类的信息是判断问题的最快途径。2.2 设置字符集的三个层级和正确姿势字符集设置分为服务器级、数据库级、表级、列级优先级从低到高列级最高。这意味着表设置可以覆盖库设置列设置可以覆盖表设置。所以如果发现某个字段乱码而同一张表其他字段正常多半就是字段级别被单独指定了其他字符集。常用的设置方式有三种配置文件全局设置。在MySQL配置文件的[mysqld]段里加上[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci注意MySQL 8.0默认已经是utf8mb4不用改也可以但5.7及以下版本默认经常是latin1必须显式指定。改完配置文件要重启MySQL服务才生效。建库建表时显式指定CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; CREATE TABLE mytable ( name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ) DEFAULT CHARSETutf8mb4;见名知义这时库和表都已经明确了规范。养成这个习惯比依赖全局默认值安全得多。会话级临时设置SET NAMES utf8mb4;SET NAMES utf8mb4其实是一条组合命令等价于把character_set_client、character_set_connection、character_set_results三个变量同时设为utf8mb4。它不改变服务器和库表本身的字符集只影响当前会话的传输过程。这条命令在排查乱码时非常常用。2.3 utf8 和 utf8mb4 到底差在哪里什么时候非用不可前面提到utf8在MySQL里最多用3个字节一个emoji用4个字节编码所以碰到表情就会存不进去。更准确地说MySQL 8.0里已经把utf8改名为utf8mb3并建议用utf8mb4彻底替代。什么时候必须用utf8mb4业务上需要存emoji表情。需要存生僻汉字比如一些粤语字、古汉字部分字符超出3字节范围。业务是移动端App用户输入法里任何字符都可能出现存文本时要给自己留足空间。从老库往新库迁移统一字符集的场景。说实话现在的环境下新建项目已经没有任何理由再用utf8了直接utf8mb4一了百了。至于存储空间虽然utf8mb4单个字符最多4字节但它是变长编码纯ASCII字符仍然只占1字节中文仍然是3字节只有必须用4字节编码的表情和生僻字才会多占用绝大多数场景下成本增加非常有限。3. 从建库到查询字符集配置的完整落地流程3.1 建库建表时应该如何显式指定字符集强烈建议所有建表语句都写成这样不依赖全局默认值CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE app_db; CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, nickname VARCHAR(50) NOT NULL, bio VARCHAR(200) NULL, PRIMARY KEY (id), KEY idx_nickname (nickname) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这里每次都要重复写字符集和校对规则虽然是冗余但换来的是“这张表不管落到哪个环境都不会因为默认值不同而出问题”的确定性。如果已经建好的表需要改字符集有两种改法一定要分清第一种只想改表的默认字符集不处理已有数据ALTER TABLE users DEFAULT CHARACTER SET utf8mb4;第二种要连同已有数据一并转换ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;第二条命令会重写整张表的数据。表大时会长时间锁表直接影响线上读写。我踩过的坑就是在一个千万级大表上直接执行CONVERT TO跑了一个多小时还把从库延迟拉到了半小时后来只能临时切流才救回来。正确做法是评估表大小预估锁表时间用在线工具或者在维护窗口操作同时先做好备份。3.2 连接层字符集乱码最容易被忽视的环节存储层字符集对了乱码还可能出现在连接层。这个环节非常隐蔽。拿最常见的编程场景举例。Java的JDBC连接串jdbc:mysql://localhost:3306/app_db?useUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/ShanghaicharacterEncodingUTF-8这一项就会在建立连接时自动执行类似SET NAMES utf8mb4的操作。如果连接串里没有指定编码且数据库服务器端默认字符集不是utf8mb4那么客户端和MySQL之间就可能因为编码不一致而出现乱码。Python的SQLAlchemy或MySQLdb连接里一般这样指定engine create_engine( mysqlpymysql://root:passwordlocalhost/app_db?charsetutf8mb4 )这里的charset参数同样对应了连接层的字符集。另一个容易踩的点是PHP老项目里的latin1。如果连接层和库表都是latin1中文虽然能用但是全凭侥幸一旦需要兼容新功能存表情改起来更麻烦。我的习惯是一开始就在连接层统一使用utf8mb4并且顺手验证一遍执行SHOW VARIABLES LIKE character_set%确认character_set_client、character_set_connection、character_set_results三个值都是utf8mb4再继续下一步业务开发。3.3 数据导入导出时的字符集处理用mysqldump做备份或迁移时不指定字符集很容易拿到一份乱码的SQL文件。导出时指定mysqldump -u root -p --default-character-setutf8mb4 --single-transaction app_db backup.sql--default-character-setutf8mb4决定了导出文件里的字符集标注--single-transaction用于InnoDB表保证一致性快照避免锁表。导入时指定mysql -u root -p --default-character-setutf8mb4 app_db backup.sql如果发现导入之后个别字段乱码先不要急着改数据用SHOW CREATE TABLE看下这张表的字符集是不是utf8mb4再用SET NAMES utf8mb4重试一次导入。大多数情况都是连接层字符集不匹配导致的真正需要改动数据的反而少。4. 排序规则如何影响业务的排序和比较4.1 大小写敏感与不敏感不同业务要选不同规则_ci系列大小写不敏感_cs系列大小写敏感_bin系列用二进制比较。选择直接影响业务逻辑。用户登录场景要特别注意。如果一个用户注册了昵称Tom另一个用户尝试注册tom在utf8mb4_unicode_ci下这两个会被认为是同一个值唯一索引会直接拦住后者的注册。如果业务需求是必须要区分大小写那就得用utf8mb4_bin或utf8mb4_0900_as_cs这类规则。反过来验证码、邮箱这类场景一般又希望大小写不敏感。邮箱写USERexample.com和userexample.com如果被当成两个账号就反人类了这时_ci反而更合适。所以不存在“哪个校对规则更好”只有“哪个更适合这个业务场景”。我的建议是默认全库用utf8mb4_unicode_ci或utf8mb4_0900_ai_ci遇到确实需要区分大小写的字段再单独在列级别改成utf8mb4_bin。4.2 多语言排序和中文排序的问题utf8mb4_unicode_ci和utf8mb4_0900_ai_ci对英文、数字、法语、德语等拉丁语系的排序基本符合人们的直觉。但中文排序是个老大难。MySQL默认的校对规则对中文是按照Unicode编码排序的而不是按拼音或笔画。举个例子SELECT name FROM users ORDER BY name;结果往往不符合中文用户的拼音预期。张三、李四、王五三个名字按哈希一样的顺序乱排你无法控制它走“zhang、li、wang”的拼音顺序。解法常见有三种在MySQL里用CONVERT(name USING gbk)转成GBK再排序因为GBK的编码顺序和拼音顺序基本一致SELECT name FROM users ORDER BY CONVERT(name USING gbk);这个方法在小表上很方便但会让索引失效大表排序性能很差。业务层单独维护一个拼音字段排序时用这个字段做ORDER BY这也是很多电商系统处理商品名排序的通用做法。在数据库外比如应用内先查到数据再用拼音库排序。只适合数据量可控的场景。4.3 校对规则和索引长度限制的关系这里有个实打实会踩的坑字符集和索引长度有直接关联。MySQL的索引在旧版本里单列最大767字节在开启innodb_large_prefix并启用DYNAMIC行格式后是3072字节MySQL 5.7.7以后默认开启。一个VARCHAR(255)字段如果字符集是latin1索引最大长度是255字节没问题如果是utf8mb4一个字符最多4字节最大索引长度就是255 * 4 1020字节如果版本和行格式不支持3072字节就会超过767字节限制导致建索引报错。所以建表时看到某个字段要加索引不要无脑VARCHAR(255)。要么把长度改成合理值要么用前缀索引ALTER TABLE users ADD KEY idx_nickname (nickname(50));nickname(50)表示取前50个字符建索引50乘4等于200字节远小于限制。代价是没法用这个索引做完整排序但大部分场景等价查询都够用。5. 高频问题排查与避坑实录5.1 乱码问题排查顺序乱码问题基本逃不出这三层传输层、存储层、展示层。先看传输层SHOW VARIABLES LIKE character_set%;确认client、connection、results三个值最好都是utf8mb4。这一步可以在终端里手动执行SET NAMES utf8mb4试一下如果问题消失就是连接层配置没到位。再看存储层SHOW CREATE TABLE your_table;重点看DEFAULT CHARSET和具体字段的CHARACTER SET。如果表是latin1但应用层按utf8写入那不乱码才怪。最后看展示层。很多时候数据存进库是好的但PHP页面或命令终端本身没声明UTF-8编码导致读取时被浏览器用错误编码渲染。这个不在MySQL层面但排查时最容易浪费时间建议先排除。一个快速定位技巧在MySQL命令行里直接执行SELECT name, HEX(name) FROM users看十六进制值。正常UTF-8中文会以E开头如E4B8AD如果看到一堆3F或者C4E3这种规律基本可以确定是存储层用了错误的编码。5.2 emoji 存不进去或者全变成问号这个问题的原因相当集中。存储层不是utf8mb4是最大可能。正确修复路径ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;然后修改连接层在应用配置里把字符集设为utf8mb4重启连接再试。如果还不行检查一下连接串是否被中间件或代理改变了。另一个隐藏点如果用的是老版本驱动比如比较旧的JDBC驱动不支持utf8mb4即使库表和连接都对emoji也可能被截断。升级驱动版本就能解决。5.3 Illegal mix of collations 报错执行JOIN或UNION时报Illegal mix of collations一般是两个字段的校对规则不一致。比如A表字段用utf8mb4_unicode_ciB表字段用utf8mb4_bin直接A.id B.id做关联就可能报错。解决办法有这几种把两张表的字段改成同一个校对规则最彻底。在SQL里显式指定SELECT * FROM a JOIN b ON a.name b.name COLLATE utf8mb4_unicode_ciCOLLATE在表达式里临时指定校对规则不改变表结构适合快速止血。用CONVERT转字符集SELECT * FROM a JOIN b ON a.name CONVERT(b.name USING utf8mb4)这个适合字符集都不一样的情况。从我处理过的案例看绝大多数都是因为建表时没注意统一规范有人用了utf8mb4_general_ci有人用了utf8mb4_unicode_ci结果联表查询就炸了。最好的方式还是从建表阶段强制统一。5.4 排序走索引失效或者数据查不到一个容易被忽略的点排序规则的改变会让原有索引的排序顺序“失效”。比如字段原本是utf8mb4_bin排序走索引没问题改成utf8mb4_unicode_ci后如果查询里用到了ORDER BYMySQL优化器可能认为索引不符合新的排序要求转而走文件排序。排查时用EXPLAIN看执行计划里的Using filesort标志就能发现。所以修改已有表的字符集和排序规则时要连索引一起梳理。如果改完发现排序变慢考虑重建索引并检查是否因为前缀索引导致无法覆盖排序场景。6. 实战中沉淀的几条经验这篇博客写了大量命令和排查流程最后聊几条不太好直接写进文档的经验都是这些年踩坑换来的。第一建表语句里一定要把CHARSET和COLLATE写死。不要想着“反正服务器默认字符集已经配好了”。今天换机器、明天换DBA、后天换云服务商任何一步都会改掉环境默认值只有建表语句里写死的规则能一路带到天荒地老。第二新项目一律utf8mb4。别觉得“我们业务没有表情”。用户不按你的想法输入今天不出emoji明天出一个生僻字到时候再改全库字符集那才叫痛苦。改库要锁表锁表就要维护窗口维护窗口就要出通告这一套组合拳下来成本远高于当初直接用utf8mb4。第三修改大表字符集前务必先看索引长度。一个满库VARCHAR(255)的表转成utf8mb4之后极可能触发索引超限导致DDL失败或者需要重建全部索引。最好提前用脚本扫一遍所有待改字段的长度再决定是改字段长度还是加前缀索引。第四团队协作项目最好在开发规范里规定统一校对规则并写入评审检查项。我见过一个项目库里有三种不同的_ci规则表面上都能跑一到联表查询就出现各种诡异错误排查起来极其耗费心力。第五遇到乱码问题先动脑再动手。按照“传输层、存储层、展示层”的顺序逐层排查别一上来就ALTER TABLE CONVERT。很多时候一句SET NAMES utf8mb4就能解决何苦把整张表重写一遍。最后补一句字符集和校对规则属于那种“不出事没人看出了事头就大”的知识点。花半天时间把这里面的逻辑彻底理顺比将来花几天时间去擦屁股要划算得多。
阅读完成 · 觉得有帮助？
