拨开字符编码的迷雾--MySQL数据库字符编码

时间:2022-09-09 10:36:38

一. MySQL字符集和校对规则

MySQL的字符集是用来定义MySQL存储字符串的方式,校对规则(有的软件叫排序规则)则是用来定义了比较字符串的方式。字符集和校对规则是一对多的关系。每种字符集都有一个默认校对规则。

查看数据库支持的字符集:

# 方法1:
SHOW CHARACTER SET;

# 方法2:
USE information_schema;
SELECT * FROM CHARACTER_SETS;

查看数据库支持的校对规则:

# 方法1:
SHOW COLLATION;

# 方法2:
USE information_schema;
SELECT * FROM collations;

二. MySQL各个级别字符集

MySQL可以对如下字符集进行设置:
- 服务器级字符集(CHARACTER_SET_SERVER)
- 数据库级字符集(CHARACTER_SET_DATABASE)
- 表级字符集
- 字段级字符集
- 连接字符集(CHARACTER_SET_CONNECTION),客户端连接数据库所用的字符集。
- 结果字符集(CHARACTER_SET_RESULTS),存储查询结果(含错误信息)所用的字符集。
- 客户端字符集(CHARACTER_SET_CLIENT),客户端发送给MySQL服务器的查询语句字符集。
- 系统字符集(CHARACTER_SET_SYSTEM),用于存储我们新建的或自带的数据库的表、列的名称,默认是UTF-8

服务器级、数据库级、表级、字段级 这4个字符集设置影响到数据库中存储数据的编码。 这4个级别的字符集继承关系为:服务器级 --> 数据库级 --> 表级 --> 字段级, 从左到右,一级继承一级,和C++、Java中的类的继承类似,如果某一级未显式的指定字符集,那么将继承上一级的字符集设置。

2.1 服务器级别字符集设置

服务器级别的字符集可以从下面几个地方指定,从上到下优先级依次增加:
- 编译MySQL时指定的字符集
- my.cnf配置文件设置character-set-server
- mysqld服务启动命令行中指定字符集

影响数据存储的字符集之间的关系:
拨开字符编码的迷雾--MySQL数据库字符编码

查看当前数据库的字符集设置:

# 方法1:
SHOW VARIABLES LIKE '%character%';

# 方法2:
USE information_schema;
SELECT * FROM GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE '%character%';

3. utf8与utf8mb4区别

UTF-8是多字节编码方案,采用1~4个字节来存储一个字符,但在MySQL设计之初,一个字符最多采用3个字节的就可以存储,所以Mysql的设计者将MySQL中的UTF-8字符集(UTF-8其实不是字符集,是字符编码方案,但在MySQL中一直这么叫)设计成了最大长度只能为3. 如图:
拨开字符编码的迷雾--MySQL数据库字符编码

但随着Unicode字符集的扩张,出现了需要4个字节才能存储的字符,如果这时仍然使用utf-8(指mysql中的3字节utf-8)来存储这些字符就会出现错误,
如执行INSERT INTO member(memberName)VALUES('��'); 报错如下:

Warning Code : 1366
Incorrect string value: '\xF0\xA4\xAD\xA2' for column 'memberName' at row 1

于是MySQL后来新增了utf8mb4字符集,最大长度为4,兼容之前的utf8,但为了之前的数据库不报错,仍然保留了之前的utf8字符集。 所以MySQL中的UTF-8字符集是伪UTF-8,现在需要使用utf8mb4。

4. 完全避免字符乱码

要完全避免字符乱码,最简单也是最好的方法就是将各个字符集保持一致,可以都设置为utf8mb4。Mysql数据存储涉及到的4个字符集的设置可以使用第2节的方法进行设置,客户端字符集、连接字符集,结果字符集关系到数据显示的正确性,可以使用如下语句统一设置为utf8mb4,但这个设置只针对当前连接有效:

set names utf8mb4;

在编程开发中,一般使用MySQL API进行数据连接、查询等操作,可以使用mysql_set_character_set对每个连接进行设置,这个API会将客户端字符集、连接字符集,结果字符集都设置为指定字符集,代码大致如下:

MYSQL* pMysql = NULL;
mysql_real_connect(pMysql, szHost, szUser, szPwd, szDatabase, iPort, NULL, CLIENT_MULTI_STATEMENTS);
mysql_set_character_set(pMysql, "utf8mb4");