知識星球本文由leekafai在眾成翻譯上翻譯，
連結：http://www.zcfy.cc/article/a-href-title-komlenic-com-komlenic-com-a
原文http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/
MySQL的 列舉（ENUM）型別 是程式員群體中的一個討論熱點。乍一看，我們可以透過列舉型別，很好地將記錄值限制在允許範圍內。一個典型的例子是，一個具有欄位名稱為“大陸板塊”的資料表：每一個國家位於一個大陸板塊，而這些大陸板塊不太可能經常變化。當然，或許一天北美板塊會與亞洲板塊碰撞形成北美亞,但即便你的資料庫能夠延續使用到那個時候，起碼你也不需要研討怎麼去重構你的資料表，那將是當時的開發者要做的工作。
言歸正傳。如果，使用ENUM是唯一 一個，能夠代表某一個國家屬於哪個大陸板塊的選擇，那我們大可進行下一步，去爭辯諸如NoSQL的優劣、Git和SVN孰強孰弱、你喜歡的框架有哪些缺點這些其他的問題。但這裡有一個普遍適用於實現列舉的最佳實踐：
維基百科 是這樣描述關係表的:
所以，關係表也可以滿足列舉的實現。下麵就來看看，ENUM的”八宗罪“到底是什麼：
1. 資料被錯誤對待
男、女；先生、夫人、小姐；非洲、亞洲，等等。這些人們使用作為ENUM型別欄位的短詞稱為資料。當你使用一個ENUM型別欄位, 技術上看，是你將資料抽離出來 (對應到實際資料表時), 放到一個獨立的地位（一種資料庫的元資料，具有精確定義欄位）。 這不同與約束資料型別，如我們通常的做法：數值型欄位只能儲存整型資料，或者日期型欄位不能為空——這些都沒有問題，而且還十分重要。使用ENUM型別欄位時，我們實際上是儲存部分資料 去作為 這個資料模型的一個特徵資訊。簡而言之， ENUM型別欄位破壞了正規化要求。這也許看起來十分“學院派”或“迂腐陳舊”，但這正是以下各種“罪行”的源頭。
2. 更改ENUM型別欄位，代價很昂貴
永恆不變的是， 每次你建立ENUM型別欄位的時候都說：“這個欄位不可能變的”。人類普遍欠缺顧全大局的能力，預測上更是糟糕，其如研發部的新產品線、貴司新的航運方案、北美板塊碰撞亞洲板塊。
使用ALTER TABLE去修改整個資料表的ENUM型別欄位，是十分耗費資源的。如果將ENUM(‘red’, ‘blue’, ‘black’) 改為 ENUM(‘red’, ‘blue’, ‘white’), MySQL 需要重構整個資料表，並且檢索 所有資料去檢查 ‘black’這個無效值。 MySQL 是真的蠢，它確實會在你每次增加一個新的ENUM值時都這麼做的！（傳言未來會處理ENUM型別欄位的效率問題，但我對其受重視程度深表懷疑。）
全表重構在小型資料表中可能沒有那麼痛苦，但在海量資料的情況下可能會導致資源被鎖死很長很長一段時間。如果你使用關係表去替代ENUM型別欄位，改變列舉集合只不過是使用INSERT、UPDATE和DELETE，對比來看真是滑稽。
很重要的一點，當更改ENUM型別欄位的列舉集合時，MySQL會轉換任意已有但不存在於新的列舉集合中的記錄值為”（空的字串）。使用關係表，在更改和刪除列舉集合時會靈活很多（下麵會提到）。
3. 幾乎無法給關聯資料新增額外的屬性
至今都沒有一個可以更加明智地改變ENUM型別欄位的方法，這也是我們的常態。在我們的“國家、大陸板塊”例子中, 更改“國土面積”會出現什麼情況？我們沒有預料到這個屬性， 但也要既來之則安之。使用關係表設計，我們可以輕易地拓展“大陸板塊”這個資料表，各種方式為其增加我們想要的資料和欄位 。ENUM？快別說了。
另一種極妙的靈活性體現在關係表的拓展便捷性上。一個簡單的標記位欄位即可表示這個“列舉值”是否可用。所以，當你的公司不打算銷售黑色的裝飾品了，你只需在“黑色”所對應的_isdiscontinued欄位中做個標記即可。而且你依然可以查詢到已售的顏色（譯者：指的是，ENUM的修改會導致原有，而現在已經沒有的值變為空字串，資料失去了部分特徵），同時你那些黑色裝飾品的訂單依然可統可計哦！ENUM，你要不要試試？
4. 獲取ENUM全部可能值，很麻煩
一個很常見的需求是，將資料庫中存在的資料顯示在可拖拽串列中，例如：
選擇顏色：
紅 藍 黑
如果這些數值儲存在一個名為‘colors’的資料表裡，你所要做的僅僅是：SELECT * FROM colors，這樣即可動態地令資料地顯示在可拖拽串列中。你可以新增或者改變color關係表中的顏色，並且，你那酷炫訂單的顏色可選項會自動更新，真了不起。 （譯：此處所舉例子，應等同於：“透過後臺管理，可以限定前端使用者某型別資料的可選項。”這樣的功能。）
回到ENUM上：你要如何獲取全部的列舉值？你當然可以使用ENUM值搭配DISTINCT去查詢（譯：即是查詢ENUM值互相不相同的資料，等於利用DISTINCT的唯一性去查詢ENUM），但這樣也只會傳回確實使用過，並存在於資料表ENUM欄位可選值中的ENUM值，而不是所有可能的值。你也可以查詢INFORMATION_SCHEMA然後透過程式碼解析傳回的資料，去找到你想要的ENUM的所有值，但這完全是多此一舉。事實上，我依然沒有發現，有任何兼顧了優雅與原生的SQL方式，可以獲取ENUM型別欄位的所有值。
5. ENUM型別欄位所提供的最佳化有限
通常使用ENUM的正當理由，不外乎“最佳化”二字，譬如，效能提升，簡化模型與高可讀性。
那我們從效能上看。你可以在未最佳化的資料庫中做很多匪夷所思而誇張的事，但是大多情況是，在資料達到一定規模前，都不會出現影響效能的情況，並且通常我們的產品遠未達到那個尺度規模。有一點需要註意的是，因為資料庫開發者們都熱衷於令自己的設計可以達到完備的正規化，並且只會在遇到效能問題時才會考慮反正規化。如果你擔心使用關係表會導致變慢，可以在同一基準下測試不同方式下的表現，再進行考慮。切勿先入為主地認為關聯查詢會成為瓶頸，可能有時並非如此。(可參照 evidence to support that ENUM isn’t always appreciably faster than alternatives.)
另一個關於ENUM最佳化方式的說法是，ENUM可以有效減少資料庫中的資料表外來鍵。不可置否，使用外來鍵相當於是將很多不同的盒子以線相連，而且在大型系統中，正規化設計已可降低對人類的理解能力界限、複雜型查詢的要求。但是，我們為什麼會設計模型，為什麼要將模型抽象化以便我們能夠理解它。去試試做一個新資料模型圖或者ER圖，並且忽略一些小細節和關係表。有時候使用ENUM確實如看上去那般簡單，但事實上你在心裡需要想著一個隱式的關係表，所以並沒有看上去那般簡單。
6. ENUM值在其他資料表中不可直接復用
當你（在資料表中）建立了一個帶值的ENUM欄位，在其他資料表中無法直接復用這個ENUM。而當有了關係表，相同應用形式下，可以在其他多個資料表中復用。當改變關係表中的一個資料，其他多個資料表也會得到響應。
ENUM型別欄位的分離，將使你能在多個資料表中復用相同的ENUM值（需要保持一致性）。
7. ENUM型別欄位有顯然陷阱
假設你設定了一個欄位“color”ENUM(‘blue’, ‘black’, ‘red’) ，這時你想INSERT一行資料，但“color”欄位是 ‘purple’， MySQL 會將不合法的值變為 ” （空字串）。 處理上沒問題, 但如果我們使用的是帶外來鍵的關係表, 那麼我們的資料能因健壯性而更加可靠。
同樣，MySQL 會為ENUM值關聯列舉索引，並且在使用中會錯誤地呼叫到索引而不是ENUM值，反之亦然。
想象一下：
CREATE TABLE test (foobar ENUM('0', '1', '2'));
mysql> INSERT INTO test VALUES ('1'), (1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+--------+
| foobar |
+--------+
| 1 |
| 0 |
+--------+
2 rows in set (0.00 sec)
我們插入了 ‘1’ (字串)，並且不小心插入了 1 (沒有引號，數值型)。 MySQL 會將我們地數值型資料當作是列舉索引去處理（並沒有錯，但會令人混淆），根據索引可知，ENUM欄位的第一個值為 0 。（譯：列舉索引由 1 開始）
8. ENUM 的移植性不佳
ENUM型別不是SQL標準，屬於MySQL，而其他DBMS不一定有原生的支援。 PostgreSQL, MariaDB,與Drizzle (後面那兩個就MySQL的分支), 我只知道這三個是支援的ENUM的。如果某個人打算將資料庫遷移, 那麼他就要花費更多的步驟去處理你那些“精妙”的ENUM欄位了，相信他會“更愛你”。如果（那個人）是你, 你可會發現自己當時真是“聰明夠了”。通常來說，資料庫遷移不會經常發生，並且，由於所有人都會假設遷移資料庫的過程中，必然要出亂子，因此成為“第八宗罪”。
幾時適合使用ENUM:
1. 當你需要儲存的是準確、不變的值時
大陸板塊就是最好的例子，定義十分準確。另一個常用例子是稱謂：先生、夫人、小姐，或者是撲克的花色：方塊、梅花、紅心、黑桃。但是，即便是這些例子，有時也需要去拓展值的範圍（例如有人需要你稱呼“陳醫生”而不是“陳先生”的時候，或者你的撲克遊戲裡面需要用到小丑牌）。
2. 你永遠不需要儲存額外的關聯資訊
用回撲克牌的例子。撲克遊戲老少咸宜，依賴的規則是梅花和黑桃為黑色，方塊和紅心為紅色（例如，尤克牌）。如果我們需要為花色關聯額外的資訊，例如顏色，那將如何？如果我們使用關係表，那我們只需要在關係表中新增欄位即可，小事一樁。如果我們使用ENUM去表示花色，那我們就很難去準確的表示花色於顏色的關聯了，如此我們只能在應用層上去達成這種關聯。
3. ENUM值的數量大於2個並少於20個
如果你的ENUM值只有兩個，你完全可以將ENUM換成更加高效的TINYINT(1)或者更更高效的BIT(1)（MySQL5.0.3及以上）。例如: gender ENUM(‘male’, ‘female’) 可以變換為: is_male BIT(1). 當你只有兩個選項時，完全能以布林值 true/false，結合欄位名字中的“is”關鍵詞來區分。至於20個的上限設定，沒錯，ENUM事實上可以儲存多達65535個值，但求你千萬別試。超過二十個值會變得很累贅，超過50個必然難於管理與使用。
如果你無論如何都要用ENUM:
1. ENUM值千萬不要使用數值型
ENUM定義為字元型資料是有原因的。並不是說你使用數值型欄位型別去儲存數字是錯誤的，但有充足的證據顯示，MySQL內部機制使用數字去取用索引（參考 上面的第七條）。反正不要在ENUM中儲存數字，OK？
2. 考慮使用嚴格樣式
啟用嚴格樣式，至少在你插入一個不存在的ENUM值時會報告錯誤。否則，只會簡單地出現一個警告，繼而該值被設定為一個空字串“”（列舉索引為0）。抄筆記：如果你設定了IGNORE，錯誤依然會被忽略。
結論
從開發、維護的角度去做有意義的事，效能問題出現時再考慮最佳化——普遍而言，使用關係表抑或是使用ENUM型別，爭議不斷。
●本文編號298，以後想閱讀這篇文章直接輸入298即可
●輸入m獲取文章目錄
演演算法與資料結構
更多推薦《18個技術類微信公眾號》
涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。
