【MySQL】之字符集与字符序

王廷云的博客

已于 2022-03-23 17:16:48 修改

阅读量1.1k

点赞数 1

分类专栏： MySQL 文章标签： mysql 字符集字符序

于 2020-04-26 18:18:39 首次发布

本文链接：https://blog.csdn.net/aiwangtingyun/article/details/105584231

版权

MySQL 专栏收录该内容

27 篇文章 3 订阅

订阅专栏

一、字符集与字符序的概述

在数据的存储上，MySQL 提供了不同的字符集支持，而在数据的对比操作上，则提供了不同的字符序支持。MySQL 提供了不同级别的设置，包括 server 级、database 级、table 级、column 级，可以提供非常精准的设置。

1、字符集与字符序的定义：

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

举个例子：

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

字符的比较方式为：

不同字符，比如 A、B，或者 a、b，最直观的比较方式是采用它们的编码，比如因为 0 < 1，所以 A < B
相同字符不同大小写，比如 A、a，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说 A == a

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

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

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

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

2、查看字符集相关 SQL 语句：

1）查看 MySQL 支持的全部字符集

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |

2）查看特定的某个字符集

mysql> SHOW CHARACTER SET WHERE `Charset` = 'utf8';
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci   |      3 |
+---------+---------------+-------------------+--------+

3）查看某个范围的字符集

mysql> SHOW CHARACTER SET WHERE `Charset` LIKE 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

4）实际上，SHOW CHARACTER SET 命令查看的是 information_schema 数据库下的 CHARACTER_SETS 表，所以我们也可以直接查询该表：

mysql> USE information_schema;
mysql> SELECT * FROM CHARACTER_SETS;
mysql> SELECT * FROM CHARACTER_SETS WHERE `CHARACTER_SET_NAME` = 'utf8';
mysql> SELECT * FROM CHARACTER_SETS WHERE `CHARACTER_SET_NAME` LIKE 'utf8%';

3、查看字符序的相关 SQL 语句：

1）查看 MySQL 支持的全部字符序：

mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |

2）根据字符序过滤：

mysql> SHOW COLLATION WHERE `Collation` LIKE 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |

3）根据字符集过滤：

mysql> SHOW COLLATION WHERE `Charset` = 'utf8mb4';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |

4）实际上，SHOW COLLATION 命令查看的是 information_schema 数据库下的 COLLATIONS 表，所以我们也可以直接查询该表：

mysql> USE information_schema;
mysql> SELECT * FROM `COLLATIONS`;	
mysql> SELECT * FROM `COLLATIONS` WHERE `COLLATION_NAME` LIKE 'utf8mb4%';	
mysql> SELECT * FROM `COLLATIONS` WHERE `CHARACTER_SET_NAME` = 'utf8mb4';

字符序的命名规范： 字符序的命名，以其对应的字符集作为前缀，比如字符序 utf8_general_ci，表明它是字符集 utf8 的字符序。

二、`Server` 字符集与字符序

用途： 当创建数据库但没有指定字符集、字符序时，server 字符集、 server 字符序就会作为该数据库的默认字符集和字符序。

Server 字符集与字符序的指定方式： MySQL服务启动时，可通过命令行参数指定，也可以通过配置文件的变量指定；
Server 的默认字符集、字符序： 在MySQL编译的时候，通过编译参数指定；

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

1、查看 server 字符集、字符序

Server 字符集对应 character_set_server 系统变量：

mysql> SHOW VARIABLES LIKE "character_set_server";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+

Server 字符序对应 collation_server 系统变量：

mysql> SHOW VARIABLES LIKE "collation_server";
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| collation_server | utf8_unicode_ci |
+------------------+-----------------+

2、启动服务时指定 Server 字符集、字符序

mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

如果不指定则会有默认的 Server 字符集和字符序，视 MySQL 的版本而定。

3、在配置文件指定 Server 字符集、字符序

编辑 my.cnf 配置文件：

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

编辑完后需要重新启动 MySQL 服务。

4、运行时修改 Server 字符集、字符序

mysql> SET character_set_server = utf8mb4;
mysql> SET collation_server = utf8mb4_unicode_ci;

重启后会失效，如果想要重启后保持不变，需要写进配置文件里。

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

cmake . -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci

三、`Database` 字符集与字符序

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

1、设置数据库的字符集、字符序

在创建、修改数据库的时候，通过 CHARACTER SET、COLLATE 指定数据库的字符集、字符序，并且两者都是可选，即可以同时设置两个，也可以单独设置字符集或单独设置字符序，也可以两个都不设置。

1）创建数据库时指定字符集、字符序的语法格式：

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

[] 表示可选的意思；
CHARACTER SET 可以换成其对应的缩写 CHARSET。

示例如下：

CREATE DATABASE demo_db CHARACTER SET utf8 COLLATE utf8_unicode_ci;

2）修改数据库的字符集、字符序的语法格式：

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

示例如下：

ALTER DATABASE demo_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2、查看数据库的字符集、字符序

