概要
主旨是为了研究mysql里的字符集,在实践中发现中文的排序顺序有点问题,为了能让中文排序正常,尝试更改各种字符集做测试。
Mysql官方版本8.0以前默认的应该是latin1,8.0后默认为utf8mb4了
Docker安装Mysql
docker拉取Mysql官方镜像站地址:https://hub.docker.com/_/mysql
docker可以一键拉取,并映射本地/data/mysql/data 到 容器里的 /var/lib/mysql
docker pull mysql:8.2.0-oraclelinux8
docker run --name mysql8 -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root密码 -p 3306:3306 -p 33060:33060 -d mysql:8.2.0-oraclelinux8
字符集配置
官方相关手册说明:MySQL :: MySQL 8.0 Reference Manual :: 10.2 Character Sets and Collations in MySQL
我们可以使用几个命令查看
SELECT version(); #查看数据库版本
SHOW CHARACTER SET; #查看数据库字符集
SHOW COLLATION WHERE Charset = 'utf8mb4'; #查看utf8mb4里可用的collation
SHOW VARIABLES LIKE 'character_set%'; #查看系统里对象的默认字符集
SHOW VARIABLES LIKE 'collation_database%'; #查看数据库的默认collation
utf8mb4是 utf8 的一个超集,就是可以多存放一些类型,比如表情符号,是Mysql8后的推荐的字符集,最大支持长度4个字节,最小占用存储长度还是1个字节,utf8的边码规则和原理可以查看我的同名博客文章 ASCII、Unicode、UCS-2、UTF-8互转
排序规则说明
我们查看utf8mb4下面其实会有很多排序规则可以选择
以上这些规则具体选用哪一个,选择的依据是是什么呢
以默认的这个 utf8mb4_0900_ai_ci 示例说明
● 0900 是指 UCA 9.0.0标准。其中 UCA是 Unicode Collation Algorithm的缩写,UCA 9.0.0标准可以参考( https://www.unicode.org/Public/UCA/9.0.0/allkeys.txt )
像 utf8mb4_unicode_520_ci 就是 UCA 5.2.0 ( https://www.unicode.org/Public/UCA/5.2.0/allkeys.txt )
● ai、ci说明
官方这一块的说明:MySQL :: MySQL 8.0 Reference Manual :: 10.3.1 Collation Naming Conventions
Suffix | Meaning |
---|---|
_ai | Accent-insensitive |
_as | Accent-sensitive |
_ci | Case-insensitive |
_cs | Case-sensitive |
_ks | Kana-sensitive |
_bin | Binary |
ai和as是相对的,是否区分重音的意思,对于中国场景其实是用不到的,比如 Cafe和Café,一个头上有音标一个没有音标这种。
ci和cs是相对的,是否大小写敏感,如果是ci场景,hengrui=Hengrui,如果是cs场景 hengrui≠Hengrui
ks 是日语里假名的意思 かな(kana)
还有带地域的 utf8mb4_zh_0900_as_cs
● zh是中文的意思
这一块简写可以参考下表,官方地址:MySQL :: MySQL 8.0 Reference Manual :: 10.10.1 Unicode Character Sets
Language | Language Specifier |
---|---|
Bosnian | bs |
Bulgarian | bg |
Chinese | zh |
Classical Latin | la or roman |
Croatian | hr or croatian |
Czech | cs or czech |
Danish | da or danish |
Esperanto | eo or esperanto |
Estonian | et or estonian |
Galician | gl |
German phone book order | de_pb or german2 |
Hungarian | hu or hungarian |
Icelandic | is or icelandic |
Japanese | ja |
Latvian | lv or latvian |
Lithuanian | lt or lithuanian |
Mongolian | mn |
Norwegian / Bokmål | nb |
Norwegian / Nynorsk | nn |
Persian | persian |
Polish | pl or polish |
Romanian | ro or romanian |
Russian | ru |
Serbian | sr |
Sinhala | sinhala |
Slovak | sk or slovak |
Slovenian | sl or slovenian |
Modern Spanish | es or spanish |
Traditional Spanish | es_trad or spanish2 |
Swedish | sv or swedish |
Turkish | tr or turkish |
Vietnamese | vi or vietnamese |
注意点
在不同字符排序规则之间做比较系统是会报错的,所以建议在设计数据库的时候就设定好
报错示例
如果历史因素必须出现不同字符排序,可以手动声明规则,示例:collate utf8mb4_0900_ai_ci
SELECT t1.*
FROM test.table1 t1
inner join test.table2 t2 on t1.comment = t2.comment collate utf8mb4_0900_ai_ci