字符集设置

一说到字符集，大家就想到了Incorrect string value: ‘\xF0\x9F\xA5\xBA’ for column ‘xxx’,大家一看就知道这是字符集乱码了，那么什么情况下会产生乱码呢？

下面我们说一下线上常见的一个例子，emoji表情存入数据库。

案例：

首先我们创建一个库，以及一张表

mysql> create database my_test;
Query OK, 1 row affected (0.09 sec)

mysql> use my_test;
Database changed

mysql> create table t1 (title varchar(100) not null default ”)engine=innodb;
Query OK, 0 rows affected (0.24 sec)

下面我们写一个php程序，往这个表里面插入一条数据。

$link = mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD);
$content = '"😁"';
$sql = ' insert into my_test.t1 select ' . $content;
$link->query($sql);
echo "err_msg=" . $link->error . ' and err_no=' . $link->errno;

然后发现报错了，错误信息如下：

errmsg=Incorrect string value: '\xF0\x9F\xA5\xBA' for column 'title' at row 1 and errno=1366。

既然报错，我们就看一下为啥报错，首先我们查看一下表的结构。

mysql> show create table t1;
+——-+—————————————————————————————————–+
| Table | Create Table |
+——-+—————————————————————————————————–+
| t1 | CREATE TABLE t1 (
title varchar(100) NOT NULL DEFAULT ”
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+—————————————————————————————————–+
1 row in set (0.02 sec)

title列没有设置字符集，那么就使用table的字符集，也就是utf8。utf8字符集的话，每一个字符使用一到三个字节，我们看报错信息F09FA5BA，这里有4个字节，那难怪会乱码。

这时候我们修改下列的字符集

 mysql> alter table t1 modify column title varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null default ‘ ‘;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

然后再查看一下

mysql> show create table t1;
+——-+——————————————————————————————————————————————————+
| Table | Create Table |
+——-+——————————————————————————————————————————————————+
| t1 | CREATE TABLE t1 (
title varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ”
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+——————————————————————————————————————————————————+
1 row in set (0.00 sec)

再执行一下程序，还是提示：err_msg=Incorrect string value: ‘\xF0\x9F\x98\x81’ for column ‘title’ at row 1 and err_no=1366。

title列都设置成了utf8mb4，怎么还报错了？具体原因还得去分析一下客户端和服务区的通信流程。

通信流程

我们知道在客户端和服务器的通信过程中有三个字符集变量，分别是character_set_client，character_set_connection，character_set_results。

服务器端总是假设客户端是按照character_set_client设置的字符来传输数据和SQL语句的。
当服务器收到客户端的SQL语句时，它先将其转换成字符集character_set_connection。它还使用这个设置来决定如何将数据转换成字符串。
当服务器端返回数据或者错误信息给客户端时，它会将其转换成character_set_result

下图展示了这个过程

那么我们看下这三个的值是啥?

mysql> show variables like ‘%character_set%’;
+————————–+——————————–+
| Variable_name | Value |
+————————–+——————————–+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+————————–+——————————–+
8 rows in set (0.14 sec)

我们可以看到这三个参数的值都是utf8，下面我们来分析一下为什么在修改title列的情况下，还会提示Incorrect string value。

分析过程：character_set_client 是指定客户端发送到服务器的数据的字符集。因此，当 character_set_client 设置为 utf8 时，MySQL 客户端会将发送的字符数据按 utf8（3 字节）来编码和发送。这意味着在这种情况下，客户端在发送包含 4 字节字符（例如 Emoji）的数据时，数据会在客户端这里被截断或编码错误，因为 utf8 无法表示 4 字节字符。

解决方案：将character_set_client、character_set_connection 和 character_set_results都设置为utf8mb4，这将确保：

character_set_client = utf8mb4：客户端发送的数据会以 utf8mb4 编码格式发送，支持 4 字节字符。
character_set_connection = utf8mb4：服务器会以 utf8mb4 编码解释客户端发来的数据，确保字符的完整性。
character_set_results = utf8mb4：服务器返回给客户端的数据也会使用 utf8mb4 编码，以保证包含 4 字节字符的数据能够正确显示。

方案找到了，那么字符集的配置如何修改呢？

如何修改字符集配置

set names
修改配置文件

方法1：set names

你在执行sql前使用set name语句来指定这三个变量的值。请注意，影响的值只是当前会话中才有效。

$link = mysqli_connect("mysql", "root", "root");
$link->query("set names utf8mb4");
$content = '"😁"';
$sql = ' insert into test.t1 select ' . $content;
$link->query($sql);
echo "err_msg=" . $link->error . ' and err_no=' . $link->errno;

这时候，你就会发现没报错了，数据也正常入库了。我们登录另外一个会话看下这三个变量的值。发现结果跟前面的还是一样。每次手动set name不是长久之计，那么就引出了我们的第二种方法。

方法2：修改配置文件

第二种方法，就是修改配置文件，然后重启mysql。那么我们也来试一次。首先我们找到对应的配置文件，然后加上对应的行。

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4

然后重启mysql。接着我们登陆mysql看一下这三个系统变量的值。

mysql> show variables like ‘%character_set%’;
+————————–+——————————–+
| Variable_name | Value |
+————————–+——————————–+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+————————–+——————————–+
8 rows in set (0.01 sec)

再执行一下php，我们不加set names语句。

        $link = mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD);
//        $link->query("set names utf8mb4");
        $content = '"😁"';
        $sql = ' insert into my_test.t1 select ' . $content;
        $link->query($sql);
        echo "err_msg=" . $link->error . ' and err_no=' . $link->errno;

errmsg= and errno=0 发现没有报错信息。到这里，emoji表情的问题就解决了。那么接下来我们就来说说字符集到底怎么回事？

字符集概述

从本质上来说，计算机只能识别二进制代码，因此，不论是计算机程序还是其处理的数据，最终都必须转换成二进制码，计算机才能认识。为了使计算机不仅能做科学计算，也能处理文字信息，人们想出了给每个文字符号编码以便于计算机识别处理的办法，这就是计算机字符集的由来

字符集是符号和编码的集合。排序规则是字符集内字符比较规则的集合。

我们可以用一个简单的例子来说明。

假设我们现在有一个字母表，里面有四个字母，分别是A,B,a,b，我们给每个字母分配一个数字，分别是A = 0, B = 1, a = 2, b = 3。那么字母A就是符号，数字0就是A的编码。那么将这四个字母和它们的编码组合起来就是一个字符集。

假如我们现在想比较A和B，最简单的方法就是看它们的编码。A的编码是0，B的编码是1，因为0<1，因此我们可以说A<B。

我们刚刚所做的是将一个排序规则应用到我们的字符集。这个排序规则是一组规则，不过在我们这个案例中只有一条规则而已，也就是根据编码比较大小。我们称这种简单的排序规则为二进制排序规则。

如果我们想不区分大小写，那么我们至少需要两条规则：

将小写字母a和b等价于A和B
接着比较编码。我们称这种为不区分大小写排序规则。它比二进制排序规则稍微复杂一点。

在真实的生活中，大部分字符集都包含了很多字符，不仅仅是A和B，而是整个字母表以及多字母或数千个字符的东方书写系统，以及许多特殊的符号和标点符号。在真实生活中，大部分排序都有很多规则，而不仅仅是是否区分大小写。

MySQL支持的字符集列表

mysql支持多种字符集，我们可以在INFORMATION_SCHEMA的CHARACTER_SETS表或执行SHOW CHARACTER SET 语句查看全部可用的字符集。

默认情况下SHOW CHARACTER SET 显示全部可用的字符集，我们可以指定LIKE或WHERE子句来查出我们感兴趣的字符集

mysql> show character set like “%utf8%”;
+———+—————+——————–+——–+
| Charset | Description | Default collation | Maxlen |
+———+—————+——————–+——–+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+———+—————+——————–+——–+
2 rows in set (0.00 sec)

排序规则

每个字符集至少包含一个排序规则，大多数都包含多个排序规则。

如果我们想查看某个字符集下面有多少排序规则，我们可以去INFORMATION_SCHEMA.COLLATIONS 表里面查看或者使用SHOW COLLATION语句。

下面我们看一下utf8这个字符集下面有哪些排序规则，注意：Default列为Yes的，表示为默认排序规则。

排序规则的特性：

两个不同的字符集不能拥有相同的排序规则
每一个字符集都有一个默认的排序规则
排序规则的名称开头是以其关联的字符集来决定的，然后后面加上一个或多个后缀来指示其他的排序特性。

当一个字符集包含多个排序规则时，你可能不确定具体哪个排序规则更加适合你的应用。为了避免选中不合适的排序规则，那么可以用代表性数据进行一些比较，以确保给定的排序规则按照你期望的方式对值进行排序。

字符集和排序规则设置的四个级别:

1：server

MySQL服务器有一个server字符集和一个server排序规则。这些选项可以在服务器启动时在命令行上设置，也可以在选项文件中设置，并在运行时更改。

最开始，server字符集和服务器排序规则依赖于启动mysqld时的选项。你可以使用character-set-server 指定字符集，接着你可以再用–collation-server 指定排序规则。

如果你没有指定字符，那么就相当于指定–character-set-server=latin1。如果你只指定了字符集(在我们的例子是latin1)，那么就相当于指定–character-set-server=latin1 –collation-server=latin1_swedish_ci 。

因为latin1_swedish_ci是latin1的默认排序规则。那么也就是说，下面的语句的效果是一样的。

mysqld
mysqld –character-set-server=latin1
mysqld –character-set-server=latin1  –collation-server=latin1_swedish_ci

如果你在创建数据库的时候没有指定字符集和排序规则的话，那么就使用server级别的字符集和排序规则。

character-set-server 和–collation-server 这两个系统变量是可以在运行时修改的。

2：database

每一个数据库都有一个数据库字符集和数据库排序规则。CREATE DATABASE和 ALTER DATABASE 有对应的可选子句可以指定字符集和排序规则。

CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

CHARACTER SET和COLLATE子句可以在同一MySQL服务器上创建具有不同字符集和排序规则的数据库。

例如CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL按照以下方式选择database字符集和数据库排序规：

如果CHARACTER SET charset_name 和COLLATE collation_name都指定了，那么就使用指定的。
如果指定了CHARACTER SET charset_name，没有指定COLLATE collation_name，那么就使用charset_name字符集和其默认的排序规则
如果指定了COLLATE collation_name，没有指定CHARACTER SET charset_name，那么就使用collation_name排序规则和其相关联的的字符集
如果CHARACTER SET charset_name和COLLATE collation_name都没有指定，那么就使用server字符集和server排序规则

默认数据库的字符集和排序规则可以用character_set_database 和 collation_database 系统变量来确定。

要查看指定数据库的字符集和排序规则。请使用以下语句。

mysql> use my_test;
Database changed

mysql> SELECT @@character_set_database, @@collation_database;
+————————–+———————-+
| @@character_set_database | @@collation_database |
+————————–+———————-+
| utf8 | utf8_general_ci |
+————————–+———————-+
1 row in set (0.00 sec)

或者通过这种方式

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ‘my_test’;
+—————————-+————————+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+—————————-+————————+
| utf8 | utf8_general_ci |
+—————————-+————————+
1 row in set (0.01 sec)

3：table

每一个表都有一个数据库字符集和数据库排序规则。CREATE TABLE 和 ALTER TABLE 语句具有用于指定表字符集和排序规则的可选子句：

CREATE TABLE tbl_name (column_list) [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]
ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]

