MYSQL中的COLLATE是什麼?

騰訊雲+社群發表於2018-11-28

本文由horstxu發表

在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的開發可能會非常眼熟,因為其中的中文表頭已經給出了答案:

imgphpmyadmin截圖

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

各種COLLATE的區別

COLLATE通常是和資料編碼(CHARSET)相關的,一般來說每種CHARSET都有多種它所支援的COLLATE,並且每種CHARSET都指定一種COLLATE為預設值。例如Latin1編碼的預設COLLATElatin1_swedish_ciGBK編碼的預設COLLATEgbk_chinese_ciutf8mb4編碼的預設值為utf8mb4_general_ci

這裡順便講個題外話,mysql中有utf8utf8mb4兩種編碼,在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如下圖所示。

imgmysql中和utf8mb4相關的所有COLLATE

圖中我們能看到很多國家的語言自己的排序規則。在國內比較常用的是utf8mb4_general_ci(預設)、utf8mb4_unicode_ciutf8mb4_bin這三個。我們來探究一下這三個的區別:

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

utf8mb4_unicode_ciutf8mb4_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_ciutf8mb4_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;

如果庫級別沒有設定CHARSETCOLLATE,則庫級別預設的CHARSETCOLLATE使用例項級別的設定。在mysql8.0以下版本中,你如果什麼都不修改,預設的CHARSETLatin1,預設的COLLATElatin1_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;

如果表級別沒有設定CHARSETCOLLATE,則表級別會繼承庫級別的CHARSETCOLLATE

列級別的設定,則在CREATE TABLE中宣告列的時候指定,例如

CREATE TABLE (

`field1` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT ``,

……

) ……

如果列級別沒有設定CHARSETCOLATE,則列級別會繼承表級別的CHARSETCOLLATE

最後,你也可以在寫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的查詢中也應該儘量避免使用中文做查詢條件。

此文已由作者授權騰訊雲+社群釋出,更多原文請點選

搜尋關注公眾號「雲加社群」,第一時間獲取技術乾貨,關注後回覆1024 送你一份技術課程大禮包!

相關文章