前段時間，遇到一個mysql的問題，我仔細(xì)看看報錯信息，應(yīng)該是MySQL數(shù)據(jù)庫報出來的，大意是說：collation不兼容，一個是?utf8mb4_0900_ai_ci，另一個是utf8mb4_general_ci。

utf8mb4_general_ci這玩意兒我見過，是針對utf8mb4編碼的collation，但是utf8mb4_0900_ai_ci是啥，我也沒見過。

于是我問他，這玩意兒從哪里出來的？

他說：“我也不知道，我完全沒見過啊。再說，我數(shù)據(jù)庫編碼已經(jīng)是utf8mb4了，怎么還會有這么多名堂？”

看他著急又不知所措的樣子，我便花了點時間來研究，還真學(xué)到點新知識。而且我也發(fā)現(xiàn)，有許多程序員天真的以為“用了UTF8就等于做了國際化了，不用再擔(dān)心編碼問題”?？磥?，這個話題還真值得多講講。

首先從utf8mb4_0900_ai_ci這個詭異的名字說起。

Unicode編碼的誕生，是為了解決之前各國的計算機(jī)文字編碼自成一體的問題。不同國家采用不同的編碼，自己用還算正常，但是跨文化交流必然會出問題，更無法解決“在同一篇文檔里又要顯示中文又要顯示韓文還要顯示日文”之類的問題。

有了Unicode，地球上所有的文字都有獨一無二的編碼(Code Point，也就是為它分配的碼值，或者說“邏輯代號”)，前述問題就解決了。

但是Unicode(有個相關(guān)的名字是UCS，Universal Coded Character Set，二者基本等價)只確定了碼值，或者說，只分配了邏輯代號。至于這些邏輯代號在實際使用中如何存儲，如何傳輸，那是另一個問題。而UTF-8，就是解決存儲和傳輸?shù)葐栴}的“實際方案”。

實際上，UTF的全名是Unicode Transformation Format，也就是“Unicode變換格式”。這里的“變換”，基本可以類比為：要告訴別人明天早上九點來開會，到底是發(fā)郵件呢，還是打電話呢，還是寫紙條呢，還是直接去敲門打招呼呢？。

所以，Unicode的變換格式不只UTF-8一種，還有UTF-16、UTF-32等等。UTF-8使用比較普遍，因為它是變長編碼，如果只傳輸ASCII字符，則每個字符只需要一個字節(jié)。因此，如果數(shù)據(jù)中包含大量的ASCII字符，那么UTF-8可以節(jié)省很多存儲空間。

老一點的程序員大概都知道UTF-8，在MySQL中寫作utf8，沒有橫線。如果要用MySQL存儲多種語言的字符，那么把字符集(character set)設(shè)定為utf8是合適的選擇。注意，MySQL中必須指定utf8，而不是Unicode。因為Unicode只是邏輯規(guī)范，utf8才是具體存儲和傳輸?shù)母袷健?/p>

那么，utf8mb4_0900_ai_ci什么意思呢？

我們分部分來看這個名字，先從開頭看起。

utf8mb4，這個名字許多人大概熟悉。如今?????emoji表情已經(jīng)大量使用，但MySQL之前的的字符集(character set)是utf8(更準(zhǔn)確的名字是utf8mb3，一個字符最多使用3個字節(jié)來存儲)，只能存儲編碼值從0x000到0xFFFF之間的字符。

然而，emoji表情字符的碼值超過了0xFFFF，按照UTF-8規(guī)范，存儲時需要用4個字節(jié)。正因為如此，MySQL才提供了utf8mb4的字符集。如果把數(shù)據(jù)庫表的字符集設(shè)定為utf8mb4，就可以正常存儲包含表情字符的文本了。

UTF-8編碼圖解，來源：維基百科

中間的0900，它對應(yīng)的是Unicode 9.0的規(guī)范。要知道，Unicode規(guī)范是在不斷更新的，每次更新既包括擴(kuò)充，也包括修正。比如6.0版新加入了222個中日韓統(tǒng)一表義字符(CJK Unified Ideographs)，7.0版加入了俄國貨幣盧布的符號等等。

如果支持新的Unicode規(guī)范，就可以直接享受好處，像對待普通字符那樣對待這些新字符，當(dāng)然是好事。