MySQL选择table字符集和数据库排序规则遵循以下行为：

如果CHARACTER SET charset_name 和COLLATE collation_name都指定了，那么就使用指定的。
如果指定了CHARACTER SET charset_name，没有指定COLLATE collation_name，那么就使用charset_name字符集和其默认的排序规则
如果指定了COLLATE collation_name，没有指定CHARACTER SET charset_name，那么就使用collation_name排序规则和其相关联的的字符集。
如果CHARACTER SET charset_name和COLLATE collation_name都没有指定，那么就使用database级字符集和排序规则

4：Column

每一个”character”列(char,varchar,text或其他同义列)有一个列字符集和列排序规则。

CREATE TABLE和ALTER TABLE的列定义语法有可选的子句，用于指定列字符集和排序规则:

 col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

这些子句同样也可以使用到ENUM和SET列上：

col_name {ENUM | SET} (val_list) [CHARACTER SET charset_name] [COLLATE collation_name]

mysql选择列字符集和排序规则遵守以下行为：

如果CHARACTER SET charset_name 和COLLATE collation_name都指定了，那么就使用指定的。
如果指定了CHARACTER SET charset_name，没有指定COLLATE collation_name，那么就使用charset_name字符集和其默认的排序规则
如果指定了COLLATE collation_name，没有指定CHARACTER SET charset_name，那么就使用collation_name排序规则和其相关联的的字符集
如果CHARACTER SET charset_name和COLLATE collation_name都没有指定，那么就使用table字符集和排序规则

