快捷導(dǎo)航

詳細(xì)談?wù)凪YSQL中的COLLATE是什么

更新時(shí)間：2021年06月11日 12:08:08 作者：horstxu

mysql COLLATE是校對(duì)集的意思,可以理解為,排序規(guī)則等,下面這篇文章詳細(xì)的給大家介紹了關(guān)于MYSQL中COLLATE是什么的相關(guān)資料,需要的朋友可以參考下

前言

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

COLLATE是用來(lái)做什么的？

使用phpmyadmin的開(kāi)發(fā)可能會(huì)非常眼熟，因?yàn)槠渲械闹形谋眍^已經(jīng)給出了答案：

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

各種COLLATE的區(qū)別

COLLATE通常是和數(shù)據(jù)編碼（CHARSET）相關(guān)的，一般來(lái)說(shuō)每種CHARSET都有多種它所支持的COLLATE，并且每種CHARSET都指定一種COLLATE為默認(rèn)值。例如Latin1編碼的默認(rèn)COLLATE為latin1_swedish_ci，GBK編碼的默認(rèn)COLLATE為gbk_chinese_ci，utf8mb4編碼的默認(rèn)值為utf8mb4_general_ci。

這里順便講個(gè)題外話，mysql中有utf8和utf8mb4兩種編碼，在mysql中請(qǐng)大家忘記**utf8**，永遠(yuǎn)使用**utf8mb4**。這是mysql的一個(gè)遺留問(wèn)題，mysql中的utf8最多只能支持3bytes長(zhǎng)度的字符編碼，對(duì)于一些需要占據(jù)4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

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

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以u(píng)tf8mb4為例，該編碼所支持的所有COLLATE如下圖所示。

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

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

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

而utf8mb4_unicode_ci和utf8mb4_general_ci對(duì)于中文和英文來(lái)說(shuō)，其實(shí)是沒(méi)有任何區(qū)別的。對(duì)于我們開(kāi)發(fā)的國(guó)內(nèi)使用的系統(tǒng)來(lái)說(shuō)，隨便選哪個(gè)都行。只是對(duì)于某些西方國(guó)家的字母來(lái)說(shuō)，utf8mb4_unicode_ci會(huì)比utf8mb4_general_ci更符合他們的語(yǔ)言習(xí)慣一些，general是mysql一個(gè)比較老的標(biāo)準(zhǔn)了。例如，德語(yǔ)字母“ß”，在utf8mb4_unicode_ci中是等價(jià)于"ss"兩個(gè)字母的（這是符合德國(guó)人習(xí)慣的做法），而在utf8mb4_general_ci中，它卻和字母“s”等價(jià)。不過(guò)，這兩種編碼的那些微小的區(qū)別，對(duì)于正常的開(kāi)發(fā)來(lái)說(shuō)，很難感知到。本身我們也很少直接用文字字段去排序，退一步說(shuō)，即使這個(gè)字母排錯(cuò)了一兩個(gè)，真的能給系統(tǒng)帶來(lái)災(zāi)難性后果么？從網(wǎng)上找的各種帖子討論來(lái)說(shuō)，更多人推薦使用utf8mb4_unicode_ci，但是對(duì)于使用了默認(rèn)值的系統(tǒng)，也并沒(méi)有非常排斥，并不認(rèn)為有什么大問(wèn)題。結(jié)論：推薦使用utf8mb4_unicode_ci，對(duì)于已經(jīng)用了utf8mb4_general_ci的系統(tǒng)，也沒(méi)有必要花時(shí)間改造。

另外需要注意的一點(diǎn)是，從mysql 8.0開(kāi)始，mysql默認(rèn)的CHARSET已經(jīng)不再是Latin1了，改為了utf8mb4（參考鏈接），并且默認(rèn)的COLLATE也改為了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大體上就是unicode的進(jìn)一步細(xì)分，0900指代unicode比較算法的編號(hào)（ Unicode Collation Algorithm version），ai表示accent insensitive（發(fā)音無(wú)關(guān)），例如e, è, é, ê 和 ë是一視同仁的。相關(guān)參考鏈接1，相關(guān)參考鏈接2

COLLATE設(shè)置級(jí)別及其優(yōu)先級(jí)

設(shè)置COLLATE可以在示例級(jí)別、庫(kù)級(jí)別、表級(jí)別、列級(jí)別、以及SQL指定。實(shí)例級(jí)別的COLLATE設(shè)置就是mysql配置文件或啟動(dòng)指令中的collation_connection系統(tǒng)變量。

庫(kù)級(jí)別設(shè)置COLLATE的語(yǔ)句如下：

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
如果庫(kù)級(jí)別沒(méi)有設(shè)置CHARSET和COLLATE，則庫(kù)級(jí)別默認(rèn)的CHARSET和COLLATE使用實(shí)例級(jí)別的設(shè)置。在mysql8.0以下版本中，你如果什么都不修改，默認(rèn)的CHARSET是Latin1，默認(rèn)的COLLATE是latin1_swedish_ci。從mysql8.0開(kāi)始，默認(rèn)的CHARSET已經(jīng)改為了utf8mb4，默認(rèn)的COLLATE改為了utf8mb4_0900_ai_ci。

表級(jí)別的COLLATE設(shè)置，則是在CREATE TABLE的時(shí)候加上相關(guān)設(shè)置語(yǔ)句，例如：

CREATE TABLE (

……

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表級(jí)別沒(méi)有設(shè)置CHARSET和COLLATE，則表級(jí)別會(huì)繼承庫(kù)級(jí)別的CHARSET與COLLATE。

列級(jí)別的設(shè)置，則在CREATE TABLE中聲明列的時(shí)候指定，例如

CREATE TABLE (

`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',

……

) ……

如果列級(jí)別沒(méi)有設(shè)置CHARSET和COLATE，則列級(jí)別會(huì)繼承表級(jí)別的CHARSET與COLLATE。

最后，你也可以在寫(xiě)SQL查詢(xún)的時(shí)候顯示聲明COLLATE來(lái)覆蓋任何庫(kù)表列的COLLATE設(shè)置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;

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

如果全都顯示設(shè)置了，那么優(yōu)先級(jí)順序是 SQL語(yǔ)句 > 列級(jí)別設(shè)置 > 表級(jí)別設(shè)置 > 庫(kù)級(jí)別設(shè)置 > 實(shí)例級(jí)別設(shè)置。也就是說(shuō)列上所指定的COLLATE可以覆蓋表上指定的COLLATE，表上指定的COLLATE可以覆蓋庫(kù)級(jí)別的COLLATE。如果沒(méi)有指定，則繼承下一級(jí)的設(shè)置。即列上面沒(méi)有指定COLLATE，則該列的COLLATE和表上設(shè)置的一樣。

以上就是關(guān)于mysql的COLLATE相關(guān)知識(shí)。不過(guò)，在系統(tǒng)設(shè)計(jì)中，我們還是要盡量避免讓系統(tǒng)嚴(yán)重依賴(lài)中文字段的排序結(jié)果，在mysql的查詢(xún)中也應(yīng)該盡量避免使用中文做查詢(xún)條件。