某日,数据库建表的时候,我开始好奇mysql的charset编码设置参数。uft8和utf8mb4有啥区别呢?


运行环境 Runtime environment

1
2
3
操作系统: Windos10  
navicat: 15.0.2
mysql: 5.7.31

背景

某日,数据库建表的时候,我开始好奇mysql的charset编码设置参数。

uft8和utf8mb4有啥区别,谁更有优势呢?

UTF8和UTF8MB4

在早期MySQL版本中,使用只支持最长三字节的UTF8字符集便可以存放所有Unicode字符。

随着Unicode的完善,Unicode字符集收录的字符数量越来越多,最新版本的UTF8需要使用1到4个字节来存放Unicode字符,而MySQL为保持版本兼容,

依旧使用最多3字节的UTF8字符集,并在MySQL 5.5.3版本引入UTF8MB4字符集来支持4字节的Unicode字符。

测试案例

汉字 ‘𤋮’ 和 ‘ 𤋮 ‘ 是异体字,读音均为xi,但两个字的unicode不同:

1
2
3
4
5
6
𤋮 对应的UNICODE是 \ud850\udeee; 
𤋮 对应的UTF8是 ��
𤋮 对应的HEX编码是 %f0%a4%8b%ae
熙 对应的UNICODE是 \u7199
熙 对应的UTF8是 熙
熙 对应的HEX编码是 %e7%86%99

charset UTF8 测试

创建测试表:

1
2
3
4
5
6
CREATE TABLE `test_table` (
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`test1` VARBINARY(100) DEFAULT NULL,
`test2` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

UTF8字符集测试:

1
2
3
4
5
6
7
8
SET NAMES utf8;

INSERT INTO test_table(test1,test2)
SELECT '熙','熙';
INSERT INTO test_table(test1,test2)
SELECT '𤋮','𤋮';

SELECT * FROM test_table;

执行第一条INSERT有警告信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SET NAMES utf8
> OK
> 时间: 0.004s


INSERT INTO test_table(test1,test2)
SELECT '熙','熙'
> Affected rows: 1
> 时间: 0.012s


INSERT INTO test_table(test1,test2)
SELECT '𤋮','𤋮'
> 1366 - Incorrect string value: '\xF0\xA4\x8B\xAE' for column 'test2' at row 1
> 时间: 0.004s

查询结果:

异体字那一行数据,写都写球不上去, 在UTF8字符集,VARCHAR类型”无法支持“四字节的”𤋮”,但VARBINARY不受字符集影响。

charset UTF8MB4 测试:

UTF8MB4字符集测试:

1
2
3
4
5
6
7
8
9
SET NAMES utf8mb4;

INSERT INTO test_table(test1,test2)
SELECT '𤋮','𤋮';

INSERT INTO test_table(test1,test2)
SELECT '熙','熙';

SELECT * FROM test_table;

执行结果:

执行顺利,大便通畅.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SET NAMES utf8mb4
> OK
> 时间: 0.004s


INSERT INTO test_table(test1,test2)
SELECT '𤋮','𤋮'
> Affected rows: 1
> 时间: 0.019s


INSERT INTO test_table(test1,test2)
SELECT '熙','熙'
> Affected rows: 1
> 时间: 0.017s


SELECT * FROM test_table
> OK
> 时间: 0.004s

查询结果:

显示结果也不尽如人意。不过至少可以把数据存入数据库当中,而不是前者根本存不进去。

总结

UTF8MB4适用场景更加大一些,能确保数据能入库。

但是不管 UTF8MB4还是UTF8 在特殊字符、表情符号、生僻字的话,无论是哪种编码,入库的时候都会存在乱码问题。

这些类型的数据在入库是要注意躲坑了。

要保证数据库正常存储4字节的表情符合生僻字,除将数据库相关表和列设置为UTF8MB4外,

还需要确保操作数据库时使用UTF8MB4,需重点关注以下几个方面:

1、数据库启动配置参数

2、应用与数据库连接配置

3、DBA日常运维操作

DBA操作过程中,使用mysql客户端连接到数据库执行操作,

而mysql客户端可能使用默认UTF8字符集(default-character-set),导出乱码问题。