Skip to content

13. MySQL字符集

MySQL 的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符 集是用来定义 MySQL 存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集 和校对规则是一对多的关系。

1. 校对规则⚓

1.1 查询⚓

查看有哪些字符集：SHOW CHARACTER SET LIKE '%***%';

MariaDB [MYISAM_TEST]> SHOW CHARACTER SET LIKE '%GBK%';
+---------+------------------------+-------------------+--------+
| Charset | Description            | Default collation | Maxlen |
+---------+------------------------+-------------------+--------+
| gbk     | GBK Simplified Chinese | gbk_chinese_ci    |      2 |
+---------+------------------------+-------------------+--------+

查看有哪些字符集的校对规则：SHOW COLLATION LIKE '%***%';

MariaDB [MYISAM_TEST]> SHOW COLLATION LIKE '%utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |

1.2 命名约定⚓

它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、 _cs （大小写敏感） 或_bin （二元， 即比较是基于字符编码的值而与language无关）结束。

MariaDB [MYISAM_TEST]> SELECT CASE WHEN ('SHIHR' COLLATE utf8_bin) = 'shihr' THEN 1 ELSE 0 END;
+------------------------------------------------------------------+
| CASE WHEN ('SHIHR' COLLATE utf8_bin) = 'shihr' THEN 1 ELSE 0 END |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [MYISAM_TEST]> SELECT CASE WHEN ('SHIHR' COLLATE utf8_general_ci) = 'shihr' THEN 1 ELSE 0 END;
+-------------------------------------------------------------------------+
| CASE WHEN ('SHIHR' COLLATE utf8_general_ci) = 'shihr' THEN 1 ELSE 0 END |
+-------------------------------------------------------------------------+
|                                                                       1 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 字符集的设置⚓

MySQL 的字符集和校对规则有 4 个级别的默认设置：服务器级、数据库级、表级和字段级。它们分别在不同的地方设置，作用也不相同。如果只指定了字符集，那么使用默认的校对规则。

2.1 查询设置的字符集和规则⚓

SHOW VARIABLES LIKE 'CHARACTER_SET_%';
SHOW VARIABLES LIKE 'COLLATION_%';

2.2 服务器字符集和校对规则⚓

服务器字符集和校对，在 MySQL 服务启动的时候确定。

可以在 my.cnf 中设置： [mysqld] default-character-set=gbk 或者在启动选项中指定： mysqld --default-character-set=gbk 或者在编译的时候指定： ./configure --with-charset=gbk

2.3 数据库字符集和校对规则⚓

数据库的字符集和校对规则在创建数据库的时候指定， 也可以在创建完数据库后通过 alter database命令进行修改。如果没有指定字符集和校对规则， 则使用服务器字符集和校对规则作为数据库的字符集和校对规则。

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

注意：如果数据库里已经存在数据，那么修改字符集并不能将已有的数据按照新的字符集进行存放。

2.4 表字符集和校对规则⚓

同数据库字符集和校对规则。如果没有指定字符集和校对规则， 使用数据库字符集和校对规则作为表的字符集和校对规则。

2.5 列字符集和校对规则⚓

同表字符集和校对规则。如果没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。

2.6 连接字符集和校对规则⚓

对于客户端和服务器的交互操作，MylSQL 提供了 3 个不同的参数：character_set_client、character_set_connection 和 character_set_results， 分别代表客户端、 连接和返回结果的字符 集， 通常情况下， 这 3 个字符集应该是相同的， 才可以确保用户写入的数据可以正确地读出。

可通过SET NAMES ***;统一设置这三个变量，但这是临时的，重新连接数据库后需要重新执行。

在 my.cnf.d/mysql-clients.cnf 中设置以下语句：

[mysql] default-character-set=gbk

这样服务器启动后，所有连接默认就是使用 GBK 字符集进行连接的，而不需要在程序中再执行 set names 命令。

3. 字符集的修改步骤⚓

已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成。

以下模拟的是将 utf8 字符集的数据库修改成 GBK 字符集的数据库的过程。

3.1 1. 导出表结构⚓
mysqldump -uroot -pabcd1234 --default-character-set=gbk -d blog > createblog.sql
  • --default-character-set=gbk：表示设置以什么字符集连接
  • -d：表示只导出表结构，不导出数据
3.2 2. 手工修改 createblog.sql 中表结构定义中的字符集为新的字符集。⚓
3.3 3. 确保记录不再更新，导出所有记录。⚓
mysqldump -uroot -pabcd1234 --quick --no-create-info --extended-insert --default-character-set=utf8 blog > blogdata.sql
  • --quick：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中 的行而不是检索所有行，并在输出前将它缓存到内存中。
  • --extended-insert：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件 更小，重载文件时可以加速插入。
  • --no-create-info：不写重新创建每个转储表的 CREATE TABLE 语句。
  • --default-character-set=utf8： 按照原有的字符集导出所有数据， 这样导出的文件中， 所有中文都是可见的，不会保存成乱码。
3.4 4. 打开 blogdata.sql，将 SET NAMES utf8 修改成 SET NAMES gbk。⚓
3.5 5. 使用新的字符集创建新的数据库。⚓
CREATE DATABASE blog_back DEFAULT CHARACTER SET gbk;
3.6 6. 创建表，执行 createblog.sql。⚓
mysql -uroot -pabcd1234 blog_back < createblog.sql
3.7 7. 导入数据，执行 data.sql。⚓
mysql -uroot -pabcd1234 blog_back < blogdata.sql

注意：选择目标字符集的时候，要注意最好是源字符集的超集，或者确定比源字符集的字库更大，否则如果目标字符集的字库小于源字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。