MySQL 字符集和校对——《高性能MySQL》

最新推荐文章于 2022-12-02 16:01:10 发布

crazy-tiger

最新推荐文章于 2022-12-02 16:01:10 发布

阅读量289

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/u013490280/article/details/111163785

版权

MySQL 专栏收录该内容

14 篇文章 2 订阅

订阅专栏

读《高性能MySQL》第三版，笔记。

1、字符集和校对

字符集：是指一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字母。

校对：是指一组用于某个字符集的排序规则。

MySQL 4.1 和之后的版本中，每一类编码字符都有对应的字符集和校对规则。

1.1、MySQL 如何使用字符集

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。

每个校对规则都是针对某个特定的字符集的，和其他的字符集没有关系。

MySQL 的设置可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设置。

创建对象时的默认设置

MySQL 服务器有默认的字符集和校对规则，每个数据库也有自己的默认值，每个表也有自己的默认值。

这是一个逐层继承的默认设置，最终最底层的默认设置将影响你创建的对象。

这些默认值，至上而下地告诉 MySQL 应该使用什么字符集来存储某个列。

在这个 “ 阶梯 ” 的每一层，你都可以指定一个特定的字符集或者让服务器使用它的默认值：

创建数据库的时候，将根据服务器上的 character_set_server 设置类设定该数据库的默认字符集。
创建表的时候，将根据数据库的字符集设置指定这个表的字符集设置。
创建列的时候，将根据表的设置指定列的字符集设置。

需要记住的是，真正存放数据的是列，所以更高 “ 阶梯 ” 的设置只是指定默认值。

一个表的默认字符集设置无法影响存储在这个表中某个列的值。

只有当创建而没有为列指定字符集的时候，如果没有指定字符集，表的默认字符集才有作用。

服务器和客户端通信时的设置

当服务器和客户端通信的时候，它们可能使用不同的字符集。

这时，服务器端将进行必要的翻译转换工作：

服务器端总是假设客户端时按照 character_set_client 设置的字符来传输数据和 SQL 语句的。
当服务器收到客户端的 SQL 语句时，它先将器转换成字符集 character_set_connection 。它还使用这个设置来决定如何将数据转换成字符串。
当服务器端返回数据或者错误信息给客户端时，它会将其转换成 character_set_result 。

根据需要，可以使用 SET NAMES 或者 SET CHARACTER SET 语句来改变上面的设置。不过在服务器上使用这个命令只能改变服务器端的设置。客户端程序和客户端的 API 也需要使用正确的字符集才能避免在通信时出现问题。

MySQL 如何比较两个字符串的大小

如果比较的两个字符串的字符集不同，MySQL 会先将其转成同一个字符集再进行比较。

如果两个字符集不兼容的话，则会抛出错误。MySQL 5.0 和更新的版本经常会做这样的隐式转换，所以这类错误通常是在 MySQL 4.1 中比较常见。

MySQL 还会为每个字符串设置一个 “ 可转换性 ”（coercibility() 函数的返回值）。这个设置决定了值的字符集的优先级，因而会影响 MySQL 做字符集隐式转换后的值。

还可以使用前缀和 COLLATE 子句来指定字符串的字符集或者校对字符集。

mysql> SELECT _utf8 'hello world' COLLATE utf8_bin;
+--------------------------------------+
| _utf8 'hello world' COLLATE utf8_bin |
+--------------------------------------+
| hello world                          |
+--------------------------------------+
1 row in set (0.01 sec)

1.2、选择字符集和校对规则

MySQL 4.1 和之后的版本支持很多的字符集和校对规则，包括支持使用 Unicode 编码的多字节 UTF-8 。

可以使用命令 SHOW CHARACTER SET 和 SHOW COLLATION 来查看 MySQL 支持的字符集和校对规则。

正确的方法是，最好先为服务器（或者数据库）选择一个合理的字符集。然后根据不同的实际情况，让某些列选择合适的字符集。

对于校对规则通常需要考虑的一个问题是，是否以大小写敏感的方式比较字符串，或者是以字符串编码的二进制来比较大小。

前缀	说明
_cs	大小敏感
_ci	大小不敏感
_bin	二进制比较

大小写敏感和二进制校对规则的不同之处在于，二进制校对规则直接使用字符的字节进行比较，而大小写敏感的校对规则在多字节字符集时，如德语，有更复杂的比较规则。

在显式设置字符集的时候，并不是必须同时指定字符集和校对规则的名字。

如果缺失了其中一个或者两个，MySQL 会使用可能的默认值来进行填充。

MySQL 如何选择字符集和校对规则：

用户设置	返回结果的字符集	返回结果的校对规则
同时设置字符集和校对规则	与用户设置相同	与用户设置相同
仅设置字符集	与用户设置相同	与字符集的默认校对规则相同
仅设置校对规则	与校对规则对象的字符集相同	与用户设置相同
都未设置	使用默认值	使用默认值

查询表的字符集和校对规则：

mysql> show full columns from city;
+-------------+----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type     | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| ID          | int(11)  | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| Name        | char(35) | latin1_swedish_ci | NO   |     |         |                | select,insert,update,references |         |
| CountryCode | char(3)  | latin1_swedish_ci | NO   | MUL |         |                | select,insert,update,references |         |
| District    | char(20) | latin1_swedish_ci | NO   |     |         |                | select,insert,update,references |         |
| Population  | int(11)  | NULL              | NO   |     | 0       |                | select,insert,update,references |         |
+-------------+----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.00 sec)