MySQL 中 字符集 和 排序规则
字符集
MySQL 提供了多种字符集和排序规则选择，其中字符集设置和数据存储以及客户端与 MySQL 实例的交互相关，排序规则和字符串的对比规则相关
(1). 字符集的设置可以在 MySQL 实例、数据库、表、列四个级别
(2). MySQL 设置字符集支持在 InnoDB, MyISAM, Memory 三个存储引擎
(3). 查看当前 MySQL 支持的字符集的方式有两种，一种是通过查看 information_schema.character_sets 系统表，一种是通过命令【 show character set; 】查看。
1
2
3
4
5
6
7
8
9
10
11
| mysql> select * from character_sets;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| ascii | ascii_general_ci | US ASCII | 1 |
| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| ascii | US ASCII | ascii_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
|
常见的MySQL字符集主要有以下四种：
1
2
3
4
5
6
| 字符集 长度 说明
GBK 2 支持中文，但是不是国际通用字符集
UTF-8 3 支持中英文混合场景，是国际通用字符集
latin1 1 MySQL默认字符集
utf8mb4 4 完全兼容UTF-8，用四个字节存储更多的字符
|
• 如果系统开发面向国外企业，需要处理不同国家、不同语言，则应该选择utf-8或者utf8mb4。
• 如果只需要支持中文，没有国外业务，则为了性能考虑，可以采用GBK。
默认服务器字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
|
名词解释：
character_set_client：客户端请求数据的字符集
character_set_connection：客户机/服务器连接的字符集
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的
character_set_results：结果集，返回给客户端的字符集
character_set_server：数据库服务器的默认字符集
character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。
排序规则
每个指定的字符集都会有一个或多个支持的排序规则，可以通过两种方式查看，一种是查看 information_schema.collations 表，另一种是通过【show collation】命令查看
(1). 查看 utf8mb4 字符集对应的排序规则有哪些。
1
2
3
4
5
6
7
8
9
10
11
12
13
| 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 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)
mysql>
|
(2).剖析排序规则
排序规则的命令通常是以对应的字符集的名字为开头，并以自己的特定属性结尾，比如排序规则 utf8_general_ci 和 latin1_swedish_ci 就分别是对应 utf8 和 latin1 字符集的排序规则
• 当排序规则特指某种语言时，则中间的部分就为这种语言的名字，比如 utf8_turkish_ci 和 utf8_hungarian_ci 就代表 UTF8 字符集中的土耳其语和匈牙利语
• 排序规则名字的结尾字符代表是否大小写敏感，重音敏感以及是否是二进制的.ai重音不敏感，as重音敏感。ci大小写不敏感，cs大小写敏感。bin二进制。
• A. 当仅指定了字符集而没有指定排序规则时，则会使用该字符集的默认排序规则
• B. 当仅指定了排序规则而没有字符集时，则在该排序规则名称上含有的字符集会被使用
• C. 当数据库创建时没有指定这两项，则使用实例级别的字符集和排序规则
设置字符集/字符序
数据库的字符集、字符序
创建数据库：
1
2
3
| CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
|
修改数据库：
1
2
3
4
5
|
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
|
例子：创建数据库test_schema，字符集设置为utf8，此时默认的排序规则为utf8_general_ci。
1
2
3
4
| CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;
CREATE DATABASE `luban` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_bin;
|
查看新创建的数据库的字符集和排序规则
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> SHOW CREATE DATABASE luban;
+----------+---------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------+
| luban | CREATE DATABASE `luban` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name="luban";
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| luban | utf8mb4 | utf8mb4_bin |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)
|
table的字符集、字符序
创建表、修改表的语法如下，可通过CHARACTER SET、COLLATE设置字符集、字符序。
1
2
3
| CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]]
|
1
2
3
| ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]
|
例子如下，指定字符集为utf8，字符序则采用默认的。
1
2
3
4
| CREATE TABLE `test_schema`.`test_table` (
`id` INT NOT NULL COMMENT '',
PRIMARY KEY (`id`) COMMENT '')
DEFAULT CHARACTER SET = utf8;
|
column的字符集、排序
类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，语法如下：
1
2
3
| col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name]
[COLLATE collation_name]
|
例子如下：（创建table类似）
1
| mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;
|
何时设置字符集、字符序
一般来说，可以在三个地方进行配置：
创建数据库的时候进行配置。
mysql server启动的时候进行配置。
从源码编译mysql的时候，通过编译参数进行配置
微信