拨开字符编码的迷雾--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");
标签：

相关文章