如果使用ALTER TABLE将一个列从一个字符集转换为另一个字符集，MySQL会尝试映射数据值，但是如果字符集不兼容，可能会导致数据丢失。

用于连接字符集配置的SQL语句

在连接建立之后，客户端可以在当前会话修改其字符集和排序规则。这些变量都可以通过SET 语句来修改，但是还有两个更方便的语句将与连接有关的字符集系统变量作为一个组来影响：

1：SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]

SET NAMES指示客户端将使用什么字符集向服务器发送SQL语句。因此， SET NAMES ‘cp1251’ 告诉服务器“以后从这个客户端传过来的消息将使用字符集cp1251”。同时它也指定了服务器返回给客户端的结果所用的字符集。

SET NAMES ‘charset_name’ 语句相当于以下三条语句

SET character_set_client = charset_name;       
SET character_set_results = charset_name;   
SET character_set_connection = charset_name;

2：SET CHARACTER SET ‘charset_name’

SET CHARACTER SET 类似于SET NAMES，但将character_set_connection和collation_connection设置为character_set_database和collation_database。

character_set_database和collation_database系统变量表示默认数据库的字符集和排序规则

SET CHARACTER SET charset_name语句相当于以下三个语句:

SET character_set_client = charset_name;   
SET character_set_results = charset_name;      
SET collation_connection = @@collation_database;

