2019年，德國
前段時間，遇到朋友的求助，說以前運行的好好的系統現在出問題了，而且看不懂報錯到底是什麼意思。
我仔細看看報錯信息，應該是MySQL數據庫報出來的，大意是說：collation不兼容，一個是 utf8mb4_0900_ai_ci，另一個是utf8mb4_general_ci。
utf8mb4_general_ci這玩意兒我見過，是針對utf8mb4編碼的collation，但是utf8mb4_0900_ai_ci是啥，我也沒見過。
於是我問他，這玩意兒從哪裡出來的？
他說：“我也不知道，我完全沒見過啊。再說，我數據庫編碼已經是utf8mb4了，怎麼還會有這麼多名堂？”
看他著急又不知所措的樣子，我便花了點時間來研究，還真學到點新知識。而且我也發現，有許多程序員天真的以為“用了UTF8就等於做了國際化了，不用再擔心編碼問題”。看來，這個話題還真值得多講講。
首先從utf8mb4_0900_ai_ci這個詭異的名字說起。
Unicode編碼的誕生，是為了解決之前各國的計算機文字編碼自成一體的問題。不同國家採用不同的編碼，自己用還算正常，但是跨文化交流必然會出問題，更無法解決“在同一篇文檔裡又要顯示中文又要顯示韓文還要顯示日文”之類的問題。
有了Unicode，地球上所有的文字都有獨一無二的編碼（Code Point，也就是為它分配的碼值，或者說“邏輯代號”），前述問題就解決了。
但是Unicode（有個相關的名字是UCS，Universal Coded Character Set，二者基本等價）只確定了碼值，或者說，只分配了邏輯代號。至於這些邏輯代號在實際使用中如何存儲，如何傳輸，那是另一個問題。而UTF-8，就是解決存儲和傳輸等問題的“實際方案”。
實際上，UTF的全名是Unicode Transformation Format，也就是“Unicode變換格式”。這裡的“變換”，基本可以類比為：要告訴別人明天早上九點來開會，到底是發郵件呢，還是打電話呢，還是寫紙條呢，還是直接去敲門打招呼呢？。
所以，Unicode的變換格式不只UTF-8一種，還有UTF-16、UTF-32等等。UTF-8使用比較普遍，因為它是變長編碼，如果只傳輸ASCII字符，則每個字符只需要一個字節。因此，如果數據中包含大量的ASCII字符，那麼UTF-8可以節省很多存儲空間。
老一點的程序員大概都知道UTF-8，在MySQL中寫作utf8，沒有橫線。如果要用MySQL存儲多種語言的字符，那麼把字符集（character set）設定為utf8是合適的選擇。注意，MySQL中必須指定utf8，而不是Unicode。因為Unicode只是邏輯規範，utf8才是具體存儲和傳輸的格式。
那麼，utf8mb4_0900_ai_ci什麼意思呢？
我們分部分來看這個名字，先從開頭看起。
utf8mb4，這個名字許多人大概熟悉。如今️✈️♥️emoji表情已經大量使用，但MySQL之前的的字符集（character set）是utf8（更準確的名字是utf8mb3，一個字符最多使用3個字節來存儲），只能存儲編碼值從0x000到0xFFFF之間的字符。
然而，emoji表情字符的碼值超過了0xFFFF，按照UTF-8規範，存儲時需要用4個字節。正因為如此，MySQL才提供了utf8mb4的字符集。如果把數據庫表的字符集設定為utf8mb4，就可以正常存儲包含表情字符的文本了。
中間的0900，它對應的是Unicode 9.0的規範。要知道，Unicode規範是在不斷更新的，每次更新既包括擴充，也包括修正。比如6.0版新加入了222箇中日韓統一表義字符（CJK Unified Ideographs），7.0版加入了俄國貨幣盧布的符號等等。
如果支持新的Unicode規範，就可以直接享受好處，像對待普通字符那樣對待這些新字符，當然是好事。
以前的MySQL雖然也會跟隨Unicode的更新，但速度太慢了。MySQL 5.7的第一個發行版MySQL 5.7.1是2013年4月23日面世的，它包含的最新的Unicode規範是Unicode 5.2，發佈於2009年10月。即便是2020年1月13日發佈的MySQL 5.7.29，仍然是這樣。
然而Unicode規範早已升級了很多版，即便是9.0版本，也發佈於2016年6月，過去了好多年了。到目前為止，最新的版本已經到了12.1，發佈於2019年5月。所以從5.2更新到9.0，看起來是一大進步，其實也只是補課而已。
Unicode在不斷更新，來源：維基百科
最後兩部分_ai_ci，ai表示accent insensitivity，也就是“不區分音調”，而ci表示case insensitivity，也就是“不區分大小寫”。
所以，utf8mb4_0900_ai_ci到底是個什麼東西呢？其實，它是個collation。
說起“字符集”，許多人想當然認為，給每個字符分配了一個編碼，並且能存儲、能傳輸，這就夠了。其實這當然不夠，我們不但需要給每個字符分配編碼，讓它們能存儲、能傳輸，還需要定義一套關係來組織它們，找到它們之間的聯繫。這套關係的定義，就是collation。
collation定義了哪個字符和哪個字符是“等價”的。所以如果指定“不區分大小寫”，那麼a和A，e和E就是等價的，這樣查找時就會方便很多。但這還不夠，世界上的文字很多，所以才會有“不區分音調”的要求，這時候e、ē、é、ě、è就是等價的，那麼假設我們要進行拼音查找，只要按e去找就可以全部列出來，很方便。甚至，它們也和ê、ë也是等價的，這樣就更方便了。
collation也定義了字符的排序規則，如果按照“字符順序（而不是簡單的‘字母順序’）”來排序，哪個字符應當排在哪個字符前面。所以，儘管“啊”、“副”、“德”三個字的拼音開頭分別為A、F、D，但直接選定collation為utf8mb4，它們並不會按照“啊”、“德”、“副”的順序排序，而是會排成“副”、“啊”、“德”。如果你希望把中文字符按照拼音來排序，指定使用gb18030_chinese_ci作為collation就可以了。
當然，要補充的是，collation依賴於字符集（character set），所以把gb18030_chinese_ci作為collation，就要求字符集是gb18030，而不能是utf8mb4。
這也很好理解，字符集定義了可以使用的字符，對應的collation定義了字符之間的關係。如果collation不依賴於字符集，那麼很可能出現“有些字符沒有關係定義，不知如何判斷等價和順序”的問題。
到這裡，那位朋友的疑惑就解開了。MySQL 8.0之後，默認collation不再像之前版本一樣是是utf8mb4_general_ci（這個名字也確實取得有問題，話說得太滿，有點自負了），而是統一更新成了
utf8mb4_0900_ai_ci。不幸的是，這位朋友的系統是一路升級上來的，所以之前建的各種數據表，它們的collation仍然是utf8mb4_general_ci，而新建的表是utf8mb4_0900_ai_ci。如果恰好遇到包含字符串相等或者大小比較的聯表查詢語句，而關聯的表又使用了不同的collation，MySQL就無法決策到底應當使用哪個，就會報錯。
既然如此，解決辦法也很簡單，用alter table table_name collate utf8mb4_0900_ai_ci顯式統一所有表的collation，問題就解決了。
我們可以多想想，把character set和collation分開，到底有什麼好處？其實好處很多。如果把字符看作個人，character set就相當於驗明正身，給每個字符發張身份證，而collation相當於告訴大家，排隊的時候誰在前誰在後。collation有多套，就相當於可以靈活按身高、體重、年齡、出身地等等因素來排序，卻完全不會受到身份證號的干擾。
實際上collation也是如此，既然有utf8mb4_0900_ai_ci，就還有utf8mb4_0900_as_ci和utf8mb4_0900_as_cs。看名字也可以知道，
utf8mb4_0900_as_ci表示“區分音調”，所以e、ē、é、ě、è就不再是等價的；而utf8mb4_0900_as_cs表示“區分大小寫”，所以查e的時候就不會把E查出來。這個問題本來不麻煩，為什麼會難住人呢？原因不復雜，你去看關於MySQL和Unicode的中文資料，絕大部分都是告訴你，utf8或者utf8mb4就可以解決問題了。因此，不少程序員完全意識不到還有collation這種東西。
所以，這些程序員理解的“字符集”就只有一堆孤零零的字符，根本沒想到還需要定義字符之間的等價和排序關係。而這恰恰是最可惜的，因為他們完全錯過了“舉一反三”的啟發，許多類似問題也就缺乏解決思路。要知道，哪怕你做的不是國際化的業務，也可以從collation中受益的。
我們都知道，電商系統的訂單處理是一個流程，其中涉及許多狀態，比如“已下單，未支付”、“已支付”、“已確認”、“已揀貨”、“已發貨”等等。
有程序員看到這個需求，想當然就按照先後順序，用1、2、3、4、5來表示對應狀態，確實簡單不會出錯，也方便先後對比，比如要查找所有“已確認”之前的訂單，就查查“已確認”的狀態碼是4，那麼找狀態碼<4的訂單就可以。
然後，有一天，忽然要在兩個狀態之間加入某個中間狀態，比如“已確認”之後需要新的風險評估，通過了才可以去揀貨，怎麼辦？總不可能在3和4之間加一個3.5吧？因為這個數據字段本來就是整數型啊。
所以“有經驗”一點的程序員會改改，一開始就不按照1、2、3、4、5這樣來分配狀態碼，而是按100、200、300、400、500，留足空隙，這樣就避免了3.5的尷尬，直接給“風控系統已通過”分配350就可以了。
但這仍然不夠。如果業務忽然要求既有順序要變，比如之前“已確認”在前，“風控系統已通過”在後，現在要求“風控系統已通過”在前，“已確認”在後，該怎麼辦？350總不可能大於400呀。
如果你瞭解了collation就會發現，這是同樣的問題。數據的標識和數據的有序性應當隔離開來，標識是一套規範，有序性是另一套規範，兩者可以隨意組合。你看，Unicode字符的排序可以按照字符的編碼值來，也可以按照其它規範來——加載不同collation就是了嘛。
所以，“已下單，未支付”的代碼就可以是OUPD，“已支付“的代碼就可以是PDED，“已確認”的代碼就可以是CFMD…… 它們只用來做唯一標識，沒有任何其它意義。然後在外面定義一套順序規則，比如OUPD < PDED < CFMD，然後提供一個查詢接口，做任何比較的時候都查詢這個接口就好——實際上許多語言可以自定義compare函數來做排序，道理就在這裡。萬一將來要改業務流程，比如加入新狀態，或者更改狀態的先後順序，也只需要做一點點更改，規則查詢接口保持不變，其它地方更是保持原封不動。
最後我想補充的是，即便你有非常多的軟件開發經驗，但如果要做“國際化”的業務，仍然會面對許多想不到的問題——e、ē、é、ě、è、ê、ë的等價問題就是一例。這類問題，不親自經歷是很難想象的。
回想起來，十多年前我開始接觸這方面業務，還真的積累了一些經驗，是坐在辦公室裡寫代碼想不到，也非常有意思的問題。如果大家有興趣，下回我們接著聊。
閱讀更多 餘晟以為 的文章
