mysql中的collate是什么？
在mysql中執行show create table
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 collation指令可以查看到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 Collation 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;</db_name>
如果庫級別沒有設置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和表上設置的一樣。