1）通过查看创建数据库的语句，来查看字符集、字符序：

mysql> SHOW CREATE DATABASE demo_db;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| demo_db  | CREATE DATABASE `demo_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+------------------------------------------------------------------------------------------------+

/*!40100 */ 之间的就是数据库创建时的字符集和字符序。

2）通过变量 character_set_database、collation_database 查看：

mysql> USE demo_db;
Database changed

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_unicode_ci      |
+--------------------------+----------------------+

3）通过查看 information_schema 数据库下的 SCHEMATA 表：

mysql> SELECT `DEFAULT_CHARACTER_SET_NAME`, `DEFAULT_COLLATION_NAME` 
    -> FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` = 'demo_db';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_unicode_ci     |
+----------------------------+------------------------+

3、Database 字符集、字符序的生效顺序

创建数据库时：

如果指定了字符集、字符序，则使用对应的字符集、字符序；
如果只设置了字符集但没有设置字符序，则字符集使用指定的字符集，而字符序使用字符集对应的默认字符序；
如果只设置了字符序但没有设置字符集，则字符序使用指定字符序，而字符集使用字符序关联的字符集；
如果没有指定字符集或字符序，则使用 character_set_server、collation_server 的字符集、字符序。

四、`Table` 字符集与字符序

用途： 指定数据表的字符集和字符序。两者都是可选的，可以同时指定字符集和字符序，也可以单独设置其他一个。

1、设置数据表的字符集、字符序

1）创建表的时候指定字符集、字符序的语法如下：

CREATE TABLE tbl_name (
	column_list
)[[DEFAULT] CHARSET=charset_name] [COLLATE=collation_name]];

示例如下：

CREATE TABLE `demo_db`.`test` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2）修改表的字符集、字符序的语法如下：

ALTER TABLE tbl_name
	[[DEFAULT] CHARACTER SET charset_name]
	[COLLATE collation_name];

示例如下：

ALTER TABLE test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2、查看数据表的字符集、字符序

1）通过查看表的创建语句查看表的字符集、字符序：

mysql> SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------+
| Table | Create Table                                                  |
+-------+---------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci      |
+-------+---------------------------------------------------------------+

2）通过 SHOW TABLE STATUS 查看：

SHOW TABLE STATUS FROM `demo_db`;

3）通过查看 information_schema 数据库下的 SCHEMATA 表

mysql> SELECT `TABLE_COLLATION` FROM `information_schema`.`TABLES` WHERE `TABLE_NAME` = 'test';
+--------------------+
| TABLE_COLLATION    |
+--------------------+
| utf8mb4_unicode_ci |
+--------------------+

3、Table 字符集、字符序的生效顺序

创建表的时候：

如果明确设置字符集、字符序，则使用对应的字符集、字符序；
如果只设置了字符集但没有设置字符序，则字符集使用指定的字符集，而字符序使用字符集对应的默认字符序；
如果只设置了字符序但没有设置字符集，则字符序使用指定字符序，而字符集使用字符序关联的字符集；
如果字符集、字符序都没有设置，则使用数据库的字符集、字符序；

五、`Column` 字符集与字符序

除了可以给数据库和数据表设置字符集、字符序之外，还可以单独为表的字符类型字段（CHAR、VARCHAR、TEXT等）指定使用的字符集、字符序。

1）字段指定字符集、字符序语法如下：

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

2）新增和修改字段时指定字符集、字符序：

-- 添加字段
ALTER TABLE `test` ADD COLUMN `name` VARCHAR(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '';

-- 修改字段
ALTER TABLE `test` MODIFY COLUMN `name` VARCHAR(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '';

3）查看字段的字符集、字符序：

mysql> SELECT `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `information_schema`.`COLUMNS`
    -> WHERE `TABLE_NAME` = 'test' AND `COLUMN_NAME` = 'name';
+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------------+--------------------+
| utf8mb4            | utf8mb4_general_ci |
+--------------------+--------------------+

或者查看表字段结构：

SHOW FULL COLUMNS FROM table_name;

4）Column 字符集、字符序的生效顺序

如果字段指定了字符集、字符序，则使用对应的字符集、字符序；
如果只设置了字符集但没有设置字符序，则字符集使用指定的字符集，而字符序使用字符集对应的默认字符序；
如果只设置了字符序但没有设置字符集，则字符序使用指定字符序，而字符集使用字符序关联的字符集；
如果两者都没有设置，则使用 Table 的字符集、字符序。

王廷云的博客

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
【MySQL】之字符集与字符序

目录一、字符集与字符序的概述二、Server 字符集与字符序三、Database 字符集与字符序四、Table 字符集与字符序五、Column 字符集与字符序六、字符集与字符序的选择一、字符集与字符序的概述在数据的存储上，MySQL 提供了不同的字符集支持，而在数据的对比操作上，则提供了不同的字符序支持。MySQL 提供了不同级别的设置，包括 server 级、database 级、table 级、column 级，可以提供非常精准的设置。1、字符集与字符序的定义：字符集（c
复制链接

扫一扫