1. 新建数据库时的字符集与排序规则到底该怎么选很多人在安装完 MySQL 之后第一步就是打开命令行或者 Navicat敲一句CREATE DATABASE但到了选择字符集那一栏就开始犯难了。utf8mb4 和 utf8mb3 到底差在哪utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci这一堆排序规则看着都差不多选错了会有什么后果先说结论2024 年新建 MySQL 数据库除非你有特殊的历史兼容需求否则字符集选utf8mb4排序规则在 MySQL 8.0 里默认的utf8mb4_0900_ai_ci可以直接用在 5.7 里选utf8mb4_general_ci也问题不大。但这个结论背后有很多细节值得聊因为字符集选错前期可能看不出问题等数据量上来、业务跑起来再想改就麻烦大了。这篇文章不是给你背文档而是从实际建库的角度把字符集、排序规则这些概念拆开揉碎讲清楚它们是怎么影响你的数据存储、查询排序、索引命中的再给你一套可以直接照着抄的建库方案。适合谁看刚入门 MySQL 的新手、正在做数据库课程设计的同学、从 Oracle 或 SQL Server 转过来的老开发以及被线上乱码问题折磨过的运维朋友。看完这篇文章你不仅知道怎么选还能知道为什么这么选。2. 先把概念搞清楚字符集和排序规则不是一回事2.1 字符集决定你能存什么排序规则决定你怎么查我见过不少同学把字符集和排序规则混为一谈其实两者职责完全不同。字符集Character Set决定了一个数据库能存储哪些字符。它是一张映射表把人类能看懂的字符映射成计算机能存能算的二进制数据。比如 ASCII 字符集只能存英文字母、数字和少量符号一个字符占 1 个字节而 GBK 能存中文一个汉字占 2 个字节utf8mb4 能存世界上几乎所有语言的字符包括中文、日文、韩文、阿拉伯文甚至 emoji 表情符号。排序规则Collation则是在字符集的基础上规定了字符之间如何比较和排序。也就是说字符集决定能存哪些字符排序规则决定这些字符谁大谁小、谁的优先级高、查的时候怎么匹配。同一个字符集下可以搭配多个不同的排序规则它们对大小写是否敏感、对中文拼音排序是否友好、对特殊字符的权重处理方式都会有差异。我用一个生活化的类比来解释字符集就像一套完整的砖块砖块的种类决定了你能盖什么样的房子排序规则就像你摆放砖块的顺序规则同样一批砖按大小排、按颜色排、按重量排结果完全不一样。2.2 utf8mb3 和 utf8mb4一字之差天壤之别在 MySQL 里utf8这个字符集名字本身就有一个历史包袱问题。在 MySQL 8.0 之前utf8是utf8mb3的别名它最多支持 3 个字节来存储一个字符。这意味着一个字符最多只能编码到 Unicode 的基本多语言平面BMP涵盖范围是绝大多数常用字符中文、英文、日文假名、韩文谚文都在里面。但问题是emoji 表情不在 BMP 里面。emoji 需要 4 个字节才能存储。如果你建库的时候用了utf8即 utf8mb3往里插入一个 立刻报错Incorrect string value这就是很多人第一次遇到乱码和插入失败的常见原因之一。utf8mb4则是完整的 UTF-8 编码一个字符最多占 4 个字节可以覆盖 Unicode 全部字符集。MySQL 官方从 8.0 开始已经把默认字符集从utf8mb4之前的latin1改成了utf8mb4等于官方也默认新的数据库就应该用 utf8mb4。所以通用原则是任何新建的数据库不要再用utf8或utf8mb3一律utf8mb4除非你有极其特殊的存量业务限制。2.3 排序规则里那些后缀是什么意思看到一个排序规则名字比如utf8mb4_0900_ai_ci很多人一头雾水。其实拆开看并不复杂utf8mb4这个排序规则基于的字符集0900指的是 Unicode 排序算法版本号9.0.0 版本。5.7 及之前的版本没有这个标识因为当时用的是旧版权重aiaccent insensitive表示对重音符号不敏感cicase insensitive表示对大小写不敏感还有一个常见的bin后缀比如utf8mb4_bin表示二进制比较直接按字符的二进制编码来比区分大小写不做任何语言层面的归一化。理解这些后缀之后你就能自己读懂 MySQL 里几十种排序规则的含义了。_general_ci是早期 MySQL 实现的一套简单比较逻辑速度快但不够精确_unicode_ci是实现了 Unicode 排序算法UCA的版本比较精确但旧版5.7 前的 unicode_ci速度稍慢一些8.0 里的_0900_ai_ci则是基于新版 UCA 9.0.0 的实现做到了既快又准。3. MySQL 8.0 和 5.7 默认值变化别吃了旧习惯的亏3.1 版本差异直接影响你的选择策略很多人在 5.7 上用了很多年习惯性地把CREATE DATABASE语句里的默认字符集改掉然后直接用到 8.0 上。大部分情况下没问题但默认排序规则的变化值得注意。MySQL 8.0 的默认字符集是utf8mb4默认排序规则是utf8mb4_0900_ai_ci。而 MySQL 5.7 的默认字符集是latin1如果手动指定utf8mb4默认排序规则是utf8mb4_general_ci。两个版本都是 utf8mb4 字符集但排序规则不同会导致什么问题最典型的是索引失效。如果你有一个表在 5.7 上用utf8mb4_general_ci建好了然后迁移到 8.0某些查询如果你在 SQL 里不写排序规则MySQL 会使用表的默认设置。两边排序规则不一致关联查询时可能出现Illegal mix of collations的错误直接报错或者导致无法使用索引查询性能骤降。所以跨版本迁移时不要只看字符集排序规则也必须统一检查。3.2 8.0 的 utf8mb4_0900_ai_ci 到底好在哪如果说 5.7 年代你必须在general_ci和unicode_ci之间做取舍那么 8.0 的0900_ai_ci基本上终结了这个选择题。_0900_ai_ci基于 Unicode 9.0.0 标准的校对算法在比较和排序的准确性上比general_ci高了一个台阶。最直观的例子是general_ci在比较英文字符时有一些历史遗留的不规范行为比如它会把a、à、á视为同一级别的等价但某些边缘字符的处理与 Unicode 标准不一致。而0900_ai_ci严格遵循 Unicode Collation Algorithm处理多语言文本时更加符合预期。性能上8.0 对 UCA 实现做了大量优化0900_ai_ci的排序和比较速度比 5.7 时代的utf8mb4_unicode_ci更快接近甚至超过general_ci。所以精确的比较慢这个旧印象已经过时了。3.3 有个大坑0900_ai_ci 对中文拼音排序并不友好虽然0900_ai_ci在 Unicode 标准上很正确但如果你在中国做业务有一个实际情况要想清楚你的中文排序是按什么规则排utf8mb4_0900_ai_ci是中性的 Unicode 排序规则它不专门针对中文拼音做优化。如果你对一列中文数据用ORDER BY name得到的顺序是按 Unicode 编码点排序不是按拼音首字母排。这意味着阿里巴巴不一定会排到百度前面大概率是按汉字的 Unicode 编码顺序来的。如果你的业务明确要求中文按拼音排序就不能只依赖数据库默认排序规则要么在应用层用拼音字段单独处理要么考虑其他方案。这个跟排序规则选哪个没有绝对关系0900_ai_ci不是不好而是它的设计目标本来就不是中文专属排序。4. 新建数据库的实操指南命令行和图形化工具双方案4.1 命令行建库这条 SQL 直接抄如果你习惯命令行操作新建数据库的完整 SQL 语句如下CREATE DATABASE your_db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;这是 MySQL 8.0 上最推荐的写法。如果你用的是 MySQL 5.7utf8mb4_0900_ai_ci这个排序规则并不存在需要换成utf8mb4_general_ciCREATE DATABASE your_db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;注意 MySQL 5.7 不能使用utf8mb4_0900_ai_ci8.0 之前的版本没有这个规则。如果你在 5.7 里执行会直接报错这也是很多人从网上复制建库语句到旧版本执行失败的主要原因。 /注意建完数据库之后建议顺手验证一下SHOW CREATE DATABASE your_db_name;这条命令会显示该数据库的实际字符集和排序规则确保跟你的预期一致不会因为全局配置或 session 配置的干扰而变成别的字符集。4.2 Navicat 图形化建库点几下就搞定用 Navicat 之类的图形化工具更简单。连接数据库后右键点击连接下的数据库区域选择新建数据库弹出窗口里填写数据库名然后重点看字符集和排序规则两个下拉框。字符集选择utf8mb4排序规则在 MySQL 8.0 上会自动带出utf8mb4_0900_ai_ci保持默认即可在 MySQL 5.7 上会自动带出utf8mb4_general_ci也保持默认即可。如果你想让中文按拼音排序且不区分重音可以选择utf8mb4_zh_0900_as_csMySQL 8.0 提供的中文排序规则之一但要注意它区分大小写。很多初学者容易踩的坑是数据库这一层的字符集选好了但建表时不指定默认继承数据库的可如果你建表时手动指定成了别的字符集那数据库层面的设置就白做了。表层面的字符集优先级高于数据库层面所以建表时务必也检查一下。4.3 连接层的编码建库对了只是一半数据库建对了字符集但你的连接层编码不对照样会出现中文乱码或 emoji 存不进去的情况。常见的表现是数据库设置明明是 utf8mb4但通过命令行查询返回的中文是乱码或者程序写入时中文显示正常但特殊字符变成问号。我给你的排查和解决路径是固定的连接字符串里必须指定字符集参数。JDBC 连接串加上characterEncodingutf8和useUnicodetruePHP 的 mysqli 连接加上SET NAMES utf8mb4Python 的 pymysql 连接参数里加上charsetutf8mb4。连接建立成功后可以执行一句SHOW VARIABLES LIKE character_set_connection;确认连接层字符集是utf8mb4而不是latin1。很多乱码问题根源不在建库而在连接层默认字符集不对这个我之前排查线上问题时遇到过很多次了。5. 不同业务场景下排序规则的选择策略5.1 通用业务系统就用默认别折腾如果你做的是一套常规的 Web 系统、管理后台、内容管理系统用户主要用简体中文和英文没有特殊的排序、搜索需求我的建议非常简单MySQL 8.0 直接用utf8mb4_0900_ai_ciMySQL 5.7 直接用utf8mb4_general_ci。理由一是官方默认值就是经过充分考虑和测试的踩坑概率最低理由二是现在很多 ORM 框架和数据库迁移工具默认配置都按官方默认值来你手动改成其他排序规则反而可能在工具链里产生兼容问题。比如有些团队用 Flyway 管理数据库版本脚本里写死了排序规则换数据库实例时发现排序规则不存在导致迁移失败。这种问题属于自己给自己找麻烦没必要。5.2 多语言业务注意大小写和重音匹配策略如果你的业务涉及多语言比如跨境电商、国际化社交产品那0900_ai_ci的优势能发挥出来。aiaccent insensitive意味着用户在搜索 cafe 时可以匹配到 cafécicase insensitive意味着 Apple 和 apple 视为相同。这种宽容的匹配规则对搜索体验来说通常是好事但它也会带来一个副作用如果你需要做精确去重比如用户名不区分大小写那用_ci排序规则建唯一索引时会发现 Admin 和 admin 会冲突这是符合预期的。而如果业务要求用户名严格区分大小写那就该选utf8mb4_bin或utf8mb4_0900_bin这类二进制排序规则。5.3 需要二进制精确匹配的场景有些业务场景对字符比较的严格性要求很高。典型的如存储系统路径、JWT token、哈希值、编码唯一标识。这些值本质上是大小写敏感的字符串如果用_ci规则去建唯一索引一旦存在 AbC 和 abc 两个不同的 token数据库直接拒绝插入第二个。这种场景就应该用utf8mb4_bin5.7或utf8mb4_0900_bin8.0。二进制排序规则对两个字符串的每一个字节直接比较不存在大小写归一化只要字节不同就算不同。要注意的是选择_bin规则后ORDER BY的结果会按字节序排列这通常不是你想要的人性化排序所以日常业务查询如果需要按名称排序建议在应用层做或者额外加一个拼音/笔画排序字段。5.4 中文排序需求拼音排序到底怎么处理说句实在话MySQL 对中文拼音排序的支持一直不算好。MySQL 5.7 的utf8mb4_general_ci和 8.0 的utf8mb4_0900_ai_ci都不能完美解决中文按拼音排序的问题。MySQL 8.0 引入了utf8mb4_zh_0900_as_cs这个中文专属排序规则实测可以按中文拼音排序但它同时满足asaccent sensitive重音敏感和cscase sensitive大小写敏感也就是说它区分大小写这在某些业务中不符合要求。如果你的业务确实需要按中文拼音排序我的实操建议是不要在数据库层面硬扛而是在用户表中单独维护一个pinyin或sort_key字段插入记录时用 Java 的 pinyin4j、Python 的 pypinyin 这类库生成拼音索引然后按这个字段排序。这样保证了排序的确定性和灵活性不管是拼音、笔画还是自定义权重都能实现。6. 已经建好库了发现字符集不对怎么补救6.1 数据库层面直接改但要分批操作如果你的库是刚建的里面没什么数据直接执行ALTER DATABASE your_db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;这个命令只改数据库的默认属性不会自动修改已有表的字符集。如果你的库里面已经有表了每个表需要单独改ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CONVERT TO这个命令会直接把表中已有的数据也转成新的字符集如果数据量大、文本字段多会锁表可能影响线上业务一定不要在生产环境直接执行要先在测试环境验证或者用在线 DDL 工具。6.2 数据已经乱码了怎么救回来最麻烦的情况是数据库建立了很久数据已经以错误的字符集存进去了中文显示成???或者一堆乱七八糟的符号。先说一个残酷的现实如果数据在存储时因为字符集不对已经变成了?那是无法恢复的因为原始字节已经丢了。但如果只是显示层面的混乱比如客户端字符集和数据库不一致导致的错位显示还有机会通过重新转换恢复。我踩过一个典型的坑表原来是latin1存储但业务写入时实际写的是 UTF-8 字节。此时 Navicat 打开中文显示是乱码但如果我执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4会基于latin1的字节做一次新映射反而让乱码更严重。正确的操作是先用latin1导出数据再用utf8mb4导入或者用HEX()函数确认原字节然后手动转码。遇到这种情况不要着急先把SELECT HEX(column)出来看原始字节是否符合正常 UTF-8 编码的规则再决定转码方案。6.3 改完字符集后别忘了重建索引很多人改完表字符集后发现查询速度变慢了以为是字符集本身的问题其实真正的原因是原有的索引没有重建。字符集改变后索引的排序规则也需要跟着变MySQL 某些版本下旧的索引可能没有自动适配。我建议你在完成ALTER TABLE ... CONVERT TO之后务必检查一下SHOW INDEX FROM your_table_name;必要时删除旧的索引并重新创建确保索引的 collation 和当前表一致。否则排查半天性能问题结果发现是索引没重建那就太冤了。7. 常见问题速查与避坑指南先说一个高频问题为什么我建表时明明写了 utf8mb4但表实际是 latin1这种情况通常是 MySQL 配置文件里的character_set_server被设置成了别的值或者建表语句中某个字段级别写了CHARACTER SET latin1覆盖了表级别的设置。字段级 表级 数据库级 服务器级优先级从高到低是这样的。你建表时如果字段级别的字符集指定了别的表级设置不会生效。解决方式是检查建表语句确保没有字段级字符集覆盖。再来一个高频问题可以只改一列的字符集吗可以但非必要不建议。ALTER TABLE只针对单个字段改字符集会产生行级锁或表级锁数据量大时影响较大。而且字段级字符集和表级字符集不一致会埋下隐患查询时如果关联字段的 collation 不一致会出现Illegal mix of collations报错。我的建议是保持整表统一不要搞出混血的表结构。关于排序规则和索引的搭配还有一个很重要的点我写了这么多次collation相关的排查最后总结成一个速查表给你场景MySQL 8.0 推荐MySQL 5.7 推荐常规业务系统中文英文utf8mb4_0900_ai_ciutf8mb4_general_ci多语言搜索、不区分重音utf8mb4_0900_ai_ciutf8mb4_unicode_ci用户名、token、大小写敏感utf8mb4_0900_binutf8mb4_bin中文拼音排序utf8mb4_zh_0900_as_cs或应用层处理应用层处理最后一个提醒很多人在建库的时候容易忽略列级别的字符集。表建好了字符集也对了但字段级别如果单独指定了还是会在查询和排序时出问题。最稳妥的检查方式是SHOW FULL COLUMNS FROM your_table_name;查看Collation一列确保所有文本字段都跟表保持一致。8. 建库前想清楚这三步能少走很多弯路新建一个数据库字符集和排序规则看起来只是初始化的一个步骤但它的影响会跟随这个库的整个生命周期。我个人的体感是很多人因为前期没想清楚后续在数据迁移、跨表关联、乱码修复上花的时间远比当初多花五分钟搞清楚要耗得多。我平时调研一个数据库选型基本遵循这三步第一步确认 MySQL 大版本因为 5.7 和 8.0 可用的排序规则完全不同这一步直接决定了你能选什么。第二步评估业务数据的语言范围是不是只要中文和英文还是有多语言需求有没有 emoji 存储需求是否需要大小写敏感的比较。第三步确认排序需求是默认的编码序就可以还是需要按拼音、笔画、重音等特定规则排序。这三步想清楚之后再打开命令行或者 Navicat 建库你心里就有底了不会再对着下拉框发愁也不会被网上各路说法带偏。