共計 3749 個字符，預計需要花費 10 分鐘才能閱讀完成。
這篇文章給大家分享的是有關 MYSQL 中 COLLATE 有什么用的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。
MYSQL 中的 COLLATE 是什么？
在 mysql 中執行 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 關鍵字。這個值后面對應的 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;如果庫級別沒有設置 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 中 COLLATE 有什么用”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
