場(chǎng)景描述

今天在將 Hive 表同步到 MySQL 之后，其中有一列是唯一列，但是在 MySQL 中查詢的時(shí)候 count 與 distinct count 查詢出來(lái)的數(shù)值是不一樣的，這么來(lái)看的話是有重復(fù)的數(shù)據(jù)（按理說(shuō)不應(yīng)該的，因?yàn)樵?Hive 中，這兩個(gè)數(shù)值是一樣的），那么將重復(fù)的數(shù)據(jù)查出來(lái)看了一下，發(fā)現(xiàn)是大小寫的問(wèn)題，然后查了一下，發(fā)現(xiàn) MySQL 數(shù)據(jù)庫(kù)默認(rèn)情況下，字符串字段的所有相關(guān)運(yùn)算是大小寫"不敏感"的。

這一點(diǎn)與其它流行的數(shù)據(jù)庫(kù)都不相同。

解決辦法

1. 查詢時(shí)指定大小寫敏感

MySQL 允許在查詢的時(shí)候指定以大小寫敏感方式，需要使用關(guān)鍵字 BINARY，查詢?nèi)缦拢?/p>

SELECT * FROM student WHERE BINARY name = 'ZhangSan';
--或者
SELECT * FROM student WHERE name = BINARY 'ZhangSan';

很多時(shí)候當(dāng)發(fā)現(xiàn) MySQL 數(shù)據(jù)庫(kù)存在上述問(wèn)題時(shí)，系統(tǒng)已經(jīng)運(yùn)行了一段時(shí)間，如果采用方法二或方法三的代價(jià)可能會(huì)很大。

使用此方法最大的好處便是可以快速實(shí)現(xiàn)功能。

但是這個(gè)方法也存在很大的限制：如此可能因?yàn)闊o(wú)法使用索引導(dǎo)致查詢性能下降。

原因很好理解，因?yàn)榇藭r(shí)針對(duì)查詢字段的索引也是按照大小寫不敏感方式建立的。

除非數(shù)據(jù)量不大，或者在你的應(yīng)用中不在乎這點(diǎn)性能上的損失，那么只能選擇方法二或方法三了。

2. 定義表結(jié)構(gòu)時(shí)指定字段大小寫敏感

在創(chuàng)建表時(shí)指定具體的字段大小寫敏感，示例如下：

CREATE TABLE student (
  ...
  name VARCHAR(64) BINARY NOT NULL,
  ...

)

關(guān)鍵字 BINARY 指定 name 字段大小寫敏感。

如此在查詢時(shí)就算不使用 BINARY 關(guān)鍵字，查詢語(yǔ)句也是大小寫敏感的。

在此基礎(chǔ)上創(chuàng)建的 name 相關(guān)的索引也是大小寫敏感的，也就能夠使用索引來(lái)提高性能。

MySQL 允許在大多數(shù)字符串類型上使用 BINARY 關(guān)鍵字，用于指明所有針對(duì)該字段的運(yùn)算是大小寫敏感的，更多信息請(qǐng)參見 MySQL 官方文檔。

這種方法使得設(shè)計(jì)者可以精確地控制每個(gè)字段是否大小寫敏感。不過(guò)在很多系統(tǒng)的設(shè)計(jì)中，期望大部分甚至所有的字段統(tǒng)一大小寫敏感。MySQL 也提供了解決方案，這就要用到方法三。

3. 修改排序規(guī)則（COLLATE）

在 MySQL 中執(zhí)行 show create table <tablename> 指令，可以看到一張表的建表語(yǔ)句，example 如下：

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_general_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_general_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_general_ci 是什么意思呢？下面我們就來(lái)了解一下。

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

使用 Navicat 開發(fā)的可能會(huì)比較眼熟，因?yàn)槠渲械倪x項(xiàng)中已經(jīng)給出了答案：

