理解 MySQL 字符集级别_云计算

发布时间:2024-07-11 19:36

阅读量:10

以下是以前的一些笔记，汇总一下。

MySQL--迁移到uft8mb4需要考虑的事项

MySQL8.0中utf8mb4的强大：释放多语言数据的全部潜能

MySQL如何使用字符集配置选项

在讨论字符集时，通常会伴随以下一些问题：

·修改 MySQL Server 的字符集是否会影响已有库和表

·修改库的字符集是否会影响已有表

·修改表的字符集是否会影响现有表的列

根据这些问题，可以看到 MySQL 有多个字符集级别（Server、库、表和列），有时我们害怕触碰其中任何一个级别，结果会弄乱了我们的数据。

为了消除这些顾虑，让我们来了解一下每个级别：

Server 级别字符集

变量 character_set_server 定义了 Server 的字符集。它的值将作为任何 CREATE DATABASE 命令的默认字符集。例如：

> SELECT @@character_set_server;+------------------------+| @@character_set_server |+------------------------+| utf8mb4                |+------------------------+1 row in set (0.00 sec)
> CREATE DATABASE myabc;Query OK, 1 row affected (0.01 sec)
> SHOW CREATE DATABASE myabcG*************************** 1. row ***************************Database: myabcCreate Database: CREATE DATABASE `myabc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */1 row in set (0.00 sec)

库级别字符集

如上一节所述，数据库的字符集继承自创建数据库时的 character_set_server 值（除非在新建数据库的时候显式设置）。

不过，还有一个名为 character_set_database 的变量。这个变量与其他变量不同。用户不需要配置这个变量，因为它的值会自动从默认数据库继承。如果没有设置默认数据库，会话将使用 character_set_server 的全局值。该变量已被弃用，修改它会产生警告：

Level: WarningCode: 1681Message: Updating 'character_set_database' is deprecated. It will be made read-only in a future release.1 row in set (0.00 sec)

这个变量（character_set_database）只影响不包含显式字符集的 LOAD DATA 命令。另一方面，数据库的字符集将影响所有未指定字符集而创建的表和存储过程。以上面创建的数据库为例，如果我们创建的表没有指定字符集，那么它将使用 utf8mb4 创建，因为这是数据库的字符集：

创建两个表：

>show create database myabc\G*************************** 1. row ***************************       Database: myabcCreate Database: CREATE DATABASE `myabc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */1 row in set (0.00 sec)
>use myabc;Database changed>create table t1(i int not null);Query OK, 0 rows affected (0.03 sec)
>create table t2(i int not null) default charset=utf8mb3;Query OK, 0 rows affected, 1 warning (0.03 sec)
>show create table t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `i` int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
>show create table t2\G*************************** 1. row ***************************       Table: t2Create Table: CREATE TABLE `t2` (  `i` int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)
>

表级别字符集

正如我们所验证的，如果创建表命令中没有显式字符集或 collation ，表的字符集将从数据库继承。如果没有显式设置字符集，表字符集将用作列的默认字符集。例如

创建带有默认字符集列的表，并将 latin1 设置为其他列的字符集：

>create database myabc;Query OK, 1 row affected (0.01 sec)
>show create database myabc;+----------+---------------------------------------------------------------------------------------------------------------------------------+| Database | Create Database                                                                                                                 |+----------+---------------------------------------------------------------------------------------------------------------------------------+| myabc    | CREATE DATABASE `myabc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |+----------+---------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
>use myabcDatabase changed>create table t1 ( col1 char(10) character set latin1, col2 char(10));Query OK, 0 rows affected (0.04 sec)
>show create table t1;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                         |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `col1` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,  `col2` char(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
>show create table t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `col1` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,  `col2` char(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
>

这里， col2 继承了表的字符集，col1 使用 latin1 字符集。

列级别字符集

正如刚才所演示的，列的字符集继承自表的默认字符集。列的字符集最终会影响存储在该列中的数据的字符集。

总结

·变量 character_set_server 会影响数据库的创建。

·数据库的字符集会影响在该数据库中创建的表。其中：

--变量 character_set_database 取默认数据库（或 character_set_server，如果未连接任何数据库）的值，并影响 LOAD DATA。

--表的字符集会影响在表内创建或添加的列。

·列的字符集控制数据的字符集。

修改字符集

修改 character_set_server、数据库默认字符集或表默认字符集的值不会改变已创建对象的默认字符集，也不会转换任何数据。不过，在修改数据库的默认字符集后，任何新表都将使用新的默认值创建。

列的字符集是最终会影响数据的字符集。修改列并更改其字符集时，会强制 MySQL 转换数据。(注意：转换数据是个危险的操作)

# 修改库默认字符集ALTER DATABASE test DEFAULT CHARACTER SET latin1;
# 修改表默认字符集ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
# 修改列的字符集ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8mb4;
# 修改所有列的字符集ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

客户端字符集

前面的字符集与数据在数据库中的存储方式有关。

还有其他一些变量控制着如何向数据库中发送和从中检索信息。这四个变量是 "character_set_client"、"character_set_connection"、"collation_connection "和 "character_set_results"。前三个变量与连接到数据库的客户端发送的命令的字符集有关，而 "character_set_results "则配置 MySQL 向客户端发送查询结果时使用的字符集。

变量 "character_set_client "控制连接到数据库的客户端发送的命令的字符集。"character_set_connection"和 "collation_connection "控制用于字符串比较的字符集和校对方式。使用 "where column = 'mytext'"查询时，MySQL 将使用连接的 "character_set_connection "和 "collation_connection"来解释字符串 "mytext"。假设表中含有latin1数据：

mysql > show create table latin_table G*************************** 1. row ***************************Table: latin_tableCreate Table: CREATE TABLE `latin_table` (`col1` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

而数据库连接使用的是utf8mb4字符集：

mysql > select @@character_set_connection;+----------------------------+| @@character_set_connection |+----------------------------+| utf8mb4                    |+----------------------------+1 row in set (0.00 sec)mysql > select @@collation_connection;+------------------------+| @@collation_connection |+------------------------+| utf8mb4_0900_ai_ci     |+------------------------+1 row in set (0.00 sec)

下面这样的查询会产生错误，因为字符串 "test "是 utf8mb4 格式，而列 col1 中的数据是以 latin1 格式存储的：

mysql > select concat_ws(';',IF(col1 is not null, 'test', null), col1) from latin_table;ERROR 1270 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE), (utf8mb4_0900_ai_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT) for operation 'concat_ws'

虽然 MySQL 可以执行一些校对转换，但它无法在有 IF 的 concat_ws 中执行。

如果我们将 character_set_connection 更改为 latin1（校对会自动更改为 latin1 校对），我们就能运行相同的查询，而不会引起转换问题：

mysql > set session character_set_connection=latin1;Query OK, 0 rows affected (0.00 sec)
mysql > select @@collation_connection;+------------------------+| @@collation_connection |+------------------------+| latin1_swedish_ci |+------------------------+1 row in set (0.00 sec)
mysql > select concat_ws(';',IF(col1 is not null, 'test', null), col1) from latin_table;+---------------------------------------------------------+| concat_ws(';',IF(col1 is not null, 'test', null), col1) |+---------------------------------------------------------+| test;test |+---------------------------------------------------------+1 row in set (0.01 sec)