字符集与排序规则——中文变“锟斤拷“的根源，一文搞定
关键词数据库字符集、UTF8、GBK、排序规则、collation、乱码问题大家好我是小耶写功课只是为了我踩过的坑你们别再踩了数据库里有两个设计平时不显山不露水一出错就是灾难——字符集和排序规则。你可能遇到过这些场景从老库导数据到新库中文变成了锟斤拷两个表做JOIN字段内容一样却关联不上用户存了个emoji表情数据库直接报错。这些问题表面上看八竿子打不着根子都在同一个地方字符集和排序规则没搞对。今天把字符集这件事从头到尾讲清楚帮你避开那些让人头疼的坑。几个先搞明白的概念字符集Charset数据库用来表示文字的编码规则。简单说就是规定每个文字对应哪个二进制数字。不同的字符集能表示的文字范围不同——Latin1只能表示英文和西欧文字GBK能表示中文UTF8能表示全世界几乎所有文字。字符编码字符集的具体实现方式。同一个字符集可能有多种编码方式。比如UTF8字符集在MySQL里有utf8实际是utf8mb3最多3字节和utf8mb4最多4字节两种编码后者才能存emoji。排序规则Collation决定字符串怎么比较、怎么排序的规则。比如大小写是否敏感、中文按拼音还是按笔画排序。同一个字符集可以有不同的排序规则排序规则选错了JOIN和WHERE匹配就会出问题。你可以把这三者的关系理解成字符集决定能写哪些字编码决定每个字占几个字节排序规则决定这些字怎么比大小。主流字符集对比Latin1ISO-8859-1最早期的单字节字符集只能表示256个字符覆盖英文和西欧语言。在MySQL里是早期的默认字符集。如果你的库里还有Latin1的表存中文就会变成一堆问号——因为它压根不认识中文字。GBK/GB2312中文字符集。GB2312是最早的简体中文标准包含6700多个常用汉字。GBK是它的扩展版包含21000多个汉字和符号。如果你的系统只在国内用GBK够用但和国际化系统对接时遇到繁体字或少数民族文字就会出问题。UTF8国际通用字符集能表示全世界几乎所有文字。但这里有个经典大坑MySQL里的utf8字符集其实是个阉割版——它最多只支持3字节编码而真正的UTF8RFC标准是支持4字节的。UTF8MB4真正的UTF8完整实现支持4字节编码。emoji表情、一些生僻汉字、部分特殊符号都需要4字节才能存。2018年之后所有新项目都应该直接用utf8mb4没有例外。五大经典踩坑场景场景一数据迁移变锟斤拷这是最经典的乱码场景。从GBK编码的旧库导出数据导入到UTF8编码的新库中文全变成了乱码。根因导出时数据库按GBK编码把中文转成二进制导入时新库按UTF8去解码同样的二进制串在两种编码下对应的文字完全不同就出现了乱码。排查方法-- 查看当前库的字符集设置 SHOW VARIABLES LIKE character_set%; -- 查看某张表的字符集 SHOW CREATE TABLE table_name; -- 查看某个字段的字符集 SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table;解决方案导出时明确指定字符集# 导出时指定字符集 mysqldump --default-character-setgbk old_db dump.sql # 导入时指定目标字符集 mysql --default-character-setutf8mb4 new_db dump.sql场景二emoji存不进去用户输入带emoji的昵称或评论数据库直接报错Incorrect string value: \xF0\x9F\x98\x80根因MySQL的utf8字符集只支持3字节emoji需要4字节。\xF0\x9F\x98\x80是一个4字节的UTF8编码序列被utf8字符集拒绝。解决方案把字段、表或库的字符集改成utf8mb4-- 修改字段字符集 ALTER TABLE users MODIFY nickname VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 修改整张表字符集 ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;场景三JOIN因为collation不同走不了索引两个表做JOIN关联字段内容完全一样但结果不对或者走了全表扫描。根因两个字段的排序规则collation不同。比如一个字段用utf8mb4_general_ci另一个用utf8mb4_unicode_ci数据库在做比较时认为它们不是同一个规则索引无法使用。-- 检查两个表的collation是否一致 SHOW CREATE TABLE table_a; SHOW CREATE TABLE table_b;解决方案统一collation。建议在同一个数据库中所有表使用相同的字符集和排序规则-- 统一修改字段collation ALTER TABLE orders MODIFY user_id VARCHAR(50) COLLATE utf8mb4_unicode_ci;场景四中文排序不对SELECT * FROM products ORDER BY name;按中文名称排序结果既不是拼音顺序也不是笔画顺序而是按二进制码点排序完全乱序。根因默认collation是二进制比较不是按拼音排序。解决方案使用支持中文排序的collation-- MySQL 8.0使用utf8mb4_zh_pinyin_ci SELECT * FROM products ORDER BY name COLLATE utf8mb4_zh_pinyin_ci; -- 或者建表时指定 CREATE TABLE products ( name VARCHAR(100) COLLATE utf8mb4_zh_pinyin_ci );场景五连接层字符集不对导致乱码数据库和表都设了utf8mb4但客户端插入中文还是乱码。根因客户端连接的字符集设置和数据库不一致。客户端按自己的字符集发送数据数据库按连接字符集做转换如果连接字符集设错了数据在入库前就已经被错误转换。解决方案连接时明确指定字符集-- 连接后立即执行 SET NAMES utf8mb4; -- 或者在连接字符串/配置文件中指定 -- MySQL: ?characterEncodingutf8mb4 -- JDBC: jdbc:mysql://host/db?characterEncodingutf8mb4useUnicodetrue字符集选型建议新项目直接用utf8mb4排序规则选utf8mb4_unicode_ci国际通用或utf8mb4_zh_pinyin_ci中文按拼音排序。不要犹豫不要先用utf8再说。老项目迁移先检查现有数据的实际编码用HEX()函数看原始字节确定是GBK还是UTF8。然后制定迁移计划先改连接字符集→再改字段字符集→最后改表和库的默认字符集。不要一步到位直接ALTER全库大数据量下会很慢。不同场景的字符集选择 纯国内业务只需要中文 → GBK也可以但不推荐限制未来扩展 需要存emoji、繁体字、多语言 → utf8mb4必须 金融、政务系统有国密要求 → 按项目规范来通常是utf8mb4 和老系统对接 → 先确认老系统的实际编码再做映射避坑检查清单建库前过一遍这个清单能省后续无数麻烦1️⃣ 数据库默认字符集 → utf8mb42️⃣ 建表默认字符集 → utf8mb43️⃣ 客户端连接字符集 → utf8mb44️⃣ 排序规则统一 → 同一个库内所有表用相同collation5️⃣ 数据迁移前确认源库和目标库的字符集6️⃣ 应用层JDBC/ORM连接字符串里指定字符集参数能统一的地方全部统一字符集不一致是乱码的第一大来源。小耶在手SQL不愁。还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~
