【MySQL】设置字符集与排序规则

【MySQL】字符集与排序规则 介绍了 MySQL字符集与排序规则的相关定义、查询方式及命名特点。

本文紧接上一篇文章，讲述如何根据需要设置字符集与其排序规则。

注： 排序规则也称字符集校验规则

引

MySQL 支持字符集与比较规则（字符集校验规则）的设置。 MySQL 共提供了四个级别来设置字符集与比较规则（字符集校验规则），分别是： MySQL服务级别 、 库级别 、 表级别 、 字段级别 。

设置

服务级

服务级指数据库服务级，对其设置字符集与排序规则（字符集校验规则）分为服务启动前和服务启动后两种情况。

除了服务级以外，其他级别的设置与查看只能在服务启动后进行。

启动前

可以在 my.ini 配置文件中配置与查看数据库服务的字符集与排序规则（字符集校验规则）

[server]
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci

其中 character_set_server 是字符集配置项，collation_server 是排序规则配置项。

启动后

MySQL 提供了 SQL 来查看数据库的字符集与排序规则

# 查看数据库服务端字符集
mysql>  show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.06 sec)

# 查看数据库服务端排序规则（字符集校验规则）
mysql> show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_general_ci |
+------------------+--------------------+
1 row in set (0.05 sec)

# 设置数据库服务端字符集为 utfmb4
mysql> set character_set_server = 'utf8mb4';
Query OK, 0 rows affected (0.00 sec)

# 设置数据库服务端字符集排序规则（字符集校验规则）为 utf8mb4_general_cs
mysql> set collation_server = 'utf8mb4_general_cs';
Query OK, 0 rows affected (0.00 sec)

数据库级别

该级别主要用于对数据库设置字符集与排序规则（字符集校验规则）。不同的数据库可以设置不同的字符集与排序规则（字符集校验规则）。

• 创建时设置

数据库的字符集与排序规则可以在创建时设置。若不设置，则默认使用数据库服务配置的字符集与排序规则。以下是创建语句

create database db_demo character set utf8mb4 collate utf8mb4_bin;

• 查看

查看数据库字符集与排序规则需要先使用 use <database_name>; 切换到数据库模式下。SQL 如下

mysql> create database db_demo character set utf8mb4 collate utf8mb4_bin;
Query OK, 1 row affected (0.09 sec)

mysql> use db_demo; 
Database changed
mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.03 sec)

mysql> show variables like 'collation_database';
+--------------------+-------------+
| Variable_name      | Value       |
+--------------------+-------------+
| collation_database | utf8mb4_bin |
+--------------------+-------------+
1 row in set (0.00 sec)

• 修改

可以使用SQL修改数据库的字符集与排序规则

# 只修改字符集，当字符集修改后，该数据库的排序规则会更改为指定字符集的默认排序规则
alter database <database_name> character set = '<charset_name>' ;
# 同时修改字符集与排序规则
alter database <database_name> character set = '<chartset_name>' collate = '<collate_name>';
# 只修改排序规则
alter database <database_name> character collate = '<collate_name>'; 

需要注意，后两种指定排序规则时，需与字符集匹配，负责会致使设置失败。

以下时设置示例

# 1、只更改字符集
mysql> alter database db_demo character set = 'utf8';
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show variables like 'collation_database';  # 其排序规则变为utf8的默认排序规则
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.01 sec)

mysql> show variables like 'character_set_database'; 
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb3 |
+------------------------+---------+

# 2、同时设置字符集及排序规则
mysql> alter database db_demo character set = 'utf8mb4' collate = 'utf8mb4_bin';
Query OK, 1 row affected (0.04 sec)

mysql> 
mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_database';
+--------------------+-------------+
| Variable_name      | Value       |
+--------------------+-------------+
| collation_database | utf8mb4_bin |
+--------------------+-------------+
1 row in set (0.00 sec)

# 设置时字符集与排序规则不匹配，致使设置失败
mysql> alter database db_demo character set = 'utf8mb4' collate = 'utf8mb4_binghhg';
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_binghhg'
mysql> 

表级别

可以在创建时指定字符集合排序规则，不指定的话，用数据库的字符集和排序规则，也可以修改字符集和排序规则。

• 创建

create table <table_name> (<字段列表>) character set <charset_name> collate <collate_name>;

• 查看

show create table <table_name>;

• 修改

alter table <table_name> character set <charset_name> collate <collate_name>;

示例：

# 创建表tb_demo,并指定字符集与排序规则
mysql> create table tb_demo (c_demo varchar(32)) character set utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.09 sec)

# 查看表 tb_demo 字符集与排序规则
mysql> show create table tb_demo;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------+
| tb_demo | CREATE TABLE `tb_demo` (
  `c_demo` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

# 更改表 tb_demo 的字符集
mysql> alter table tb_demo character set gb2312 collate gb2312_chinese_ci;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_demo;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                  |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| tb_demo | CREATE TABLE `tb_demo` (
  `c_demo` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出，仅仅是表的字符集还有排序规则变了，对于已有字段，并没有改变编码和排序规则。

列级别

MySQL 可以在创建表的时候，指定不同的列有不同的字符集和排序规则，也可以修改列的字符集和排序规则。

字段没有指定字符集时，默认使用表的字符集与排序规则。

# 创建表时指定字段的字符集与排序规则
mysql> create table tb_table (name varchar(32) character set gb2312 collate gb2312_chinese_ci) character set utf8mb4 collate utf8mb4_bin; 
Query OK, 0 rows affected (0.03 sec)

# 查看
mysql> show create table tb_table;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_table  | CREATE TABLE `tb_table` (
  `name` varchar(32) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

# 修改字段的字符集与排序策略
mysql> alter table tb_table modify column name  varchar(32) CHARACTER SET utf16 COLLATE utf16_general_ci;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_table;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_table  | CREATE TABLE `tb_table` (
  `name` varchar(32) CHARACTER SET utf16 COLLATE utf16_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

注意，可以只修改字符集或只修改排序规则

-- 只修改字符集
alter table tb_table modify column name  varchar(32) CHARACTER SET utf16 ;
-- 只修改排序规则
alter table tb_table modify column name  varchar(32) COLLATE utf16_general_ci;

各级别字符集与排序规则之间的关系

当四级字符集与排序规则都有设置时，各级之间存在优先级关系，其优先级由大到小依次为： 字段级别、表级别、库级别、MySQL服务级别。

字符集与排序规则是作用到数据表中的字符串字段的，当保存字符串数据时，数据库会一次检查字段级别、表级别、库级别、MySQL服务级别的字符集与排序规则设置，从前往后第一个查到的字符集与排序规则将对该字段的数据保存生效。

注意
尽管MySQL 支持众多字符集与排序规则，在使用 MySQL 时，确保数据库、表和列的字符集设置一致，以避免潜在的字符转换问题。