目录
一、字符集与字符序的概述
在数据的存储上,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 的字符集、字符序。