MySQL character set & collation

作者: 西柚酱_ | 来源:发表于2019-07-15 10:53 被阅读0次

1. COLLATE是用来做什么的？

1.1 character set & collation定义

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

1.2 MySQL支持的字符集和字符序

A MySQL collation is a set of rules used to compare characters in a particular character set. Each character set in MySQL can have more than one collation, and has, at least, one default collation. Two character sets cannot have the same collation.

比如collate utf8_unicode_ci，其实是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

-- 1. 查看字符集
-- 方法一：
SHOW CHARACTER SET;
SHOW CHARACTER SET WHERE Charset="utf8";
SHOW CHARACTER SET LIKE "utf8%";

-- 方法二：
use information_schema;
select * from CHARACTER_SETS;
-----------------------------------------------------------------------------------------
-- 2. 查看字符序
-- 方法一：
SHOW COLLATION WHERE Charset = 'utf8'; -- 通过Default的值是否为Yes，判断是否默认的字符序。
show collation;   -- 查看到mysql所支持的所有COLLATE，如下图所示
SHOW COLLATION LIKE 'character_set_name%';
SHOW COLLATION LIKE 'latin1%';

-- 方法二：查询 information_schema.COLLATIONS
USE information_schema;
SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";

2. 各种COLLATE的区别

By convention, a collation for a character set begins with the character set name and ends with _ci (case insensitive忽略大小写) _cs (case sensitive区分大小写) or _bin (binary二进制).
Case Insensitive大小写无关，也就是说"A"和"a"在排序和比较的时候是一视同仁的。selection * from table1 where field1="a"同样可以把field1为"A"的值选出来。Case Sensitive，即大小写敏感的。

COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认COLLATE为utf8mb4_general_ci。

ps: 建议使用utf8mb4而非utf8。
mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

图中我们能看到很多国家的语言自己的排序规则。在国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。
三个的区别：
utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。
utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。对于我们开发的国内使用的系统来说，随便选哪个都行。只是对于某些西方国家的字母来说，utf8mb4_unicode_ci会比utf8mb4_general_ci更符合他们的语言习惯一些，general是mysql一个比较老的标准了。例如，德语字母“ß”，在utf8mb4_unicode_ci中是等价于"ss"两个字母的（这是符合德国人习惯的做法），而在utf8mb4_general_ci中，它却和字母“s”等价。不过，这两种编码的那些微小的区别，对于正常的开发来说，很难感知到。本身我们也很少直接用文字字段去排序，退一步说，即使这个字母排错了一两个，真的能给系统带来灾难性后果么？从网上找的各种帖子讨论来说，更多人推荐使用utf8mb4_unicode_ci，但是对于使用了默认值的系统，也并没有非常排斥，并不认为有什么大问题。
结论：推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。
另外需要注意的一点是，从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（ Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê 和 ë是一视同仁的。相关参考链接1，相关参考链接2

3. COLLATE设置级别及其优先级

MySQL设置COLLATE可以在server级、database别、table级、column级、以及SQL指定。

3.1 Server级别character set 和 collation

实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

3.1.1 查看server字符集、字符序

分别对应character_set_server、collation_server两个系统变量

SHOW VARIABLES LIKE "character_set_server";
SHOW VARIABLES LIKE "collation_server";

3.1.2 设置server字符集、字符序

可以在MySQL服务启动时，指定server字符集、字符序。如不指定，默认的字符序分别为latin1、latin1_swedish_ci

-- sets the utf8 character set and utf8_unicode_cs collation for the server via command line:
mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci

除了在命令行参数里指定，也可以在配置文件里指定，如下所示。

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

运行时修改
运行时修改（重启后会失效，如果想要重启后保持不变，需要写进配置文件里）
mysql> SET character_set_server = utf8 ;
编译时指定默认字符集、字符序

cmake . -DDEFAULT_CHARSET=latin1 \
           -DDEFAULT_COLLATION=latin1_german1_ci

3.2 库级别character set 和 collation

3.2.1 查看库级别character set 和 collation

-- 方法一：
SELECT @@character_set_database, @@collation_database;
-- 方法二：
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA WHERE schema_name="test_schema";
-- 方法三：
SHOW CREATE DATABASE test_schema;

3.2.2 设置库级别character set 和 collation

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

3.3 表级别character set 和 collation

3.3.1 查看表级别character set 和 collation

-- 方法一：
SHOW TABLE STATUS FROM test_schema \G;
-- 方法二：
USE test_schema;
SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";
-- 方法三：
SHOW CREATE TABLE test_table;

3.3.1 设置表级别character set 和 collation

CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

明确了charset_name、collation_name，则采用charset_name、collation_name。
只明确了charset_name，但collation_name未明确，则字符集采用charset_name，字符序采用charset_name对应的默认字符序。
只明确了collation_name，但charset_name未明确，则字符序采用collation_name，字符集采用collation_name关联的字符集。
charset_name、collation_name均未明确，则采用数据库的字符集、字符序设置。

3.4 列级别character set 和 collation

3.4.1 查看列级别character set 和 collation

SELECT CHARACTER_SET_NAME, COLLATION_NAME   \ 
FROM information_schema.COLUMNS    \ 
WHERE TABLE_SCHEMA="test_schema"  \
AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";

3.4.2 设置列级别character set 和 collation

-- 在CREATE TABLE中声明列的时候指定
CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……
--------------------------------------------------------------------------------------------------
-- 也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置
SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

如果charset_name、collation_name均明确，则字符集、字符序以charset_name、collation_name为准。
只明确了charset_name，collation_name未明确，则字符集为charset_name，字符序为charset_name的默认字符序。
只明确了collation_name，charset_name未明确，则字符序为collation_name，字符集为collation_name关联的字符集。
charset_name、collation_name均未明确，则以table的字符集、字符序为准。

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

待调研确认
从Charset and Collation Settings Impact on MySQL Performance这篇文章中，写了一些对比

MySQL 5.7 outperforms MySQL 8.0 in latin1 charset;
MySQL 8.0 outperforms MySQL 5.7 by a wide margin if we use utf8mb4 charset;
Be aware that utf8mb4  is now default MySQL 8.0, while MySQL 5.7 has latin1 by default;
When running comparison between MySQL 8.0 vs MySQL 5.7 be aware what charset you are using, as it may affect the comparison a lot.
--------------------------------------------------------------------------------------------
MySQL 5.7 latin1 charset vs utf8mb4?

参考：http://www.mysqltutorial.org/mysql-collation/
https://juejin.im/post/5bfe5cc36fb9a04a082161c2
https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html