1.配置utf8mb4
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
方案一:修改my.ini文件
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
character-set-client-handshake=true
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
default-storage-engine=MyIsam
##....其它配置省略
[client]
port=3306
default-character-set=utf8mb4
方案二:执行下面的语句,只针对当前会话有效
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
后面的内容为网上查找的内容,辅助了解utf8mb4相关问题
2. utf8mb4_unicode_ci 与 utf8mb4_general_ci 如何选择
字符除了需要存储,还需要排序或比较大小,涉及到与编码字符集对应的 排序字符集(collation)。ut8mb4对应的排序字符集常用的有 utf8mb4_unicode_ci
、utf8mb4_general_ci
,到底采用哪个在 stackoverflow 上有个讨论,What’s the difference between utf8_general_ci and utf8_unicode_ci
主要从排序准确性和性能两方面看:
- 准确性
utf8mb4_unicode_ci
是基于标准的Unicode来排序和比较,能够在各种语言之间精确排序utf8mb4_general_ci
没有实现Unicode排序规则,在遇到某些特殊语言或字符是,排序结果可能不是所期望的。
但是在绝大多数情况下,这种特殊字符的顺序一定要那么精确吗。比如Unicode把ß
、Œ
当成ss
和OE
来看;而general会把它们当成s
、e
,再如ÀÁÅåāă
各自都与A
相等。 - 性能
utf8mb4_general_ci
在比较和排序的时候更快utf8mb4_unicode_ci
在特殊情况下,Unicode排序规则为了能够处理特殊字符的情况,实现了略微复杂的排序算法。
但是在绝大多数情况下,不会发生此类复杂比较。general理论上比Unicode可能快些,但相比现在的CPU来说,它远远不足以成为考虑性能的因素,索引涉及、SQL设计才是。 我个人推荐是utf8mb4_unicode_ci
,将来 8.0 里也极有可能使用变为默认的规则。相比选择哪一种collation,使用者应该更关心字符集与排序规则在db里要统一就好。
这也从另一个角度告诉我们,不要可能产生乱码的字段作为主键或唯一索引。我遇到过一例,以 url 来作为唯一索引,但是它记录的有可能是乱码,导致后来想把它们修复就特别麻烦。
3. key 768 long 错误
字符集从utf8转到utf8mb4之后,最容易引起的就是索引键超长的问题。
对于表行格式是 COMPACT
或 REDUNDANT
,InnoDB有单个索引最大字节数 768 的限制,而字段定义的是能存储的字符数,比如 VARCHAR(200)
代表能够存200个汉字,索引定义是字符集类型最大长度算的,即 utf8 maxbytes=3, utf8mb4 maxbytes=4,算下来utf8和utf8mb4两种情况的索引长度分别为600 bytes和800bytes,后者超过了768,导致出错:Error 1071: Specified key was too long; max key length is 767 bytes
。
COMPRESSED
和DYNAMIC
格式不受限制,但也依然不建议索引太长,太浪费空间和cpu搜索资源。
如果已有定义超过这个长度的,可加上前缀索引,如果暂不能加上前缀索引(像唯一索引),可把该字段的字符集改回utf8或latin1。
但是,( 敲黑板啦,很重要 ),要防止出现 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
错误:连接字符集使用utf8mb4,但 SELECT/UPDATE where条件有utf8类型的列,且条件右边存在不属于utf8字符,就会触发该异常。表示踩过这个坑。
再多加一个友好提示:EXPLAIN 结果里面的 key_len 指的搜索索引长度,单位是bytes,而且是以字符集支持的单字符最大字节数算的,这也是为什么 INDEX_LENGTH 膨胀厉害的一个原因。
4. 参考
- https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html
- database - Why are we using utf8mb4_general_ci and not utf8mb4_unicode_ci? - Drupal Answers
- MySQL :: Re: utf8_unicode_ci vs utf8_general_ci
- 10分钟学会理解和解决MySQL乱码问题 • cenalulu's Tech Blog
- How to support full Unicode in MySQL databases · Mathias Bynens
- MySQL表字段字符集不同导致的索引失效问题