以前的MySQL雖然也會跟隨Unicode的更新，但速度太慢了。MySQL 5.7的第一個發(fā)行版MySQL 5.7.1是2013年4月23日面世的，它包含的最新的Unicode規(guī)范是Unicode 5.2，發(fā)布于2009年10月。即便是2020年1月13日發(fā)布的MySQL 5.7.29，仍然是這樣。

然而Unicode規(guī)范早已升級了很多版，即便是9.0版本，也發(fā)布于2016年6月，過去了好多年了。到目前為止，最新的版本已經(jīng)到了12.1，發(fā)布于2019年5月。所以從5.2更新到9.0，看起來是一大進(jìn)步，其實也只是補課而已。

Unicode在不斷更新，來源：維基百科

最后兩部分_ai_ci，ai表示accent insensitivity，也就是“不區(qū)分音調(diào)”，而ci表示case insensitivity，也就是“不區(qū)分大小寫”。

所以，utf8mb4_0900_ai_ci到底是個什么東西呢？其實，它是個collation。

說起“字符集”，許多人想當(dāng)然認(rèn)為，給每個字符分配了一個編碼，并且能存儲、能傳輸，這就夠了。其實這當(dāng)然不夠，我們不但需要給每個字符分配編碼，讓它們能存儲、能傳輸，還需要定義一套關(guān)系來組織它們，找到它們之間的聯(lián)系。這套關(guān)系的定義，就是collation。

collation定義了哪個字符和哪個字符是“等價”的。所以如果指定“不區(qū)分大小寫”，那么a和A，e和E就是等價的，這樣查找時就會方便很多。但這還不夠，世界上的文字很多，所以才會有“不區(qū)分音調(diào)”的要求，這時候e、ē、é、ě、è就是等價的，那么假設(shè)我們要進(jìn)行拼音查找，只要按e去找就可以全部列出來，很方便。甚至，它們也和ê、?也是等價的，這樣就更方便了。

collation也定義了字符的排序規(guī)則，如果按照“字符順序(而不是簡單的‘字母順序’)”來排序，哪個字符應(yīng)當(dāng)排在哪個字符前面。所以，盡管“啊”、“副”、“德”三個字的拼音開頭分別為A、F、D，但直接選定collation為utf8mb4，它們并不會按照“啊”、“德”、“副”的順序排序，而是會排成“副”、“啊”、“德”。如果你希望把中文字符按照拼音來排序，指定使用gb18030_chinese_ci作為collation就可以了。

當(dāng)然，要補充的是，collation依賴于字符集(character set)，所以把gb18030_chinese_ci作為collation，就要求字符集是gb18030，而不能是utf8mb4。

這也很好理解，字符集定義了可以使用的字符，對應(yīng)的collation定義了字符之間的關(guān)系。如果collation不依賴于字符集，那么很可能出現(xiàn)“有些字符沒有關(guān)系定義，不知如何判斷等價和順序”的問題。

到這里，我的疑惑就解開了。MySQL 8.0之后，默認(rèn)collation不再像之前版本一樣是是utf8mb4_general_ci(這個名字也確實取得有問題，話說得太滿，有點自負(fù)了)，而是統(tǒng)一更新成了utf8mb4_0900_ai_ci。

不幸的是，我之前建的各種數(shù)據(jù)表，它們的collation仍然是utf8mb4_general_ci，而新建的表是utf8mb4_0900_ai_ci。如果恰好遇到包含字符串相等或者大小比較的聯(lián)表查詢語句，而關(guān)聯(lián)的表又使用了不同的collation，MySQL就無法決策到底應(yīng)當(dāng)使用哪個，就會報錯。

既然如此，解決辦法也很簡單，用alter table table_name collate utf8mb4_0900_ai_ci顯式統(tǒng)一所有表的collation，問題就解決了。

我們可以多想想，把character set和collation分開，到底有什么好處？其實好處很多。如果把字符看作個人，character set就相當(dāng)于驗明正身，給每個字符發(fā)張身份證，而collation相當(dāng)于告訴大家，排隊的時候誰在前誰在后。collation有多套，就相當(dāng)于可以靈活按身高、體重、年齡、出身地等等因素來排序，卻完全不會受到身份證號的干擾。

