1. 字符集到底是什么东西1.1 从乱码开始认识的字符集如果你干过一段时间数据库那你一定遇到过这种场景程序里明明存的是中文查出来却变成了一堆问号或者各种奇怪的符号。比如你在命令行里执行INSERT INTO user (name) VALUES (张三);然后SELECT出来变成了å¼ ä¸又或者干脆全是???。这个时候绝大多数人第一反应是“数据库坏了”但实际上极大概率是字符集没配对。我当年第一次踩这个坑的时候也完全摸不着头脑程序里输出正常数据库里也正常就是通过客户端一查就乱。后来才知道问题出在“字符集”这三个字上。那字符集到底是个什么东西用大白话说字符集就是一张“翻译对照表”它规定了“某个字符用什么样的二进制数字来代表”。计算机本身只认0和1它不认识什么“张”“三”“A”“B”我们之所以能看到这些字是因为有一套规则把字形映射到了二进制数字。这套映射规则就是字符集。而“编码”这个概念其实就是字符集的具体实现方式比如把“张”字映射到数字0x5F20这就是一种编码规则。1.2 认识几个常用的字符集在正式开始操作MySQL之前我觉得有必要先把几个常见的字符集名字搞清楚否则后面配置的时候会一头雾水。ASCII最古老的字符集之一只包含英文字母、数字、标点符号和一些控制字符一共128个字符用1个字节表示。基本属于上古产物现在的业务系统已经很少直接用了。GBK / GB2312 / GB18030这是中国制定的汉字编码标准主要用来处理简体中文。GB2312是早期版本能表示6763个常用汉字GBK在它基础上扩展了繁体字和更多生僻字GB18030则是更完整的版本。这类字符集的特点是“中文占2个字节英文占1个字节”在纯中文场景下确实省空间但一旦碰到国际化业务各种编码混在一起就是灾难。UTF-8目前最通用的编码方式号称能把全世界所有语言的字符都装进去。它采用变长编码英文占1个字节中文通常占3个字节。缺点就是中文数据比GBK多占一点空间但在今天这个存储不贵的时代这点空间完全值得。UTF8MB4这是UTF-8的超集真正的“完全体”。MySQL里那个utf8其实是utf8mb3只能存基本的多语言字符像emoji表情这种四字节字符是存不进去的。而utf8mb4专门用来解决这个问题它的名字里“mb4”就是“maximum bytes 4”的意思。现在做新项目如果选MySQL我个人强烈建议直接上utf8mb4省得以后折腾。1.3 字符集和MySQL的关系MySQL本身是一个“存储箱子”它本身不关心你存的是中文还是英文它只负责把数据原样放进文件里。但你往这个箱子里放数据的时候箱子会问你一个问题“我该用什么规则来理解你给我的这一串字节”如果你告诉它用A规则而你的程序是用B规则生成这串字节的那箱子就会用A规则去解读B规则产生的数据最终解读出来的就是一堆乱码。更让人头疼的是MySQL里字符集不止一层。数据库有数据库的字符集表有表的字符集字段有字段的字符集连接有连接的字符集结果集有结果集的字符集。只要其中任何一个环节配错最后到你屏幕上显示的文字就可能不对。我后面会详细拆这层关系这里先记住一句话字符集是MySQL里最容易被忽视、但影响面极大的基础配置。2. MySQL字符集的层级体系2.1 服务器层、数据库层、表层的字符集MySQL的字符集配置就像一套“套娃”系统上级有默认配置下级可以覆盖上级。先说这层套娃的完整链路。服务器层安装MySQL的时候会在配置文件里写一个默认字符集这就是整个实例的“底子”。如果你建数据库的时候不指定字符集就沿用服务器层的设置。服务器层对应的参数名是character_set_server。数据库层CREATE DATABASE的时候可以单独指定字符集比如CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4;这层会把服务器层的默认值覆盖掉。数据库层的字符集存在这个库自己的元数据里即使你以后把服务器默认改掉已经建好的库还是用自己的配置。表层建表的时候也可以指定DEFAULT CHARSETutf8mb4这层会覆盖掉数据库层的默认配置。字段层建表的时候你可以给某一个VARCHAR列单独指定字符集比如name VARCHAR(50) CHARACTER SET utf8mb4。这一层的优先级是最高的它会覆盖掉表级配置。层层覆盖的关系用一句口诀来记就是字段覆盖表表覆盖库库覆盖服务器。知道这个层级关系特别重要因为很多新手排查乱码问题的时候只改了表结构却没发现服务器层和连接层的配置全都在捣乱。2.2 连接层的字符集链路如果说上面那套是“存储端”的字符集体系那下面这套就是“传输端”的字符集链路它管的是客户端和MySQL服务器之间通信时用的是哪套规则。一条SQL从客户端出发最终把结果带回来期间至少经过这几个环节character_set_client服务器认为“客户端发来的SQL语句是用什么字符集编码的”。character_set_connectionSQL语句进入服务器后服务器会把从客户端收到的SQL从character_set_client指定的字符集“翻译”成character_set_connection指定的字符集然后再去解析执行。character_set_results结果集要返回给客户端的时候服务器会把数据从内部存储的字符集“翻译”成character_set_results指定的字符集。character_set_database当前默认数据库的字符集。character_set_server服务器实例的默认字符集。新手没必要把这五个参数全背下来关键是理解一件事数据在服务器内部是以“表结构定义的字符集”存储的但在传输过程中服务器会做两次“翻译”。一次是接收时从client翻译成connection一次是发送结果时从存储字符集翻译成results。这两次翻译只要有一次用错了“词典”数据就会乱。2.3 执行过程和层级配置的前后关系你会不会好奇为什么MySQL要搞这么复杂的一套东西就不能统一成一个字符集吗早期MySQL确实是想省事几乎全用一套字符集。但后来发现现实世界太残酷了有的工具用GBK有的程序用UTF-8有的老系统还在用Latin1。如果服务器只认一种编码兼容性就会非常差。为了兼容各种客户端工具MySQL设计出了这种“客户端声明一个编码服务端翻译后用内部编码存储返回结果再按客户端的编码翻译回去”的机制。这个机制本身是合理的问题是它给使用者增加了认知负担。我见过不少项目表结构建得没问题程序里也用的UTF-8但就是乱码最后排查发现是某个开发者在命令行里执行了一句SET NAMES gbk;然后后续所有会话都乱了。这一句SET NAMES的本质是同时把character_set_client、character_set_connection、character_set_results三个参数一次性设置掉它能让排查变得简单也能让排查变得复杂全看你怎么用它。提示SET NAMES utf8mb4;这条命令在手动管理MySQL会话时非常实用它一次性把客户端、连接和结果集三个层的字符集都统一了是排查乱码问题时首选的第一步操作。3. 零基础上手查看和设置字符集3.1 查询当前环境的字符集状态在动手改东西之前我强烈建议你先用一条SQL把当前所有字符集相关的参数看清楚避免盲人摸象。这条SQL是SHOW VARIABLES LIKE character_set%;执行完你会看到类似这样的结果------------------------------------------------------------ | 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 | utf8mb4 | | character_sets_dir | /usr/share/mysql/charsets/ | ------------------------------------------------------------这些字段的含义分别对应我上面说过的那些层级。你把这条SQL记下来它就是你排查字符集问题的第一把钥匙。以后再遇到乱码第一时间别急着改表先看一眼当前会话的环境变量十有八九能找到线索。另外还有一个查询可以查看当前MySQL支持哪些字符集SHOW CHARACTER SET;这个命令会列出所有字符集的名字、默认排序规则、最大字节数等信息。你主要关注Maxlen这一列就行看到utf8那一行的Maxlen是3而utf8mb4是4就能理解为什么很多老项目存emoji会报错。3.2 建库、建表时指定字符集的完整写法这里直接给一套我常用的建库建表模板新手可以直接照着抄-- 建库时指定字符集和排序规则 CREATE DATABASE myapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 建表时指定字符集 CREATE TABLE user ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL COMMENT 用户名, intro TEXT COMMENT 个人简介 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这里解释一下COLLATE是什么。COLLATE叫“排序规则”它决定了这个字符集里字符要怎么比较大小、怎么排序。举个例子utf8mb4_unicode_ci里的ci是case insensitive的缩写意思是“大小写不敏感”。假设你有个字段存英文单词查询条件WHERE word abc能匹配到ABC和Abc。如果你希望大小写敏感就要用utf8mb4_bin或者utf8mb4_general_cs不过MySQL里很多字符集不支持_cs后缀的规则实际选项得看具体版本。排序规则对业务的影响非常隐蔽我见过一个案例某个表存的是中文姓名排序规则用的是utf8mb4_general_ci结果ORDER BY name的时候“张”字和“章”字的排序顺序和字典顺序不一致导致分页数据错位。这种问题改起代码非常痛苦所以建表之初就要想清楚排序规则别上来全用默认值。3.3 修改已有库表的字符集如果你的项目已经建好了现在想把整个库的字符集从utf8升级到utf8mb4该怎么做有一个误区是很多人直接在数据库上执行这条SQLALTER DATABASE myapp DEFAULT CHARACTER SET utf8mb4;执行完后你会惊讶地发现库的默认字符集确实改成了utf8mb4但表里的数据还是老编码查出来依然是乱码。原因很简单这条SQL只改了“默认值”它不会自动去改已经存在的数据表。数据表的字符集存的是表自己的元数据你要挨个表去改才能生效。正确的执行流程是这样-- 第一步修改表默认字符集 ALTER TABLE user DEFAULT CHARACTER SET utf8mb4; -- 第二步修改表内所有字段的字符集 ALTER TABLE user MODIFY name VARCHAR(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT , MODIFY intro TEXT CHARACTER SET utf8mb4;有人会问DEFAULT CHARACTER SET和直接MODIFY字段有什么区别区别很大。ALTER TABLE ... DEFAULT CHARACTER SET只改表的默认值对已经存在的列没有任何作用。也就是说你只是把“以后新加的字段”变成新字符集老字段纹丝不动。所以真正要改数据必须MODIFY具体字段或者直接CONVERT TO CHARACTER SET。MySQL还提供了一条一键转换的SQL能把表里的所有列一次性转过去ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这条命令相当于把表默认字符集、所有字段字符集、以及已有数据的存储编码全部改成新字符集执行完之后整张表都是utf8mb4了。说实话我建议新手遇到“改字符集”的需求时直接记这一条命令就够用了。注意CONVERT TO CHARACTER SET在大表上执行会锁表而且会重建整张表如果表里有几百万行数据执行时间会非常长。生产环境操作前建议先评估表大小做好变更窗口规划。4. 程序侧和客户端侧的字符集配置4.1 JDBC连接串里的字符集参数如果说数据库内部的字符集是地基那程序侧的连接串就是水管。水管没接好地基再稳水也流不上来。如果你用的是Java JDBC那连接串里必须写characterEncodingutf8对是utf8不是utf8mb4这是JDBC驱动的一个历史遗留问题改完这个参数后MySQL驱动会往服务端发送SET NAMES utf8mb4的语义。一个典型的JDBC连接串长这样jdbc:mysql://127.0.0.1:3306/myapp?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghai这里有几个点值得注意useUnicodetrue必须和characterEncoding一起写只写characterEncoding不写useUnicode在某些老版本驱动里不生效。如果你用的是MySQL Connector/J 8.x以上版本characterEncodingutf8会自动适配成utf8mb4不需要写utf8mb4写了反而可能报错。serverTimezone和字符集没有直接关系但很多新版本的JDBC驱动不写这个参数会报时区错误所以我习惯一起写上。4.2 Python连接MySQL的字符集写法如果你用Python不管是pymysql还是mysql-connector-python连接参数里都有charset这个字段。典型写法是import pymysql conn pymysql.connect( host127.0.0.1, userroot, password123456, databasemyapp, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor )这里注意Python这边的charset参数会直接影响客户端发出去的字符集声明。如果你写的charsetutf8而表结构是utf8mb4连接和存储之间会出现一个“翻译”步骤虽然大多数情况下不至于乱码但遇到特殊字符时还是会有隐患。所以最稳的做法就是Python侧写utf8mb4数据库表结构也用utf8mb4两边一致中间完全没有翻译损耗数据链路最干净。还有一点容易忽略的是文件读取时的字符集。很多人从文件里读数据再入库文件本身如果是GBK编码而Python脚本按照UTF-8去read()那读进来的字符串本身就是错的后面怎么设charset都没用。遇到这种场景读文件的时候要指定编码with open(data.txt, r, encodinggbk) as f: lines f.readlines()4.3 命令行工具和图形化工具的配置差异用MySQL自带的命令行客户端连库时Windows下的默认编码经常让人头疼。以前在Windows的cmd窗口里执行mysql -uroot -p然后查询中文经常出现乱码这是因为cmd默认代码页可能是GBK而客户端默认发的是UTF-8或者反过来。这时候你需要在登录后手动执行一次SET NAMES utf8mb4;这条命令会把客户端的三个传输层参数全部设置为utf8mb4这样查询结果返回时服务器就会按照utf8mb4编码“翻译”结果集。但cmd窗口本身能不能正确显示utf8mb4的中文字符还取决于窗口的代码页设置。在Windows下可以执行chcp 65001把代码页切到UTF-8配合SET NAMES utf8mb4双管齐下才能彻底解决命令行乱码。图形化工具这边以我常用的Navicat和DBeaver为例它们通常在连接参数里有一个“编码”或者“字符集”选项。用Navicat连接MySQL时右键连接-编辑连接-高级-编码下拉框里选utf8mb4即可。DBeaver则在连接设置里有一个Encoding字段选择UTF-8。这类工具默认配置一般问题不大但如果你遇到“表格数据看起来正常但导出SQL文件后乱码”的情况多半是导出时的字符集选项没选对注意看一下导出窗口里的编码选项就行。5. 零基础必看的乱码排查实战5.1 乱码的几种典型形态乱码这东西不同的乱长相背后原因完全不同。我列几个最常见的形态方便你对号入座乱码形态典型表现大概率原因问号中文全部变成???存储时客户端字符集和表字符集不一致导致数据在入库前就被丢弃成了问号基本不可逆转义符号显示为å¼ ï¼这类欧式字符通常是UTF-8编码的字节被GBK解码属于“翻译词典用错”数据本身还有救方块/黑点显示为无法识别的方块字体不支持该字符或者字符集转换后出现了非法字节数据正常但排序错中文排序和字典不一致排序规则COLLATE选错了和数据存储本身无关这里要特别强调第一行???这种乱码基本是彻底损毁。为什么因为?是服务器在“无法识别某个字节”的时候用来占位的替代符号一旦字节被替换成?原始字节就没法找回来了。所以如果你发现数据已经变成???不要想着怎么“解乱”赶紧想办法从原始数据源重新灌数据才是正经事。5.2 最实用的乱码排查三步法遇到乱码问题我自己的排查套路是固定的三步每次都很管用。第一步确认客户端声明的是什么编码先执行SHOW VARIABLES LIKE character_set_client;看看当前会话客户端声明的编码。如果客户端声明是gbk而你的SQL文件是UTF-8编码那从源头就错了后面怎么处理都没用。第二步确认表结构的实际存储编码执行SHOW CREATE TABLE 表名;看表的DEFAULT CHARSET和每个字段的CHARACTER SET。这一步能看到数据落库时用的是哪套编码规则。如果表是utf8mb4但客户端声明是gbk那character_set_client到character_set_connection的翻译过程就会出问题。第三步在数据库内部做转换测试如果你怀疑数据已经以错误的编码存进库了可以尝试用MySQL函数把字段内容转出来看看-- 查看字段的原始字节 SELECT HEX(name) FROM user WHERE id 1; -- 把字节按GBK解码、再按UTF-8编码输出 SELECT CONVERT(CAST(CONVERT(name USING gbk) AS BINARY) USING utf8mb4) FROM user WHERE id 1;HEX()函数特别有用它能让你看到数据最原始的字节形态。比如中文“张”在UTF-8编码下是E5 BC A0如果你查出来的一串字节不是这个那说明入库时用的编码就不是UTF-8。从十六进制入手排查是终极手段能跳过所有中间层的干扰直接看到数据文件里的真相。5.3 一个完整的乱码修复案例这里还原一个我实际处理过的类似场景。某项目有个用户昵称字段nickname VARCHAR(50)表结构是utf8mb4程序是Java连接串里也写了characterEncodingutf8。但上线后出现一个问题一部分老用户的昵称在新后台显示正常在老后台显示乱码而同一批数据在老后台导出Excel后又是一堆问号。排查过程是这样的先看老后台连接数据库的账号配置发现老系统连接串里没写characterEncoding参数驱动默认用ISO-8859-1去解码结果集也就是说服务器返回的utf8mb4字节被按ISO-8859-1解了一遍显示出来自然全是乱码。但数据本身一点问题都没有换新后台连接设置一查完全正常。这种场景其实特别典型数据没坏是读取端解码规则不对。处理方式也不是改数据而是让老系统的连接串加上正确的characterEncodingutf8参数同时在老系统入口处统一设置请求和响应的编码为UTF-8。5.4 从源头防止乱码的配置清单建库、建表统一指定utf8mb4不要依赖服务器默认值。程序连接串显式指定characterEncodingutf8或charsetutf8mb4。命令行登录后先执行SET NAMES utf8mb4;。图形化工具连接属性里确认编码为utf8mb4或UTF-8。所有源文件SQL脚本、数据文件、程序源码统一保存为UTF-8无BOM格式。HTTP接口层面设置Content-Type: text/html; charsetutf-8避免浏览器猜测编码导致显示乱码。线上变更字符集前先备份数据最好先在一张小表上演练一遍。6. 排序规则 COLLATE 的选择与影响6.1 什么场景下要关注排序规则排序规则COLLATE是字符集下的一个子概念它规定了这个字符集内部字符串的比较规则。很多零基础教程一句带过但实际业务里它真能给你挖坑。常见排序规则有这么几个utf8mb4_general_ciMySQL旧版默认规则。比较速度快但准确度一般。中文排序时不完全符合拼音/笔画顺序西文排序时个别语言规则处理得不好。utf8mb4_unicode_ci基于Unicode官方排序算法准确度更高速度略慢一点点但现代硬件上差异基本可以忽略。日常业务项目我建议优先选它。utf8mb4_bin按二进制值直接比较大小写敏感同时任何字符都严格区分。适合做精确匹配、密码哈希字段这类场景。这里解释一下ci的含义case insensitive大小写不敏感。你写WHERE name abc时它能匹配到ABC因为比较时a和A被视为同一个字符。反过来_bin排序规则下a和A是不同字符比较结果就是不等。6.2 排序规则对查询的影响实例举一个实际业务场景。假设用户表有个字段叫nickname你在查询时用了WHERE nickname admin如果排序规则是utf8mb4_unicode_ci那么Admin、ADMIN、admin全都算匹配会被查出来。这通常不是你想要的结果因为用户名是应该区分大小写的。解决办法有两种一种是把该字段的排序规则改成utf8mb4_bin另一种是查询时用BINARY关键字强制二进制比较SELECT * FROM user WHERE BINARY nickname admin;BINARY关键字会强制MySQL按字节比较不管字段定义的是什么排序规则。这个方案的好处是改动小只影响这一条SQL不会动表结构坏处是BINARY会让该查询无法用到基于普通比较规则的索引如果表很大性能可能下降。所以如果业务上确实需要区分大小写更好的方案还是改字段的排序规则为utf8mb4_bin。6.3 中文排序为什么会乱中文排序是个老大难问题。MySQL的排序规则对中文的排序逻辑是按照Unicode码点去排的并不是按拼音或者笔画。所以在MySQL里执行ORDER BY name得到的顺序对中文用户来说往往是“不可预期”的。举个例子“一”“二”“三”这三个字按照拼音排序应该是一、二、三但在MySQL默认排序规则下它们的顺序取决于Unicode码点大小实际排出来完全不是这个顺序。如果业务确实需要按拼音排序常见解决方案有两种。一种是在应用层用Java/Python的排序库去排数据量不大时很灵活另一种是加一个“拼音字段”比如name_pinyin存的是每个汉字的拼音排序时按这个字段排。第二种方案在数据量大的时候性能可控也更稳定唯一的问题是数据维护起来麻烦一点。提示MySQL 8.0及以上版本已经支持utf8mb4_zh_0900_as_cs这种更精细的中文排序规则但它仍然不是按拼音排列的是依据中文特定字典顺序排列的。如果业务对中文排序有严格要求建议先小范围验证效果别盲目切换。7. 常见问题速查表与避坑指南7.1 高频字符集问题速查问题现象排查方向推荐解法命令行查询中文变成问号客户端声明编码与表编码不一致执行SET NAMES utf8mb4;Windows下再执行chcp 65001Java程序插入emoji报错表字符集可能是utf8mb3将表和字段CONVERT TO CHARACTER SET utf8mb4Python插入数据中文正常但特殊符号变成乱码连接参数charset可能用了utf8改成charsetutf8mb4导出SQL文件后中文乱码导出工具的编码选项不对导出时选择UTF-8字符集注意SQL文件头不要带BOM老系统显示乱码但新系统正常连接参数缺字符集声明检查老系统的数据库连接串补上characterEncodingutf8表能查到数据但HEX()结果异常数据入库时编码就是错的用CONVERT(CAST(CONVERT(... USING gbk) AS BINARY) USING utf8mb4)尝试还原中文排序结果不符合预期排序规则不满足中文排序习惯增加拼音字段或在应用层排序7.2 我踩过的一些坑第一个坑是修改了数据库默认字符集之后没动老表。以前接手过一个项目DBA执行了一条ALTER DATABASE xxx DEFAULT CHARACTER SET utf8mb4;就宣布“字符集升级完成”了。结果过了两天业务反馈部分乱码一查才发现老表的字段全是utf8新写入的数据因为表级字符集的约束还是按老编码存储真是白忙活一场。自那以后我就养成了习惯查字符集现状时会同时比对库级和表级两处信息缺一不可。第二个坑是盲目使用CONVERT TO CHARACTER SET。在一个千万级别的大表上跑这条命令导致连接超时而且表被锁了很久。后来才知道这条命令本质上是把表重建一遍。大表上执行前最好评估磁盘空间和预计耗时最好在低峰期操作或者用改从库、再切换的方式来发布。第三个坑是给界面传入的数据做两次编码转换。有一次排查一个前端显示乱码问题找了半天发现后端程序在前端把从数据库查出来的字符串先encodeURIComponent了一遍取回后又用decodeURIComponent处理了一遍中间环节已经转码变形了。这种问题其实已经不完全是数据库字符集的问题了但排查时也要留意前端显示的编码链路和数据库字符集链路是串在一起的任何一环出错都可能导致最终展示异常。7.3 零基础最该记住的三条结论写到这里如果让我把整个字符集知识压缩成三句话送给零基础的朋友大概是这三条第一建库建表统一用utf8mb4连接串统一声明UTF-8。别管中间有多少层只要源头和存储端都是同一套规则乱码概率会大幅降低。第二遇到乱码先查SHOW VARIABLES LIKE character_set%再查SHOW CREATE TABLE 表名。这两条命令能定位出90%的字符集问题比瞎猜高效得多。第三数据一旦存储成???就救不回来了。任何修复手段都只能处理“编码解读错误”的情况无法处理“编码已被替换”的情况。所以备份和预防永远比事后修复重要。我个人在实际操作中的体会是字符集问题看起来复杂实际上就是一套“声明编码”和“存储编码”的匹配游戏。只要你不嫌麻烦地在每个建库建表、每个连接建立的环节都统一指定utf8mb4这套体系其实很稳定。真正容易出问题的永远是“有些地方按老规矩来有些地方按新规矩来”这种混用状态。所以如果你正在搭建一个新项目或者刚接手一个老项目花半天时间把所有字符集相关的配置过一遍绝对比日后被乱码问题折腾一星期划算得多。
