名词解释
一个例子
CREATE TABLE my_table (
my_column VARCHAR(100)
) CHARACTER SET utf8mb4（字符集） COLLATE utf8mb4_bin（校对集）;
字符集
字符集决定了可以使用哪些字符，并且每个字符在数据库中如何存储。例如，UTF-8是一种常见的字符集，它可以存储从任一Unicode版本中任意字符的数字编码。
ascii：英文字符的ASCII字符集。所有字符都是非控制字符，并且使用一个字节。
utf8：最多三个字节的宽度，它是UTF-8编码的Unicode字符集，它包含世界上几乎所有的字符。
latin1：也被称为ISO-8859-1，它是默认的字符集。适合存储英语和其他西欧语言。
utf8mb4：是utf8的超集，最多需要四个字节，包含Emoji表情等。
gbk: 用于简体中文字符。
big5: 用于繁体中文字符。
ucs2: Universal字符集，用于Unicode。
校对集
校对集决定了如何比较和排序字符串。校对不仅会按照无视大小写或区分大小写的方式比较两个字符串，而且还会考虑字符的特定版本和重音符号。例如，校对集latin1_swedish_ci是不区分大小写的，'A'等于'a'，而latin1_swedish_cs则是区分大小写的，'A'不等于'a'。
在MySQL中，校对名称通常由三部分组成，并由下划线 "_" 分隔。以下是每部分的解释：
字符集名：这是校对对应的字符集。比如在latin1_swedish_ci中，latin1就是字符集名。
语言或规则：这部分通常代表特定的语言或排序规则。在latin1_swedish_ci中，swedish就是这一部分，它代表这个校对是按照瑞典语的排序规则进行排序。
校对规则类型：这部分告诉我们这个校对是否区分大小写和标点符号。在latin1_swedish_ci中，ci表示case-insensitive（不区分大小写）。其他可能的值有cs表示case-sensitive（区分大小写），还有bin表示binary（按二进制进行字节比较）。
因此，校对名称latin1_swedish_ci可以理解为：使用latin1字符集，按瑞典语的排序规则，并且不区分大小写。
常见规则
MySQL的校对名称的第二部分和第三部分一般为以下类型：
第二部分（语言）的常见类型有：
-
general：表示该校对使用最普遍的排序规则。
-
chinese：并不代表中文排序，是历史上的兼容冗余。
-
japanese
-
swedish
-
…等等。
第三部分（规则类型）常见的包括：
-
ci: 表示case-insensitive，不区分大小写
-
cs: 表示case-sensitive，区分大小写
-
bin: 二进制，这是一个大小写敏感的比较，按照二进制代码点值进行比较（包括但不限于大小写、全角/半角符号、重音符号等差异）。
规则比较
- _ci vs _cs: 大小写敏感与否(MySQL8.0以上版本有效，5.7中，_cs不支持）
// 使用_ci
SELECT 'A' COLLATE utf8mb4_general_ci = 'a' COLLATE utf8mb4_general_ci;
// 返回1，表示相等，大小写不敏感
// 使用_cs
SELECT 'A' COLLATE utf8mb4_general_cs = 'a' COLLATE utf8mb4_general_cs;
// 返回0，表示不等，大小写敏感
- _cs vs _bin: 代码点值比较(MySQL8.0以上版本有效，5.7中，_cs不支持）
举例：字符 'Å'（字母A上带一个环，用于丹麦、瑞典和挪威语），它既可以用一个字符的形式表示（预编码形式，UTF-8编码为C3 85），也可以用两个字符表示（先是一个普通的字母A，然后是一个环形符号，UTF-8编码为41 CC 8A）。
// 使用_cs
SELECT 'Å' COLLATE utf8mb4_unicode_cs = 'Å' COLLATE utf8mb4_unicode_cs;
// 返回 1， 代表相等
// 使用_bin
SELECT 'Å' COLLATE utf8mb4_bin = 'Å' COLLATE utf8mb4_bin;
// 返回 0， 代表不等
希望区分字符串Diff，如何做
ALTER DATABASE your_database_name COLLATE utf8mb4_bin;
上述语句仅对增量表生效，存量表修改参考2,3，4
CREATE TABLE my_table (
my_column VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
CREATE TABLE my_table (
my_column VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);
SELECT * FROM my_table WHERE my_column COLLATE utf8mb4_bin = 'SomeText';
查询自己版本
// 查询
SELECT VERSION();
// 返回
VERSION() 5.7.23-23-log
// 查询
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 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European| latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| ... | ... | ... | ... |
+----------+----------------------------+-----------------------------+--------+
// 查询
SHOW SHOW COLLATION;;
// 返回
+-----------------------+----------+------+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-----------------------+----------+------+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r | 74 | | Yes | 1 |
| latin1_german1_ci | latin1 | 5 | | Yes | 1 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| ... | ... | ... | ... | ... | ... |
+-----------------------+----------+------+---------+----------+---------+
使用建议
读/容量
对于MySQL来说，使用 _ci（大小写不敏感）和 _cs 或 _bin（大小写敏感）校对规则本身不会对查询或插入等操作带来额外负担。MySQL的查询和插入操作的性能主要受到如查询优化、数据和索引结构、服务器硬件条件等因素的影响。
写：唯一键冲突
字段上定义唯一键（UNIQUE）约束时，大小写的变化会被视为相同的值，并可能触发唯一键冲突。例如，假设你有一个以 utf8mb4_general_ci 校对规则定义的字段，并且该字段有一个唯一键约束。如果你试图在这个字段上插入 Apple 和 apple，那么第二个插入会因为违反唯一键约束而失败，因为在这个校对规则下，这两个字符串被认为是一样的。
如果你想让 Apple 和 apple 被认为是两条不同的记录，你可以使用一个大小写敏感的校对规则，比如 utf8mb4_bin 或 utf8mb4_general_cs 等。
最佳实践：所见即所得
新建表：加入校对集限制
CREATE TABLE my_table (
my_column VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
存量表：（会触发表的reindex，等同于新建索引)
-- 修改整个表的校对规则
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-- 只修改某一列的校对规则
ALTER TABLE your_table_name CHANGE column_name column_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
