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