设置collation_connection还隐式地将character_set_connection设置为与排序规则关联的字符集(相当于执行set character_set_connection = @@character_set_database)。没有必要显式地设置character_set_connection。

假如column1定义为CHAR(5) CHARACTER SET latin2。如果你没有执行SET NAMES or SET CHARACTER SET语句，然后对于SELECT column1 FROM t，服务器使用客户端在连接时指定的字符集发送回column1的所有值。

另一方面，如果你在发送SELECT语句之前执行了SET NAMES ‘latin1’ 或SET CHARACTER SET ‘latin1’语句，服务器在返回结果之前将latin2的值转换为latin1。对于不在两个字符集中的字符，转换可能是有损的。

提示：

需要记住的是，真正存放数据的是列，所以更高“阶梯”的设置只是指定默认值。一个表的默认字符集设置无法影响存储在这个表中某个列的值。只有当创建列而没有为列指定字符集的时候，如果没有指定字符集，表的默认字符集才有作用。

参考资料：

《深入浅出MySQL: 数据库开发，优化与管理维护》

《高性能MySQL》

https://dev.mysql.com/doc/refman/5.7/en/charset.html

字符集设置

案例：

通信流程

下图展示了这个过程

如何修改字符集配置

方法1：set names

方法2：修改配置文件

字符集概述

MySQL支持的字符集列表

排序规则

排序规则的特性：

字符集和排序规则设置的四个级别:

1：server

2：database

3：table

4：Column

用于连接字符集配置的SQL语句

提示：

参考资料：

相关文章

衡量SQL查询的三个指标

如何删除大表

备份与恢复

数据类型：JSON

事务ID分配时间

发表评论取消回复

案例：

通信流程

下图展示了这个过程

如何修改字符集配置

方法1：set names

方法2：修改配置文件

字符集概述

MySQL支持的字符集列表

排序规则

排序规则的特性：

字符集和排序规则设置的四个级别:

1：server

2：database

3：table

4：Column

用于连接字符集配置的SQL语句

提示：

参考资料：

相关文章

衡量SQL查询的三个指标

如何删除大表

备份与恢复

数据类型：JSON

事务ID分配时间

发表评论 取消回复

发表评论取消回复