本文遵循BY-SA版权协议，转载请附上原文出处链接。

本文作者: 黑伴白

本文链接: http://heibanbai.com.cn/posts/e97433cf/

MySQL字符集字符序

在MySQL的使用过程中，了解字符集、字符序的概念，以及不同设置对数据存储、比较的影响非常重要。

不少同学在日常工作中遇到的“乱码”问题，很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。

下面介绍下如下内容：

• 字符集、字符序的基本概念及联系
• MySQL支持的字符集、字符序设置级，各设置级别之间的联系
• server、database、table、column级字符集、字符序的查看及设置
• 应该何时设置字符集、字符序

字符集、字符序的概念与联系

在数据的存储上，MySQL提供了不同的字符集支持。而在数据的对比操作上，则提供了不同的字符序支持。

MySQL提供了不同级别的设置，包括server级、database级、table级、column级，可以提供非常精准的设置。

什么是字符集、字符序？简单的来说：

• 字符集（character set）：定义了字符以及字符的编码。
• 字符序（collation）：定义了字符的比较规则。

举个例子：

有四个字符：A、B、a、b，这四个字符的编码分别是A = 0, B = 1, a = 2, b = 3。这里的字符 + 编码就构成了字符集（character set）。

如果我们想比较两个字符的大小呢？比如A、B，或者a、b，最直观的比较方式是采用它们的编码，比如因为0 < 1，所以 A < B。

另外，对于A、a，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说 A == a。

这上面定义了两条比较规则，这些比较规则的集合就是collation。

• 同样是大写字符、小写字符，则比较他们的编码大小；
• 如果两个字符为大小写关系，则它们相等。

MySQL支持的字符集、字符序

MySQL支持多种字符集 与 字符序。

• 一个字符集对应至少一种字符序（一般是1对多）。
• 两个不同的字符集不能有相同的字符序。
• 每个字符集都有默认的字符序。

修改字符集

# 修改/etc/my.cnf文件
# 在[client]下添加下述内容
default-character-set=utf8
# 在[mysqld]下添加下述内容
default-character-set=utf8

保存后重启MySQL服务即可

字符集

查看MYSQL所支持的字符集

# 方式一和二可以加上 WHERE或 LIKE限定条件
# 方式一
show charset;
show charset LIKE "utf8%";
# 方式二
SHOW CHARACTER SET;
SHOW CHARACTER SET WHERE Charset="utf8";
# 方式三
use information_schema;
select * from CHARACTER_SETS;

查看字符集

show variables like '%character%'; 

查看库的字符集

mysql> show create database testdb\G

查看表的字符集

show table status from 库名 like  表名;

查看表中所有列的字符集

show full columns from 表名;

创建库时指定字符集

create database 库名 default character set=字符集;

创建表时指定字符集

create table 表名 (属性) default character set = 字符集;

创建字段时指定字符集

CREATE TABLE table_name (id int,name char(10) CHARACTER SET utf8 COLLATE utf8_general_ci);

修改全局字符集

• 建立连接使用的编码

set character_set_connection=utf8; 

• 数据库的编码

set character_set_database=utf8;

• 结果集的编码

set character_set_results=utf8;

• 数据库服务器的编码

set character_set_server=utf8; 
set character_set_system=utf8; 
set collation_connection=utf8; 
set collation_database=utf8;
set collation_server=utf8;

修改库的字符集

alter database 库名 default character set 字符集;

修改表的字符集

alter table 表名 convert to character set 字符集;

修改字段的字符集

alter table 表名 modify 字段名 字段属性 character set gbk；

字符序

查看支持的字符序

# 方式一
SHOW COLLATION WHERE Charset = 'utf8';
# 方式二
USE information_schema;
SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";

查看字符序

show variables like 'collation%';

字符序的命名规范

字符序的命名，以其对应的字符集作为前缀，如下所示。比如字符序 utf8_general_ci，标明它是字符集 utf8的字符序，更多规则可以参考 官方文档。

mysql> use information_schema;
Database changed
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8mb4" limit 3;
+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------------+--------------------+
| utf8mb4            | utf8mb4_general_ci |
| utf8mb4            | utf8mb4_bin        |
| utf8mb4            | utf8mb4_unicode_ci |
+--------------------+--------------------+
3 rows in set (0.00 sec)

server的字符集、字符序

当你创建数据库，且没有指定字符集、字符序时，server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

MySQL服务启动时，可通过命令行参数指定。也可以通过配置文件的变量指定。

server默认字符集、字符序：在MySQL编译的时候，通过编译参数指定。

character_set_server、 collation_server分别对应server字符集、server字符序。

• 在MySQL服务启动时，指定server字符集、字符序。如不指定，默认的字符序分别为 latin1、 latin1_swedish_ci

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

• 单独指定server字符集，此时，server字符序为 latin1的默认字符序 latin1_swedish_ci。

mysqld --character-set-server=latin1

• 配置文件指定

[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8

• 运行时修改

# 运行时修改（重启后会失效，如果想要重启后保持不变，需要写进配置文件里）
mysql> SET character_set_server = utf8 ;

• 编译时指定默认字符集、字符序

# character_set_server、 collation_server的默认值，可以在MySQL编译时，通过编译选项指定：
cmake . -DDEFAULT_CHARSET=latin1 \
-DDEFAULT_COLLATION=latin1_german1_ci

database的字符集、字符序

指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库，可以分别指定不同的字符集/字符序。

• 可以在创建、修改数据库的时候，通过 CHARACTER SET、 COLLATE指定数据库的字符集、排序规则。

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]

例子：创建数据库 test_schema，字符集设置为 utf8，此时默认的排序规则为 utf8_general_ci。

CREATE DATABASE test_schema DEFAULT CHARACTER SET utf8;

• 查看数据库的字符集/字符序，有以下三种方式

1. 查看moia5数据库的字符集、排序规则。（需要切换数据库）

mysql> use moia5;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb3                  | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.00 sec)

2. 也可以通过下面命令查看 test_schema的字符集、数据库（不需要切换默认数据库）

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA WHERE schema_name="moia5";
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| moia5       | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

3. 也可以通过查看创建数据库的语句，来查看字符集

mysql> SHOW CREATE DATABASE moia5;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                   |
+----------+---------------------------------------------------------------------------------------------------+
| moia5    | CREATE DATABASE `moia5` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

database字符集、字符序是怎么确定的

• 创建数据库时，指定了 CHARACTER SET或 COLLATE，则以对应的字符集、排序规则为准。
• 创建数据库时，如果没有指定字符集、排序规则，则以 character_set_server、 collation_server为准。

---

蚂蚁再小也是肉🥩！