MYSQL中的COLLATE(COLLATION)是什么？

#字符集
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

其实是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci。

这里顺便讲个题外话，mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

问题场景1：为什么从Oracle迁移到DB2数据库或者MYSQL数据库，要扩varchar字段长度2倍，GBK: 专门用来解决中文编码，双字节，UTF-8三字节一个中文字符，不扩字段有被截断的可能。

ORACLE端编码集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

DB2端编目集：

[db2inst1@p0-pbd-pbd-db2 ~]$ db2 get db cfg for tkylcdc |grep -i code

Database code page = 1208

Database code set = utf-8

很多COLLATE都带有_ci字样，这是Case Insensitive的缩写，即大小写无关，也就是说”A”和”a”在排序和比较的时候是一视同仁的。selection * from table1 where field1=”a”同样可以把field1为”A”的值选出来。与此同时，对于那些_cs后缀的COLLATE，则是Case Sensitive，即大小写敏感的。

首先utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。而utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以utf8mb4为例，该编码所支持的所有COLLATE如下图所示
图中我们能看到很多国家的语言自己的排序规则。在国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。我们来探究一下这三个的区别：
show collation; 
| utf8mb4_bin              | utf8mb4  |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci       | utf8mb4  | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci     | utf8mb4  | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci       | utf8mb4  | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci      | utf8mb4  | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci     | utf8mb4  | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci        | utf8mb4  | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci      | utf8mb4  | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci       | utf8mb4  | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci       | utf8mb4  | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci       | utf8mb4  | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci         | utf8mb4  | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci        | utf8mb4  | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci    | utf8mb4  | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci        | utf8mb4  | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci      | utf8mb4  | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci         | utf8mb4  | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci       | utf8mb4  | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci     | utf8mb4  | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci     | utf8mb4  | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci       | utf8mb4  | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci       | utf8mb4  | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci      | utf8mb4  | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci   | utf8mb4  | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci    | utf8mb4  | 247 |         | Yes      |       8 |

如下可以看到order by name的排序规则了，如果不加order by name则不会排序。
mysql> select name from Course order by name;
+--------------------+
| name               |
+--------------------+
| 光学               |
| 化学               |
| 原子物理           |
| 声学               |
| 数学               |
| 数据库原理         |
| 数据物理方法       |
| 日语               |
| 物理               |
| 生物               |
| 电动力学           |
| 电路结构           |
| 线性代数           |
| 英文               |
| 西班牙语           |
| 计算机原理         |
| 语文               |
| 通信原理           |
| 量子力学           |
| 高等数据           |
+--------------------+
20 rows in set (0.00 sec)

mysql> select name from Course;
+--------------------+
| name               |
+--------------------+
| 语文               |
| 数学               |
| 物理               |
| 化学               |
| 生物               |
| 英文               |
| 日语               |
| 西班牙语           |
| 线性代数           |
| 高等数据           |
| 数据物理方法       |
| 计算机原理         |
| 数据库原理         |
| 电路结构           |
| 量子力学           |
| 原子物理           |
| 声学               |
| 电动力学           |
| 光学               |
| 通信原理           |
+--------------------+
20 rows in set (0.00 sec)

mysql> select name from Course where name>'物理';
+-----------------+
| name            |
+-----------------+
| 语文            |
| 生物            |
| 英文            |
| 西班牙语        |
| 线性代数        |
| 高等数据        |
| 计算机原理      |
| 电路结构        |
| 量子力学        |
| 电动力学        |
| 通信原理        |
+-----------------+
11 rows in set (0.00 sec)