實際上collation也是如此，既然有utf8mb4_0900_ai_ci，就還有utf8mb4_0900_as_ci和utf8mb4_0900_as_cs?？疵忠部梢灾?，utf8mb4_0900_as_ci表示“區(qū)分音調(diào)”，所以e、ē、é、ě、è就不再是等價的；而utf8mb4_0900_as_cs表示“區(qū)分大小寫”，所以查e的時候就不會把E查出來。

這個問題本來不麻煩，為什么會難住人呢？原因不復(fù)雜，你去看關(guān)于MySQL和Unicode的中文資料，絕大部分都是告訴你，utf8或者utf8mb4就可以解決問題了。因此，不少程序員完全意識不到還有collation這種東西。

所以，這些程序員理解的“字符集”就只有一堆孤零零的字符，根本沒想到還需要定義字符之間的等價和排序關(guān)系。而這恰恰是最可惜的，因為他們完全錯過了“舉一反三”的啟發(fā)，許多類似問題也就缺乏解決思路。要知道，哪怕你做的不是國際化的業(yè)務(wù)，也可以從collation中受益的。

我們都知道，電商系統(tǒng)的訂單處理是一個流程，其中涉及許多狀態(tài)，比如“已下單，未支付”、“已支付”、“已確認(rèn)”、“已揀貨”、“已發(fā)貨”等等。

有程序員看到這個需求，想當(dāng)然就按照先后順序，用1、2、3、4、5來表示對應(yīng)狀態(tài)，確實簡單不會出錯，也方便先后對比，比如要查找所有“已確認(rèn)”之前的訂單，就查查“已確認(rèn)”的狀態(tài)碼是4，那么找狀態(tài)碼<4的訂單就可以。

然后，有一天，忽然要在兩個狀態(tài)之間加入某個中間狀態(tài)，比如“已確認(rèn)”之后需要新的風(fēng)險評估，通過了才可以去揀貨，怎么辦？總不可能在3和4之間加一個3.5吧？因為這個數(shù)據(jù)字段本來就是整數(shù)型啊。

所以“有經(jīng)驗”一點的程序員會改改，一開始就不按照1、2、3、4、5這樣來分配狀態(tài)碼，而是按100、200、300、400、500，留足空隙，這樣就避免了3.5的尷尬，直接給“風(fēng)控系統(tǒng)已通過”分配350就可以了。

但這仍然不夠。如果業(yè)務(wù)忽然要求既有順序要變，比如之前“已確認(rèn)”在前，“風(fēng)控系統(tǒng)已通過”在后，現(xiàn)在要求“風(fēng)控系統(tǒng)已通過”在前，“已確認(rèn)”在后，該怎么辦？350總不可能大于400呀。

如果你了解了collation就會發(fā)現(xiàn)，這是同樣的問題。數(shù)據(jù)的標(biāo)識和數(shù)據(jù)的有序性應(yīng)當(dāng)隔離開來，標(biāo)識是一套規(guī)范，有序性是另一套規(guī)范，兩者可以隨意組合。你看，Unicode字符的排序可以按照字符的編碼值來，也可以按照其它規(guī)范來——加載不同collation就是了嘛。

所以，“已下單，未支付”的代碼就可以是OUPD，“已支付“的代碼就可以是PDED，“已確認(rèn)”的代碼就可以是CFMD…… 它們只用來做唯一標(biāo)識，沒有任何其它意義。然后在外面定義一套順序規(guī)則，比如OUPD < PDED < CFMD，然后提供一個查詢接口，做任何比較的時候都查詢這個接口就好——實際上許多語言可以自定義compare函數(shù)來做排序，道理就在這里。萬一將來要改業(yè)務(wù)流程，比如加入新狀態(tài)，或者更改狀態(tài)的先后順序，也只需要做一點點更改，規(guī)則查詢接口保持不變，其它地方更是保持原封不動。

最后我想補充的是，即便你有非常多的軟件開發(fā)經(jīng)驗，但如果要做“國際化”的業(yè)務(wù)，仍然會面對許多想不到的問題——e、ē、é、ě、è、ê、?的等價問題就是一例。這類問題，不親自經(jīng)歷是很難想象的。文章來源地址http://www.zghlxwxcb.cn/news/detail-496776.html

到了這里，關(guān)于mysql設(shè)置了utf8mb4，為什么還有utf8mb4_general_ci和utf8mb4_0900_ai_ci？的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！