詳解MYSQL中COLLATE的作用及各種COLLATE區(qū)別

MYSQL中的COLLATE是什么？

在mysql中執(zhí)行show create table <tablename>指令，可以看到一張表的建表語句，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關(guān)鍵字。這個值后面對應(yīng)的utf8_unicode_ci是什么意思呢？面試的時候用這個題目考一考DBA，應(yīng)該可以難倒一大部分人。

COLLATE是用來做什么的？

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

phpmyadmin截圖

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

各種COLLATE的區(qū)別

COLLATE通常是和數(shù)據(jù)編碼（CHARSET）相關(guān)的，一般來說每種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長度的字符編碼，對于一些需要占據(jù)4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

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

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

mysql中和utf8mb4相關(guān)的所有COLLATE

圖中我們能看到很多國家的語言自己的排序規(guī)則。在國內(nèi)比較常用的是utf8mb4_general_ci（默認）、utf8mb4_unicode_ci、utf8mb4_bin這三個。我們來探究一下這三個的區(qū)別：

首先utf8mb4_bin的比較方法其實就是直接將所有字符看作二進制串，然后從最高位往最低位比對。所以很顯然它是區(qū)分大小寫的。

而utf8mb4_unicode_ci和utf8mb4_general_ci對于中文和英文來說，其實是沒有任何區(qū)別的。對于我們開發(fā)的國內(nèi)使用的系統(tǒng)來說，隨便選哪個都行。只是對于某些西方國家的字母來說，utf8mb4_unicode_ci會比utf8mb4_general_ci更符合他們的語言習(xí)慣一些，general是mysql一個比較老的標準了。例如，德語字母“?”，在utf8mb4_unicode_ci中是等價于"ss"兩個字母的（這是符合德國人習(xí)慣的做法），而在utf8mb4_general_ci中，它卻和字母“s”等價。不過，這兩種編碼的那些微小的區(qū)別，對于正常的開發(fā)來說，很難感知到。本身我們也很少直接用文字字段去排序，退一步說，即使這個字母排錯了一兩個，真的能給系統(tǒng)帶來災(zāi)難性后果么？從網(wǎng)上找的各種帖子討論來說，