MySql（二）——字符集和比较规则

jiezi

6 年前

MySql（二）——字符集和比较规则
一些重要的字符集

ASCII
共收录 128 个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才 128 个字符，所以可以使用 1 个字节来进行编码

ISO 8859-1
共收录 256 个字符，是在 ASCII 字符集的基础上又扩充了 128 个西欧常用字符 (包括德法两国的字母)，也可以使用 1 个字节来进行编码。这个字符集也有一个别名 latin1

GB2312

收录汉字 6763 个，其他文字符号 682 个。同时这种字符集又兼容 ASCII 字符集
如果该字符在 ASCII 字符集中，则采用 1 字节编码，否则采用 2 字节编码
这种表示一个字符需要的字节数可能不同的编码方式称为：变长编码方式

GBK
在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312

utf8
收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用 1～4 个字节

utf8 只是 Unicode 字符集的一种编码方案，Unicode 字符集可以采用 utf8、utf16、utf32 这几种编码方案，utf8 使用 1～4 个字节编码一个字符，utf16 使用 2 个或 4 个字节编码一个字符，utf32 使用 4 个字节编码一个字符。
MySQL 中支持的字符集和排序规则
MySQL 中的 utf8 和 utf8mb4

utf8mb3：阉割过的 utf8 字符集，只使用 1～3 个字节表示字符

utf8mb4：正宗的 utf8 字符集，使用 1～4 个字节表示字符

在 MySQL 中 utf8 是 utf8mb3 的别名
字符集的查看 SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式]
比较规则的查看 SHOW COLLATION [LIKE 匹配的模式]

后缀
英文释义
描述

_ai
accent insensitive
不区分重音

_as
accent sensitive
区分重音

_ci
case insensitive
不区分大小写

_cs
case sensitive
区分大小写

_bin
binary
以二进制方式比较

字符集和比较规则的应用
1. MySQL 有 4 个级别的字符集和比较规则

服务器级别
数据库级别
表级别
列级别

2. 服务器级别

服务器级别的字符集：SHOW VARIABLES LIKE ‘character_set_server’

服务器级别的比较规则：SHOW VARIABLES LIKE ‘collation_server’

写入配置文件：

[server]
character_set_server=utf8
collation_server=utf8_general_ci
3. 数据库级别

当前数据库的字符集：SHOW VARIABLES LIKE ‘character_set_database’

当前数据库的比较规则：SHOW VARIABLES LIKE ‘collation_database’

创建和修改数据库的时候数据库的字符集和比较规则

CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称 ]
[[DEFAULT] COLLATE 比较规则名称 ];

ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称 ]
[[DEFAULT] COLLATE 比较规则名称 ];
创建数据库不指定字符集和比较规则，则默认使用服务器级别的字符集和比较规则
4. 表级别
CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称 ]
[COLLATE 比较规则名称]]

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称 ]
[COLLATE 比较规则名称]
如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则
5. 列级别
CREATE TABLE 表名 (
列名字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列 …
);

ALTER TABLE 表名 MODIFY 列名字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则
6. 仅修改字符集或仅修改比较规则

只修改字符集，则比较规则将变为修改后的字符集默认的比较规则
只修改比较规则，则字符集将变为修改后的比较规则对应的字符集

7. 各级别字符集和比较规则小结

如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

客户端和服务器通信中的字符集
从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到 3 个系统变量

系统变量
描述

character_set_client
服务器解码请求时使用的字符集

character_set_connection
服务器运行过程中使用的字符集

character_set_results
服务器向客户端返回数据时使用的字符集

服务器认为客户端发送过来的请求是用 character_set_client 编码的
服务器将把得到的结果集使用 character_set_results 编码后发送给客户端

character_set_connection 只是服务器在处理请求时使用的字符集，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求以及结果集中的字符，要不然会出现无法将请求中的字符编码成 character_set_connection 字符集或者无法编码结果集中的字符

SET NAMES 字符集名等价于：

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

如果想写进配置文件：
[client]
default-character-set=utf8