詳解MYSQL中COLLATE的作用及各種COLLATE區別

MYSQL中的COLLATE是什麼？

在mysql中執行show create table 指令，可以看到一張表的建表語句，example如下：

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

大部分欄位我們都能看懂，但是今天要討論的是COLLATE關鍵字。這個值後面對應的utf8_unicode_ci是什麼意思呢？面試的時候用這題目考一考DBA，應該可以難倒一大部分人。

COLLATE是用來做什麼的？

使用phpmyadmin的開發可能會非常眼熟，因為其中的中文表頭已經給出了答案：

phpmyadmin截圖

所謂utf8_unicode_ci，其實是用來排序的規則。對於mysql中那些字元類型的列，如VARCHAR，CHAR，TEXT類型的列，都需要有一個COLLATE類型來告知mysql如何對該列進行排序和比較。簡言之，COLLATE會影響到ORDER BY語句的順序，會影響到WHERE條件中大於小於號篩選出來的結果，會影響**DISTINCT**、**GROUP BY**、**HAVING**語句的查詢結果。另外，mysql建索引的時候，如果索引列是字元類型，也會影響索引創建，只不過這種影響我們感知不到。總之，凡是涉及字元類型比較或排序的地方，都會和COLLATE有關。

各種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才行。

很多COLLATE都帶有_ci字樣，這是Case Insensitive的縮寫，即大小寫無關，也就是說"A"和"a"在排序和比較的時候是一視同仁的。 selection * from table1 where field1="a"同樣可以把field1為"A"的值選出來。同時，對於那些_cs後綴的COLLATE，則是Case Sensitive，即大小寫敏感的。

在mysql中使用show collat​​ion指令可以查看到mysql所支援的所有COLLATE。以utf8mb4為例，該編碼所支援的所有COLLATE如下圖所示。

mysql中和utf8mb4相關的所​​有COLLATE

圖中我們能看到很多國家的語言自己的排序規則。在國內比較常用的是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 Collat​​ion Algorithm version），ai表示accent insensitive（發音無關），例如e, è, é, ê 和 ë是一視同仁的。相關參考連結1，相關參考連結2

COLLATE設定等級及其優先權

#

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

库级别设置COLLATE的语句如下：

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。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

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

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如

CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

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

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

推荐学习：《mysql视频教程》

以上是詳解MYSQL中COLLATE的作用及各種COLLATE區別的詳細內容。更多資訊請關注PHP中文網其他相關文章！