前言:
前段时间项目出现了莫名其妙的报错,在小程序端获取用户信息并存入数据库时发现微信昵称非法了!
排查之后发现用户的微信昵称
中有emojo
表情,mysql
的utf8
编码集无法存入带有emoji
表情的微信昵称
,所以应该将utf8
修改成utf8-mb4
。
但mb4
中又有unicode_ci
和general_ci
等排序规则。在选择时我也产生了困惑,通过彻夜(并不)的查资料之后,总结了这篇文章;
他们之间又有什么区别和联系呢?mysql 8 中默认的是什么排序方式呢?让我们带着问题进入文章吧!
1. 版本号和编码集的查看方法
脱离版本号谈字符编码集都是耍流氓,首先我们要学会查看版本号和字符编码集。
1.1 MySQL查看版本号的方法
Linux:
-
grep
指令查看 -mysql --help | grep Distrib
[root@heyong tools]# mysql --help | grep Distrib mysql Ver 14.14 Distrib 5.7.30, for xxxxxxxxxxxxxxx...
-
status
指令查看 - 连接上MySQL加粗样式后执行status
mysql> status mysql Ver 14.14 Distrib 5.7.30, for xxxxxxxxxxxxxxx... ...... Server version: 5.7.30 MySQL Community Server (GPL) ......
-
登录连接
时查看[root@heyong ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 874163 Server version: 5.7.30 MySQL Community Server (GPL) ......
Navicat:
- select命令查看 -
select version();
1.2 MySQL查看字符集的方法
- 查看默认字符集:
show variables like '%charac%';
(这里以MySQL5.7版本的为例)
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
- 查看排序规则 -
show variables like 'collation_%';
collation_connection utf8mb4_general_ci
collation_database utf8_bin
collation_server utf8_bin
2. 不同版本的默认字符集及解释
2.1 MySQL 5.7 默认字符集
使用命令:show variables like '%charac%';
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
2.2 MySQL 8 默认字符集
使用命令:show variables like '%charac%';
mysql> show variables like '%charac%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.02 sec)
2.3 MySQL 字符集变量解释
- character_set_client :默认的
内部操作字符集
- character_set_client:客户端
来源数据
使用的字符集 - character_set_connection:
连接层
字符集 - character_set_results:
查询结果
字符集 - character_set_database:当前选中
数据库的默认字符集
决定了新建数据库的默认字符集;数据库的字符集又决定了建表默认字符集;表字符集又决定了字段默认字符集。
如果没有通过default character set = xxx
来改变表字符集,则新表默认使用character_set_database
指定的字符集。 - character_set_system:系统元数据(字段名等)字符集
- 还有以collation_开头的同上面对应的变量,用来描述字符序。
由上总结可以可以发现5.7在建表时默认使用latin1
字符集;而8在建表时默认使用utf8mb4
字符集。
2.4 utf8 与 utf8mb4(utf8 most bytes 4)
- Mysql5.5.3之后增加了utf8-mb4字符编码集
- 支持BMP(Basic Multilingual Plane,基本多文种平面) 和补充字符
- 最多使用四个字节存储字符
utf8mb4 是 utf8 的超集并完全兼容 utf8,能够用四个字节存储更多的字符。标准的utf8字符集编码是可以使用1-4个字节去编码21位字符,这几乎包含了世界上所有能看见的语言。
2.5 MySQL的字符集实现
- utf8:最长使用3个字节,支持到了Unicode中的
基本多文本平面
(U 0000至U FFFF),包含控制符
、拉丁文
、中
、日
、韩
等绝大多数国际字符;不包括emoji
和不常用的汉字,如墅
等需要四个字节才能编码出来的字符。 - utf8mb4:最长使用4个字节,会多消耗一个字节的空间但是兼容性更好,并且包括emoji表情的uinicode编码范围
2.6 线上报错原因分析
开发时增加新字段的语句:
ADD COLUMN `wechat_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '微信姓名'
在设计微信昵称
字段时明明设置了utf8mb4
的字符集,但线上却还是报错了,这是为什么呢?
通过查询线上默认字符集、与运维哥哥沟通后找到了答案:
- 第一,线上数据库默认
utf8
字符集 - 第二,在执行SQL语句时会进行
sql审核
,在sql审核时不允许由开发人员主动指定字符集 - 第三,当我把SQL语句给同事进行线上执行时,他把后面
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
删掉了!导致数据库使用了默认的utf8
字符集
找出问题后,再与运维哥哥交涉一下,修改此字段线上库字符集后问题就完美解决了!
这里肯定会有朋友问了,那你为什么是COLLATE utf8mb4_unicode_ci
;我就是COLLATE utf8mb4_general_ci
呢?!
3. utf8mb4_unicode_ci 与 utf8mb4_general_ci
utf8mb4_unicode_ci:
utf8_unicode_ci
校对规则仅部分支持Unicode
校对规则算法。一些字符还是不能支持。并且,不能完全支持组合的记号。这主要影响越南和俄罗斯的一些少数民族语言(对我们来说可以满足使用),如:Udmurt 、Tatar、Bashkir和Mari。- 主要的特色是
支持扩展
,即当把一个字母看作与其它字母组合相等时。例如,在德语和一些其它语言中‘ß’等于‘ss’。 - 准确性 - 基于标准的Unicode来排序和比较,能够在各种语言之间精确排序
- 性能 - 在特殊情况下,Unicode排序规则为了能够处理特殊字符的情况,实现了略微复杂的排序算法()
utf8mb4_general_ci:
utf8mb4_general_ci
是一个遗留的校对规则,不支持扩展;只能在字符之间进行逐个比较。- 准确性 - 没有实现Unicode排序规则,在遇到某些特殊语言或字符时,排序结果可能不是所期望的。
- 性能 - 在比较和排序的时候更快。
小总结:
- 准确性 - 大多数情况下,这种特殊字符的排序不一定要那么精确。
Unicode:会把ß
、Œ
当做ss
和OE
来看
general:会把ß
、Œ
当做s
和e
;再入ÀÁÅåāă
各自都与A
相等 - 性能 - 理论上
general
比Unicode
快些
综上所述,general_ci
更快,unicode_ci
更准确。
我个人更加推荐utf8mb4_unicode_ci
,因为相比CPU来说,它可能不足以成为考虑性能的因素,索引设计、SQL设计才是主战场。