說(shuō)實(shí)話很多來(lái)看這個(gè)話題的人都是被標(biāo)題里的“吃”字吸引過(guò)來(lái)的。我先把結(jié)論放在前面utf8mb4_general_ci 和 utf8mb4_bin 最核心的區(qū)別一句話就是“一個(gè)不區(qū)分大小寫(xiě)一個(gè)區(qū)分大小寫(xiě)”。但如果你以為只有這一個(gè)區(qū)別直接在業(yè)務(wù)里亂用后面踩的坑大概率會(huì)讓你想把自己吃下去重來(lái)一次。用戶名在 general_ci 下可能“撞車(chē)”優(yōu)惠碼在 bin 下排序排得讓人一頭霧水兩個(gè)表的排序規(guī)則不一致直接拋 Illegal mix of collations 錯(cuò)誤甚至唯一索引在 general_ci 下會(huì)攔下你本來(lái)想放進(jìn)去的數(shù)據(jù)……這些真實(shí)發(fā)生過(guò)的生產(chǎn)問(wèn)題都藏在這兩個(gè)排序規(guī)則的行為差異里。這篇文章我會(huì)從原理到實(shí)測(cè) SQL再到選型建議和排錯(cuò)經(jīng)驗(yàn)把這兩個(gè)排序規(guī)則徹底掰開(kāi)揉碎講清楚保證你看完不需要吃任何東西。1. 從一條“詭異”的登錄 SQL 說(shuō)起假設(shè)你有一個(gè)用戶表創(chuàng)建的時(shí)候用了默認(rèn)的 utf8mb4_general_ci里面存了一個(gè)用戶名為Admin的賬號(hào)。然后用戶在前端輸入admin去登錄執(zhí)行下面這條 SQLSELECT * FROM users WHERE username admin;結(jié)果這個(gè)賬號(hào)居然被查出來(lái)了。如果你還加了唯一索引更麻煩的情況是想注冊(cè)admin的用戶會(huì)被告知“用戶名已被占用”哪怕你數(shù)據(jù)庫(kù)里根本沒(méi)有小寫(xiě)的admin。這就是 general_ci 的“ci”在做怪。它把A和a當(dāng)成同一個(gè)字符看待。那換成 bin 呢SELECT * FROM users WHERE username admin COLLATE utf8mb4_bin;這時(shí)候查詢結(jié)果為空因?yàn)锳dmin和admin在 bin 規(guī)則下是兩個(gè)完全不同的字符串。對(duì)很多程序猿來(lái)說(shuō)第一次踩到這種坑的時(shí)候第一反應(yīng)是“臥槽我的 SQL 寫(xiě)錯(cuò)了”第二反應(yīng)才是去查排序規(guī)則。所以我覺(jué)得有必要先把最基礎(chǔ)的概念講清楚后面所有差異都是由這個(gè)概念延伸出來(lái)的。1.1 先搞清楚字符集和排序規(guī)則是兩碼事很多人會(huì)把字符集charset和排序規(guī)則collation混在一起說(shuō)其實(shí)它們是兩個(gè)層級(jí)的配置。字符集解決的是“字符怎么存的”問(wèn)題。utf8mb4 是一種字符集它把每個(gè)字符映射成 1 到 4 個(gè)字節(jié)存儲(chǔ)在數(shù)據(jù)庫(kù)中。你可以把字符集理解成倉(cāng)庫(kù)里的貨架貨架的形狀決定了你能放什么尺寸的貨物。utf8mb4 能放下 emoji、生僻漢字、各種特殊符號(hào)因?yàn)樗С?4 字節(jié)字符。排序規(guī)則解決的是“字符怎么比”的問(wèn)題。同樣是兩個(gè)字符串怎么判斷它們相等怎么排先后順序這就是 collation 干的事。拿倉(cāng)庫(kù)來(lái)類(lèi)比貨架都有了你還得定規(guī)矩是按保質(zhì)期先后來(lái)上架還是按商品編碼大小來(lái)上架不同規(guī)矩同一個(gè)倉(cāng)庫(kù)里商品的擺放順序完全不一樣。所以字符集決定了你“能存什么”排序規(guī)則決定了你“查出來(lái)的結(jié)果怎么比、怎么排”。如果你在建表的時(shí)候只指定了字符集沒(méi)指定排序規(guī)則MySQL 會(huì)用一個(gè)默認(rèn)值。在 5.7 時(shí)代這個(gè)默認(rèn)值往往是utf8mb4_general_ci在 8.0 時(shí)代默認(rèn)變成了utf8mb4_0900_ai_ci。很多人根本不知道自己的表里用的是什么排序規(guī)則這是后面出問(wèn)題的根源。查看當(dāng)前表用的什么排序規(guī)則非常簡(jiǎn)單SHOW TABLE STATUS LIKE users;結(jié)果里有一列Collation會(huì)直接告訴你這個(gè)表用的規(guī)則。1.2 數(shù)據(jù)庫(kù)層面設(shè) utf8mb4到底在設(shè)什么你可能會(huì)說(shuō)“我建庫(kù)的時(shí)候直接寫(xiě)了CHARACTER SET utf8mb4不就行了”其實(shí)你只設(shè)了一半。完整的寫(xiě)法通常是CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;如果你只寫(xiě)了CHARACTER SET utf8mb4MySQL 會(huì)用字符集對(duì)應(yīng)的默認(rèn)排序規(guī)則。問(wèn)題在于不同 MySQL 版本默認(rèn)值不一樣這就導(dǎo)致遷移環(huán)境的時(shí)候可能本地是 general_ci線上卻變成了 0900_ai_ci行為立刻變得不一致。更值得注意的一點(diǎn)是MySQL 里字符集和排序規(guī)則是一對(duì)多的關(guān)系。同一個(gè)utf8mb4字符集下面掛著一堆排序規(guī)則常見(jiàn)的有utf8mb4_general_ciutf8mb4_binutf8mb4_unicode_ciutf8mb4_0900_ai_ciutf8mb4_0900_bin所以“用 utf8mb4”和“用哪個(gè) utf8mb4 規(guī)則”是兩件事。搞清楚這個(gè)前提我們?cè)賮?lái)看 general_ci 和 bin 的區(qū)別。2. 核心機(jī)制ci 和 bin 到底差在哪很多人對(duì)這兩個(gè)規(guī)則的認(rèn)知停留在“ci 不區(qū)分大小寫(xiě)bin 區(qū)分大小寫(xiě)”這個(gè)方向沒(méi)錯(cuò)但遠(yuǎn)遠(yuǎn)不夠。因?yàn)樽址容^的行為除了大小寫(xiě)還包括重音符號(hào)、尾隨空格、排序順序等多個(gè)維度。而且 general_ci 的“不敏感”并不是你想象的那種全方位不敏感。2.1 general 的“不敏感”和你以為的不一樣先說(shuō) ci 的全稱(chēng)case-insensitive即大小寫(xiě)不敏感。general_ci 在處理英文字母時(shí)會(huì)把A-Z和a-z折疊成同一組字符來(lái)比較所以在它的規(guī)則下MySQL、mysql、MYsql都是同一個(gè)字符串。但這里有個(gè)容易誤解的地方general_ci 不等于“所有字符都不敏感”。它對(duì)重音是敏感的。什么意思在utf8mb4_general_ci下é和e是兩個(gè)不同的字符?和A也不相等。這一點(diǎn)和 8.0 的utf8mb4_0900_ai_ci不一樣0900_ai_ci里的ai全稱(chēng)是 accent-insensitive重音不敏感在那種規(guī)則下é就能等于e。還有更微妙的MySQL 的字符串比較對(duì)于帶尾隨空格的情況默認(rèn)是忽略的。這兩個(gè)排序規(guī)則都屬于 PAD SPACE 族也就是說(shuō)account和account 在一般等值比較下是相等的。這又是另一個(gè)容易埋雷的細(xì)節(jié)。所以我們可以把 general_ci 的行為總結(jié)成一個(gè)矩陣行為維度utf8mb4_general_ciutf8mb4_bin大小寫(xiě)是否敏感不敏感敏感重音是否敏感敏感敏感尾隨空格是否忽略忽略PAD SPACE忽略PAD SPACE比較方式先大小寫(xiě)折疊再按規(guī)則映射比較直接按 UTF-8 字節(jié)或 Unicode 碼點(diǎn)比較排序結(jié)果大小寫(xiě)混排、偏向人類(lèi)習(xí)慣嚴(yán)格按編碼值排大寫(xiě)集中在前唯一索引行為Admin和admin會(huì)沖突互不沖突可同時(shí)存在bin 的全稱(chēng)就是 binary它做的事情非常簡(jiǎn)單粗暴比較的時(shí)候直接看字節(jié)序。因?yàn)?utf8mb4 的編碼和 Unicode 碼點(diǎn)是單調(diào)映射的所以 bin 規(guī)則下比較兩個(gè)字符串約等于按 Unicode 碼點(diǎn)一個(gè)個(gè)字符去比。2.2 關(guān)鍵差異一覽大小寫(xiě)、重音、尾隨空格、排序上面那張表信息量已經(jīng)不小但我覺(jué)得還是有必要把每個(gè)維度再拆細(xì)一點(diǎn)因?yàn)槟銓?shí)際寫(xiě) SQL 的時(shí)候這些差異會(huì)直接暴露出來(lái)。第一個(gè)維度是大小寫(xiě)。這是兩個(gè)規(guī)則最直觀的區(qū)別SELECT mysql MySQL COLLATE utf8mb4_general_ci AS result;結(jié)果是 1表示相等。SELECT mysql MySQL COLLATE utf8mb4_bin AS result;結(jié)果是 0表示不相等。第二個(gè)維度是重音。前面說(shuō)過(guò)general_ci 雖然是ci但它只對(duì)大小寫(xiě)不敏感對(duì)重音仍然敏感。拿法語(yǔ)字符試一下SELECT é e COLLATE utf8mb4_general_ci AS result;結(jié)果是 0é和e不相等。這一點(diǎn)對(duì)多語(yǔ)言業(yè)務(wù)很重要。如果你的系統(tǒng)將來(lái)要放法語(yǔ)、德語(yǔ)這類(lèi)帶重音字符的內(nèi)容又希望重音和不帶重音的字符等價(jià)那么utf8mb4_general_ci根本不能滿足你你得用utf8mb4_0900_ai_ci。第三個(gè)維度是尾隨空格。這個(gè)坑特別隱蔽平時(shí)大家?guī)缀醪粫?huì)注意到。PAD SPACE 的意思是比較兩個(gè)字符串時(shí)如果長(zhǎng)度不一致MySQL 會(huì)把較短字符串的末尾自動(dòng)補(bǔ)一些空格讓兩者長(zhǎng)度對(duì)齊后再比較。所以SELECT mysql mysql COLLATE utf8mb4_general_ci AS result; SELECT mysql mysql COLLATE utf8mb4_bin AS result;這兩個(gè)查詢結(jié)果都是 1?？吹?jīng)]有bin 在這個(gè)場(chǎng)景下也會(huì)“容忍”尾隨空格。這其實(shí)不符合很多人對(duì) binary 的直覺(jué)但 MySQL 對(duì)utf8mb4_bin的實(shí)現(xiàn)就是 PAD SPACE而不是 NO PAD。如果你想要那種連一個(gè)空格都嚴(yán)格區(qū)分的效果得用 MySQL 8.0 的utf8mb4_0900_bin那個(gè)才是 NO PAD。第四個(gè)維度是排序。同樣一批數(shù)據(jù)用不同排序規(guī)則得到的順序完全不同。這個(gè)對(duì)列表頁(yè)、分頁(yè)查詢影響很大后面實(shí)測(cè)部分我會(huì)用 SQL 展示。2.3 另外一個(gè)容易忽略的版本變化8.0 的 0900 系說(shuō)實(shí)話如果你用的 MySQL 是 8.0 及以上你大概率已經(jīng)在用utf8mb4_0900_ai_ci而不是utf8mb4_general_ci了只是你沒(méi)意識(shí)到。MySQL 8.0 把默認(rèn)字符集改成了 utf8mb4默認(rèn)排序規(guī)則改成了utf8mb4_0900_ai_ci。這個(gè) 0900 系列基于 Unicode 9.0 標(biāo)準(zhǔn)的排序算法對(duì)多語(yǔ)言的支持比 general_ci 完善得多重音不敏感還改成了 NO PAD 行為。這里有個(gè)現(xiàn)實(shí)問(wèn)題很多老項(xiàng)目是從 5.7 遷移到 8.0 的表是以前建的排序規(guī)則還停留在 general_ci。新表呢默認(rèn)已經(jīng)變成 0900_ai_ci。于是同一個(gè)數(shù)據(jù)庫(kù)里不同表用著不同規(guī)則聯(lián)結(jié)查詢的時(shí)候就會(huì)出幺蛾子。這也是我為什么一直建議團(tuán)隊(duì)在建表的時(shí)候顯式寫(xiě)明COLLATE不要讓默認(rèn)值給你暗中做決定。3. 實(shí)測(cè)演示用 SQL 把兩者“打回原形”光講理論是很虛的我們直接建表、插入數(shù)據(jù)看這兩個(gè)排序規(guī)則在真實(shí) SQL 里的表現(xiàn)。3.1 準(zhǔn)備數(shù)據(jù)同一張表兩個(gè)排序規(guī)則先建兩張結(jié)構(gòu)完全一樣、只有排序規(guī)則不同的表CREATE TABLE t_general_ci ( name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ); CREATE TABLE t_bin ( name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin );然后插入同一批數(shù)據(jù)INSERT INTO t_general_ci VALUES (Apple), (apple), (Banana), (banana), (admin); INSERT INTO t_bin VALUES (Apple), (apple), (Banana), (banana), (admin);現(xiàn)在我們對(duì)這兩張表分別查詢等值條件和排序結(jié)果差異馬上就出來(lái)了。3.2 等值查詢和排序結(jié)果實(shí)測(cè)先看等值查詢。在 general_ci 表里執(zhí)行SELECT * FROM t_general_ci WHERE name APPLE;你會(huì)看到結(jié)果返回了Apple和apple兩行。同樣的查詢?cè)?bin 表里SELECT * FROM t_bin WHERE name APPLE;結(jié)果為空因?yàn)锳PPLE是全大寫(xiě)的和Apple、apple都不同。然后看排序。執(zhí)行SELECT * FROM t_general_ci ORDER BY name; SELECT * FROM t_bin ORDER BY name;general_ci 的結(jié)果大概是這樣大小寫(xiě)被折疊對(duì)待apple和Apple會(huì)排在一起整體視覺(jué)上符合人對(duì)英文字母表的認(rèn)知。bin 的結(jié)果就完全不一樣了。因?yàn)榇髮?xiě)字母的 Unicode 碼點(diǎn)65-90比小寫(xiě)字母的碼點(diǎn)97-122小所以所有以大寫(xiě)開(kāi)頭的字符串會(huì)全部排在小寫(xiě)開(kāi)頭的字符串前面。你會(huì)看到像Apple、Banana先出現(xiàn)然后是apple、banana。這個(gè)排序差異在列表頁(yè)也許無(wú)所謂但如果你的業(yè)務(wù)依賴(lài)數(shù)據(jù)庫(kù)排序做分頁(yè)換一個(gè)排序規(guī)則可能導(dǎo)致同樣的數(shù)據(jù)被重復(fù)翻出來(lái)或者漏掉對(duì)用戶體驗(yàn)影響很大。3.3 唯一索引下的“隱形合并”再來(lái)做一個(gè)實(shí)驗(yàn)在 general_ci 字段上建唯一索引然后嘗試插入Admin和admin。CREATE TABLE users_general ( username VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci UNIQUE ); INSERT INTO users_general VALUES (Admin); INSERT INTO users_general VALUES (admin);第二條語(yǔ)句會(huì)直接報(bào)錯(cuò)提示Duplicate entry。原因很清晰general_ci 認(rèn)為Admin和admin是同一個(gè)字符串因此唯一索引判定沖突。同樣的操作在 bin 字段上就沒(méi)任何問(wèn)題CREATE TABLE users_bin ( username VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE ); INSERT INTO users_bin VALUES (Admin); INSERT INTO users_bin VALUES (admin);兩條都能插入成功。這個(gè)特性你說(shuō)它是 bug 還是 feature看業(yè)務(wù)。如果你希望用戶名不允許大小寫(xiě)重復(fù)注冊(cè)那 general_ci 正好幫你省了代碼層的邏輯。如果你希望Admin和admin是兩個(gè)完全不同的賬號(hào)那你必須用 bin。4. 性能與索引bin 真的更快嗎網(wǎng)上一直有一種說(shuō)法說(shuō) bin 比較效率更高推薦凡事都上 bin。這話有依據(jù)但也很容易誤導(dǎo)人。我們需要把比較成本、索引行為、報(bào)錯(cuò)現(xiàn)象分開(kāi)來(lái)看。4.1 比較成本拆解字節(jié)比較 vs 規(guī)則比較從算法角度說(shuō)bin 的比較確實(shí)更快。因?yàn)樗倪壿嫹浅：?jiǎn)單拿出兩個(gè)字符串的 UTF-8 字節(jié)序列一個(gè)字節(jié)一個(gè)字節(jié)地比大小本質(zhì)上就是memcmp一類(lèi)的操作。計(jì)算機(jī)對(duì)這類(lèi)操作是非常高效的而且?guī)缀醪恍枰~外的狀態(tài)轉(zhuǎn)換。general_ci 就不一樣了。它要先做大小寫(xiě)折疊每個(gè)字符可能還要查映射表然后才進(jìn)入比較邏輯。雖然 MySQL 對(duì)這塊做了很多性能優(yōu)化但不管怎么優(yōu)化計(jì)算步驟都比直接字節(jié)比較多。所以在純 CPU 密集、大量字符串比較的場(chǎng)景下bin 有優(yōu)勢(shì)。但實(shí)際業(yè)務(wù)系統(tǒng)里查詢的瓶頸很少出現(xiàn)在字符串比較這一步更多時(shí)候是磁盤(pán) IO、索引回表、網(wǎng)絡(luò)傳輸。你用一個(gè)帶索引的等值查詢數(shù)據(jù)庫(kù)通過(guò) B-Tree 定位記錄比較次數(shù)非常有限bin 和 general_ci 的差異完全可以忽略。只有當(dāng)你需要對(duì)超大結(jié)果集做ORDER BY或者在大表上頻繁做字符串排序時(shí)才能感受到一點(diǎn)點(diǎn)性能差別。但為了這點(diǎn)差別去犧牲業(yè)務(wù)語(yǔ)義完全不值得。4.2 排序規(guī)則不一致導(dǎo)致的 Illegal mix 問(wèn)題這個(gè)坑我?guī)缀趺磕甓家獛腿伺挪楹脦状螆?bào)錯(cuò)就是那句經(jīng)典的ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_bin,IMPLICIT) for operation 什么叫 illegal mix舉個(gè)簡(jiǎn)單的例子。你有兩張表一張t_general_ci的字段是 general_ci另一張t_bin的字段是 bin。你做聯(lián)結(jié)查詢SELECT * FROM t_general_ci g JOIN t_bin b ON g.name b.name;MySQL 一看兩個(gè)字段的排序規(guī)則不同不知道按誰(shuí)的規(guī)則來(lái)比較直接給你報(bào)錯(cuò)。解決辦法有幾種。最簡(jiǎn)單的是在比較的時(shí)候顯式指定規(guī)則SELECT * FROM t_general_ci g JOIN t_bin b ON g.name b.name COLLATE utf8mb4_bin;或者反過(guò)來(lái)SELECT * FROM t_general_ci g JOIN t_bin b ON g.name COLLATE utf8mb4_bin b.name;也可以用CONVERT轉(zhuǎn)換SELECT * FROM t_general_ci g JOIN t_bin b ON CONVERT(g.name USING utf8mb4) COLLATE utf8mb4_bin b.name;臨時(shí)語(yǔ)法能解決問(wèn)題但長(zhǎng)期來(lái)看還是得統(tǒng)一表的排序規(guī)則。比較合理的方式是同庫(kù)同表同一個(gè)排序規(guī)則除非某個(gè)字段有特別明確的使用理由。4.3 索引長(zhǎng)度與 utf8mb4 的 191 字符魔咒順便說(shuō)一個(gè)和排序規(guī)則一起出現(xiàn)的高頻問(wèn)題utf8mb4 下建索引字段長(zhǎng)度稍微一長(zhǎng)就直接報(bào)錯(cuò)。utf8mb4 一個(gè)字符最多占 4 字節(jié)老版本 InnoDB 的索引鍵最大限制是 767 字節(jié)算下來(lái)單個(gè)索引列最多只能存 191 個(gè)字符。所以 5.6、5.7 時(shí)代你給一個(gè)VARCHAR(255)字段加索引經(jīng)常踩到Specified key was too long。解決辦法要么是用前綴索引CREATE INDEX idx_name ON users (name(191));要么等 MySQL 8.0 用 DYNAMIC 行格式索引鍵上限提升到 3072 字節(jié)這個(gè)問(wèn)題才有所緩解。這跟 general_ci 還是 bin 沒(méi)有直接關(guān)系但只要你把字符集改成 utf8mb4就一定會(huì)碰到。很多人在換了排序規(guī)則又發(fā)現(xiàn)索引失效或建不出來(lái)的時(shí)候才會(huì)想起字節(jié)長(zhǎng)度這個(gè)隱形的天花板。5. 業(yè)務(wù)選型哪些字段必須 bin哪些適合 general_ci講完原理和實(shí)操最后落到怎么選。我的觀點(diǎn)非常簡(jiǎn)單排序規(guī)則是一種業(yè)務(wù)語(yǔ)義的數(shù)據(jù)庫(kù)表達(dá)選哪個(gè)不是看“哪個(gè)更高級(jí)”而是看你希望字符串之間怎么比較。5.1 按業(yè)務(wù)語(yǔ)義一張表做選擇不同類(lèi)型的字段建議直接照下面這個(gè)表來(lái)。字段類(lèi)型推薦排序規(guī)則原因用戶名登錄賬號(hào)general_ci / unicode_ci多數(shù)業(yè)務(wù)不希望出現(xiàn)Admin和admin同時(shí)存在大小寫(xiě)不敏感更符合常規(guī)體驗(yàn)郵箱general_ci / unicode_ci郵箱規(guī)范化時(shí)默認(rèn)不區(qū)分大小寫(xiě)能防止同一個(gè)郵箱注冊(cè)多個(gè)賬號(hào)密碼/哈希值bin哈希字符串大小寫(xiě)敏感用 ci 會(huì)直接讓不同哈希值互相匹配Token/API Keybin這類(lèi)值是精確匹配任何字符差異都有意義訂單號(hào)/優(yōu)惠碼bin很多編碼規(guī)則生成時(shí)區(qū)分大小寫(xiě)排序也應(yīng)該按精確碼值文章標(biāo)題/標(biāo)簽general_ci 或 0900_ai_ci需要做模糊匹配和展示大小寫(xiě)敏感反而影響體驗(yàn)多語(yǔ)言內(nèi)容法語(yǔ)、德語(yǔ)0900_ai_ci 優(yōu)先支持重音不敏感排序也更符合 Unicode 標(biāo)準(zhǔn)內(nèi)部編碼/狀態(tài)碼bin一般由系統(tǒng)生成精確比較與排序更穩(wěn)妥需要注意用戶名選 general_ci 不代表一定安全。如果你還要求用戶名里不能有大小寫(xiě)完全相同但視覺(jué)不同的字符那就得配合應(yīng)用層校驗(yàn)或者用更嚴(yán)格的規(guī)則。數(shù)據(jù)庫(kù)排序規(guī)則只是第一道防線不能解決所有問(wèn)題。5.2 遷移現(xiàn)有表ALTER 或重建的注意事項(xiàng)老項(xiàng)目想從 general_ci 改成 bin或者反過(guò)來(lái)操作本身不復(fù)雜ALTER TABLE users MODIFY username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;也可以直接改表默認(rèn)ALTER TABLE users DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;注意MODIFY和DEFAULT的區(qū)別很大。改了默認(rèn)值只影響后續(xù)新增列已有列不會(huì)變。想全表統(tǒng)一就得逐列MODIFY或者直接用ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;這個(gè)語(yǔ)句會(huì)轉(zhuǎn)換整張表的字符集和排序規(guī)則包括所有字符串列。遷移前務(wù)必想清楚兩個(gè)問(wèn)題。第一排序規(guī)則的改變會(huì)影響索引比較。如果之前 general_ci 下建立的唯一索引現(xiàn)在改成 bin原本被攔住的大小寫(xiě)重復(fù)數(shù)據(jù)之后可能就能插入了。應(yīng)用邏輯沒(méi)變的話你可能在用戶登錄時(shí)突然出現(xiàn)“多個(gè)賬號(hào)對(duì)應(yīng)同一用戶名”的詭異現(xiàn)象。第二大表ALTER TABLE會(huì)鎖表或者消耗大量 IO。雖然 8.0 支持在線 DDL但還是建議在低峰期操作并先用小表演練一遍。我自己的習(xí)慣是先備份然后導(dǎo)出一份到測(cè)試環(huán)境驗(yàn)證數(shù)據(jù)一致性再在線上執(zhí)行。5.3 推薦配置建表時(shí)就把規(guī)則寫(xiě)死我見(jiàn)過(guò)太多團(tuán)隊(duì)把排序規(guī)則的決策完全交給數(shù)據(jù)庫(kù)默認(rèn)值導(dǎo)致建出來(lái)的表五花八門(mén)。最好的方式是在建表 SQL 里顯式寫(xiě)明COLLATE。如果你希望用戶名大小寫(xiě)不敏感就寫(xiě)CREATE TABLE users ( username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL );如果你希望密碼哈希大小寫(xiě)敏感就寫(xiě)CREATE TABLE user_auth ( password_hash VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL );同一個(gè)庫(kù)中的字段明確自己的語(yǔ)義然后讓代碼和數(shù)據(jù)庫(kù)的規(guī)則保持一致這是最省心的做法。6. 真實(shí)案例復(fù)盤(pán)與避坑清單最后這部分我想把自己踩過(guò)以及幫人排查過(guò)的幾個(gè)典型問(wèn)題復(fù)盤(pán)一下都是真實(shí)發(fā)生過(guò)的事供你對(duì)照排查。6.1 案例一用戶名大小寫(xiě)鎖號(hào)有個(gè)朋友做社交 App注冊(cè)時(shí)允許用戶自己輸入用戶名表用的是 general_ci。某天客服收到一堆賬號(hào)登錄不上的投訴一查發(fā)現(xiàn)是有用戶注冊(cè)了ALice另一個(gè)人注冊(cè)了Alice。因?yàn)?general_ci 的唯一索引判定這兩個(gè)用戶名相同后注冊(cè)的人直接被拒。但問(wèn)題出在登錄側(cè)老用戶 A 一直用ALice登錄某天改成了alice系統(tǒng)按 general_ci 查居然也能查到賬號(hào)?？雌饋?lái)是“貼心”可一旦賬號(hào)走找回密碼流程驗(yàn)證邏輯對(duì)大小寫(xiě)敏感的字段做了嚴(yán)格匹配就會(huì)出現(xiàn)數(shù)據(jù)庫(kù)能查到但業(yè)務(wù)校驗(yàn)不通過(guò)的情況。最后我們把用戶名字段改成 bin并且在應(yīng)用層加了一道統(tǒng)一的用戶名規(guī)范化處理一律轉(zhuǎn)成小寫(xiě)再注冊(cè)。這樣既防止了大小寫(xiě)撞車(chē)又保持了用戶輸入的靈活性。6.2 案例二優(yōu)惠碼唯一索引報(bào)錯(cuò)另一個(gè)場(chǎng)景是我們公司內(nèi)部的營(yíng)銷(xiāo)系統(tǒng)發(fā)優(yōu)惠碼時(shí)用隨機(jī)生成的字符串格式類(lèi)似XK7a2BfQ。當(dāng)時(shí)建表用的是 general_ci并且給優(yōu)惠碼字段加了唯一索引。上線第二天運(yùn)營(yíng)反饋說(shuō)系統(tǒng)提示優(yōu)惠碼重復(fù)。排查后發(fā)現(xiàn)兩個(gè)批次生成了xK7A2bFQ和XK7a2BfQ這兩串在 general_ci 下被判成同一個(gè)字符串唯一索引直接拒絕第二條記錄。解決辦法是把優(yōu)惠碼字段改成 bin然后加一個(gè)普通索引不需要唯一唯一性在應(yīng)用層生成的時(shí)候保證。從此再?zèng)]出過(guò)這類(lèi)問(wèn)題。6.3 避坑速查清單我把平時(shí)最容易踩的點(diǎn)匯總成一張清單你可以直接截圖保存風(fēng)險(xiǎn)點(diǎn)現(xiàn)象規(guī)避方式登錄時(shí)大小寫(xiě)誤判輸入大小寫(xiě)不同也能查到賬號(hào)業(yè)務(wù)側(cè)明確用戶名是否大小寫(xiě)不敏感選用匹配的 collation唯一索引撞車(chē)合法、不同的字符串被判定重復(fù)對(duì)大小寫(xiě)區(qū)分的字段令牌、碼、hash用 bin聯(lián)結(jié)查詢報(bào) Illegal mixjoin/比較時(shí)報(bào)錯(cuò)兩個(gè)字段統(tǒng)一 collation或顯式 COLLATE排序結(jié)果不符合預(yù)期大寫(xiě)字母集中在前分頁(yè)數(shù)據(jù)錯(cuò)位預(yù)期人類(lèi)排序用 ci預(yù)期精確二進(jìn)制用 bin尾隨空格被忽略保存了帶空格的值查詢時(shí)等效理解 PAD SPACE 行為需要嚴(yán)格比較用 0900_bin遷移后索引失效改 collation 后部分 SQL 不能走索引MODIFY 前做 explain 檢查執(zhí)行計(jì)劃6.4 一個(gè)小技巧用 COLLATE 臨時(shí)驗(yàn)證問(wèn)題如果你懷疑線上某個(gè)字段的排序規(guī)則有問(wèn)題但不想立刻改表可以用 COLLATE 在查詢時(shí)臨時(shí)指定規(guī)則先驗(yàn)證行為是否符合預(yù)期。打個(gè)比方你要驗(yàn)證用戶的輸入在 bin 規(guī)則下是否唯一SELECT username, COUNT(*) FROM users GROUP BY username COLLATE utf8mb4_bin HAVING COUNT(*) 1;這樣你就可以快速知道當(dāng)前數(shù)據(jù)在更嚴(yán)格的比較規(guī)則下有沒(méi)有隱藏的“重復(fù)”。注意這個(gè)寫(xiě)法在 GROUP BY 中可能出現(xiàn)索引失效但作為一次性排查工具完全夠用。另外千萬(wàn)不要在已經(jīng)有線上流量的核心表上直接改排序規(guī)則。我見(jiàn)過(guò)有人覺(jué)得 bin 更“高級(jí)”順手把用戶表全改了結(jié)果應(yīng)用層還能登錄但所有涉及大小寫(xiě)的唯一性邏輯全部失效數(shù)據(jù)隔天就亂了。改之前先在測(cè)試環(huán)境完整模擬一遍。我個(gè)人用到現(xiàn)在的體會(huì)是collation 這個(gè)東西平時(shí)看不見(jiàn)摸不著一旦出問(wèn)題往往是那種“每條 SQL 看過(guò)去都沒(méi)毛病但整體行為就是不對(duì)”的疑難雜癥。與其事后排查不如在建表的時(shí)候多想兩分鐘把每個(gè)字段的語(yǔ)義和排序規(guī)則對(duì)齊。USERNAME 要大小寫(xiě)不敏感就大大方方用 general_ciTOKEN、哈希、優(yōu)惠碼這一類(lèi)機(jī)器生成且大小寫(xiě)敏感的值就直接 bin。規(guī)則和業(yè)務(wù)語(yǔ)義保持一致后面能省掉大把時(shí)間。如果你現(xiàn)在正在追查一個(gè)莫名其妙的“字符相等但又不相等”的問(wèn)題先跑一句SHOW CREATE TABLE 表名;看看每個(gè)字段的 COLLATE 是什么再對(duì)照這篇文章里的行為矩陣去排查八成能定位到根因。