MySQL的Character Set和collate

常见的charset：

一般常用的charset是utf8、utf8mb4。

如下是MySQL 8的文档描述：

• utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.
• utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character. This character set is deprecated in MySQL 8.0, and you should use utfmb4
• utf8: An alias for utf8mb3. In MySQL 8.0, this alias is deprecated; use utf8mb4utf8 is expected in a future release to become an alias for utf8mb4.

总结来说，utf8mb4字符集支持最多使用4个字节来存储一个字符，这样就支持很多额外的特殊字符，比如emoji符号。可以说utf8mb3是个历史产物，当年MySQL支持UTF-8时，UTF标准里一个字符最多只占用3个字节。而后UFT-8标准更新后最多使用4个字节后，MySQL在5.5版本开始新增utf8mb4以支持4个字节存储一个字符。而到目前为止（2023-01）MySQL的utf8还是utf8mb3的别名，当然将来可能uft8会被指向utf8mb4。

 

以上都是老生常谈的知识了，大家无脑的使用utf8mb4就可以了。今天要说的其实是collate，collate主要用于排序和字符比较。因为最近遇到一个问题，就是sql查询时，使用 name like ‘%keywords%’时，MySQL是区分大小写匹配的，一看表的collate是utf8mb4_bin，破案了。那么我们来看看MySQL有哪些常用的collate。

 

utf8mb4_bin： 将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

utf8mb4_general_ci：ci即case insensitive，不区分大小写。是一个遗留的校对规则，不支持扩展，它仅能够在字符之间进行逐个比较，没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

utf8mb4_unicode_ci：是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

utf8mb4_general_cs：基本同utf8mb4_general_ci，区别是区分大小写。

utf8mb4_unicode_cs：基本同utf8mb4_unicode_ci，区别是区分大小写。

大家可以看到规律了吧：

*_bin: 表示的是binary case sensitive，也就是说是区分大小写的

*_cs: case sensitive，区分大小写

*_ci: case insensitive，不区分大小写

 

我本地MySQL8 配置文件中字符集相关变量

show variables like '%character%';

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       utf8mb3

……


show CHARSET like 'utf%'; -- 查看MySQL的default COLLATE


Charset Description  Default collation Maxlen

utf16     UTF-16 Unicode utf16_general_ci 4

utf16le  UTF-16LE Unicode    utf16le_general_ci     4

utf32     UTF-32 Unicode utf32_general_ci 4

utf8mb3       UTF-8 Unicode   utf8_general_ci   3

utf8mb4       UTF-8 Unicode   utf8mb4_0900_ai_ci  4

这里我们会发现一个特殊的collate：utf8mb4_0900_ai_ci ，那这个0900表示什么了？原来utf8mb4_0900_ai_ci 是MySQL8新增的collate，中间的0900，表示的是Unicode 9.0的规范。对应的之前就有的utf8mb4_unicode_520_ci、utf8mb4_unicode_ci等。

• utf8mb4_0900_ai_ciis based on UCA 9.0.0 weight keys (http://www.unicode.org/Public/UCA/9.0.0/allkeys.txt).
• utf8mb4_unicode_520_ciis based on UCA 5.2.0 weight keys (http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt).
• utf8mb4_unicode_ci(with no version named) is based on UCA 4.0.0 weight keys (http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt).

 

References:

1. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
2. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html
3. https://zh.wikipedia.org/zh-hans/UTF-8
4. https://dev.mysql.com/doc/refman/8.0/en/charset-database.html
5. https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html
6. https://www.lifesailor.me/archives/2676.html