所謂 utf8_general_ci，其實(shí)是用來(lái)排序的規(guī)則。對(duì)于 MySQL 中那些字符類型的列，如VARCHAR，CHAR，TEXT 類型的列，都需要有一個(gè) COLLATE 類型來(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ǔ)句的查詢結(jié)果。另外，MySQL 建索引的時(shí)候，如果索引列是字符類型，也會(huì)影響索引創(chuàng)建，只不過(guò)這種影響我們感知不到。總之，凡是涉及到字符類型比較或排序的地方，都會(huì)和 COLLATE 有關(guān)。

涉及字符串的各種運(yùn)算其核心必然涉及到采用何種字符排序規(guī)則（COLLATE，也有翻譯為"核對(duì)"）。本質(zhì)上 MySQL 是通過(guò) COLLATE 取值決定字符串運(yùn)算是否大小寫敏感。

utf8_general_ci 是一個(gè)具體的 COLLATE 取值。每個(gè)具體的 COLLATE 都對(duì)應(yīng)唯一的字符集，可以看出該 COLLATE 對(duì)應(yīng)字符集為 utf8。而與大小寫敏感問(wèn)題相關(guān)的是其后綴 _ci，MySQL 官方文檔對(duì)其的解釋是 Case Ignore 的縮寫，即大小寫不敏感。由于 MySQL 將 utf8_general_ci 指定作為字符集 utf8 的默認(rèn) COLLATE，這也就導(dǎo)致文章開頭所說(shuō)的現(xiàn)象。與此同時(shí)，MySQL 也提供了其它的 COLLATE 取值選項(xiàng)，utf8_bin 就是大小寫敏感的。事實(shí)上所有大小寫敏感的 COLLATE 都以 _bin或 _cs 為后綴，前者是 Binary 的縮寫，后者是 Case Sensitive 的縮寫。

各種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 的縮寫，即大小寫無(wú)關(guān)，也就是說(shuō) "A" 和 "a" 在排序和比較的時(shí)候是一視同仁的。selection * from table1 where field1="a" 同樣可以把 field1 為 "A" 的值選出來(lái)。與此同時(shí)，對(duì)于那些 _cs 后綴的 COLLATE，則是 Case Sensitive，即大小寫敏感的。

在 MySQL 中使用 show collation 指令可以查看到 MySQL 所支持的所有 COLLATE。以 utf8mb4 為例，該編碼所支持的所有 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ū)分大小寫的。

而 utf8mb4_unicode_ci 和 utf8mb4_general_ci 對(duì)于中文和英文來(lái)說(shuō)，其實(shí)是沒(méi)有任何區(qū)別的。對(duì)于我們開發(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ì)于正常的開發(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 開始，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í)

MySQL 數(shù)據(jù)庫(kù)允許在庫(kù)、表 和 列 三個(gè)級(jí)別上指定 Collation。當(dāng)同時(shí)指定時(shí)，優(yōu)先關(guān)系是：列 > 表 > 庫(kù)。

設(shè)置 COLLATE 可以在實(shí)例級(jí)別、庫(kù)級(jí)別、表級(jí)別、列級(jí)別、以及 SQL 指定。當(dāng)同時(shí)指定時(shí)，優(yōu)先關(guān)系是：SQL 指定 > 列 > 表 > 庫(kù) > 實(shí)例級(jí)別。

• 實(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è)置。在 MySQL 8.0 以下版本中，你如果什么都不修改，默認(rèn)的 CHARSET 是 Latin1，默認(rèn)的 COLLATE 是 latin1_swedish_ci。從 MySQL 8.0 開始，默認(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 table_name (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT = '表注釋';

如果表級(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 '' COMMENT '字段1',
……
) ……

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

• 最后，你也可以在寫 SQL 查詢的時(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)重依賴中文字段的排序結(jié)果，在 MySQL 的查詢中也應(yīng)該盡量避免使用中文做查詢條件。

總結(jié)

這些僅為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論