使用 MySQL ENUM 作為列的數據類型有兩個主要的好處，一是使得數據更緊湊進而節省空間，二是更好的可閱讀性。但是，享受這些好處的同時，也要承擔 ENUM 所帶來的各種負面影響。

我們羅列幾個主要的負面在此，并在接下來的內容中對它們做一一解釋千萬不要使用數字作為枚舉值，因為這樣容易混淆它們的字面值和內部索引值。

在 ORDER BY 語句中使用 ENUM 更要注意

創建和使用 ENUM 數據類型的一些問題

枚舉值字面量和內部索引的問題

處理枚舉值字面量的一些問題

ENUM 類型中的 NULL 或空值問題

ENUM 類型的排序問題

ENUM 類型的一些限制

創建和使用 ENUM 數據類型

如果要將某一列指定為 ENUM 類型，可以使用 ENUM 關鍵字，且每個枚舉值都需要使用單引號 ( ' ) 引起來，例如下面的建表語句

CREATE TABLE shirts (

name VARCHAR(40),

size ENUM('x-small', 'small', 'medium', 'large', 'x-large')

);

創建了表之后，我們就可以往表中插入值了，對于 ENUM 類型，值原則上來說必須是定義表結構所指定的枚舉值之一

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),

('polo shirt','small');

數據插入成功后，我們就可以使用 SQL SELECT 語句來查詢數據

SELECT name, size FROM shirts WHERE size = 'medium';

結果如下

+---------+--------+

| name | size |

+---------+--------+

| t-shirt | medium |

+---------+--------+

如果要更新 ENUM 列，所傳遞的值也必須是定義時的枚舉值之一

UPDATE shirts SET size = 'small' WHERE size = 'large';

如果在插入和更新值時所傳遞的值并不在枚舉值列表中，那么就會拋出上一章節中開始那部分的問題。

ENUM 枚舉值字面量和內部索引

ENUM 類型中的任何一個枚舉值都有一個內部的數字索引：所以在創建表結構時指定的枚舉值都會分配一個內部索引，索引的下標從 1 開始

注意：下標并不是從 0 開始，而 0 則具有其它的意義

空字符串錯誤值的索引為 0，這樣，我們可以直接使用 0 值來查詢那些插入的或更新的無效的枚舉值

SELECT * FROM tbl_name WHERE enum_col=0;

NULL 值的索引為 NULL

ENUM 最多只能包含 65,535 個不同的枚舉值

當然了，這里的術語 「 索引 」 指的是枚舉值列表中的位置。它與表索引無關。

我們使用一個范例來解釋下上面的幾條規則，例如，假設某一列的類型為 ENUM('Mercury', 'Venus', 'Earth') ，那么該列存儲的實際值則為

枚舉值 索引

NULL NULL

'' 0

'Mercury'1

'Venus' 2

'Earth' 3

如果在數字上下文中檢索 ENUM 值，則返回列值的索引。例如，我們可以像這樣從 ENUM 列中檢索數值

SELECT enum_col+0 FROM tbl_name;

當在 ENUM 列上使用 SUM() 或 AVG() 等聚合函數時，因為這些函數的參數必須是一個數字，所以 MySQL 會自動使用它們的索引值作為參數。也就是說，對于需要計算的場景，都會使用內部索引。其實，真實的枚舉值，只有在插入或者顯示或者查詢時才會用到。

ENUM 字面量的處理

在創建表結構時，MySQL 會自動刪除 ENUM 枚舉值的尾隨空格，例如會把 'medium ' 轉換成 'medium'。

檢索時，MySQL 會自動將存儲的內部索引轉換為定義時指定的相應的 enum 枚舉值字面量。

因此，需要注意的是，可以為 ENUM 列分配字符集和排序規則。對于二進制或區分大小寫的排序規則，在為列分配值時會考慮使用字母順序。

如果將數字存儲到 ENUM 列中，則將該數字視為可能值的索引，并且存儲的值是具有該索引的枚舉成員 (當然了，這條規則對 LOAD DATA 無效，因為 LOAD DATA 會把所有的值都視為字符串 )。

如果引用了數值，即使枚舉值列表中沒有匹配的字符串，但它仍會被解釋為索引。

因為這個原因，所以，不建議使用看起來像數字的枚舉值來定義 ENUM 列，因為這很容易讓人感到困惑，分不清傳遞(引用) 的到底是枚舉值字面量還是內部索引。

例如，以下列的枚舉成員的字符串值為 '0'、'1' 和 '2'，而數字索引值為 1 、2 和 3

numbers ENUM('0','1','2')如果我們在插入數據或者更新數據時指定存儲 2 ，因為會被解釋為索引值，所以實際存儲的枚舉值為 '1' ( 索引為 2 的值 )。

而如果我們存儲 '2' ，因為枚舉值字面量 '2' 存在，所以存儲的值也為 2 。

但如果我們存儲 '3' ，因為枚舉值字面量 '3' 并不存在，那么它就會被視為是內部索引 3 ，進而存儲的實際值其實是 '2'

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');

mysql> SELECT * FROM t;

+---------+

| numbers |

+---------+

| 1 |

| 2 |

| 2 |

+---------+

如果要確定 ENUM 列的所有可能值，SHOW COLUMNS FROM tbl_name LIKE 'enum_col' 語句可以解析出 enum_col 列中的所有 enum 定義

ENUM 中的 NULL 或空 '' 值問題

在某些情況下，ENUM 數據類型的枚舉值也可以是空字符串( '' )或 NULL如果在 ENUM 列中插入無效值(即，允許值列表中不存在的字符串)，則會插入空字符串 ( '' ) 作為特殊錯誤值，這個特殊錯誤值空字符串的索引為 0 ，從而與實際的 正常 的空字符串 ( 索引大于 1 ) 區分開來

當然了，如果啟用了嚴格的 SQL 模式 ( sql_mode ) ，嘗試插入無效的 ENUM 值會導致錯誤

如果一個 ENUM 列添加了 NULL 約束，那么這個 ENUM 列就允許 NULL 值，且默認的值就是 NULL

如果一個 ENUM 列添加了 NOT NULL 約束，那么它的默認值就是第一個枚舉值。

ENUM 枚舉值的排序問題

因為 ENUM 類型存儲的是枚舉值的內部索引，所以 ENUM 值根據其索引號進行排序，具體顯示出來，則取決于定義列是的枚舉成員順序。

例如，如果在定義列時，指定了 'b' 在 'a' 前面 ('b'，'a')，那么 'b' 的順序就會在 'a' 之前，且空字符串在非空字符串之前排序，NULL 值在所有其他枚舉值之前排序

也就是排序的順序默認是 NULL '' 'b' 'a'

這是一個大坑啊，為了避免這個坑，為了在 ENUM 列上使用 ORDER BY 子句時防止出現意外結果，則需要做如下選擇指定 ENUM 列的排序順序使用字母順序表

或者使用 ORDER BY CAST (col AS CHAR) 或 ORDER BY CONCAT(col) 確保 enum 列按詞法排序而不是索引編號排序

ENUM 數據類型的一些限制枚舉值不能是表達式，即使該表達式用于計算字符串值。

例如，下面的建表語句是無效的，會執行失敗，因為 CONCAT()函數不能用于構造枚舉值

CREATE TABLE sizes ( size ENUM('small', CONCAT('med','ium'), 'large') );

不能使用用戶變量作為枚舉值。例如下面的語句也是無效的

SET @mysize = 'medium'; CREATE TABLE sizes ( size ENUM('small', @mysize, 'large') );

我們強烈建議不要使用數字用作枚舉值，因為它不會通過適當的 TINYINT 或 SMALLINT 類型保存在存儲上。而且，如果你錯誤地引用 ENUM 值，很容易混淆枚舉字面量和底層索引值 ( 可能不相同 )

ENUM 列定義中的重復值會導致警告，如果啟用了嚴格的 SQL 模式，則會出錯臭名昭著的 MySQL ENUM 類型 ( 下 )?www.twle.cn

總結

以上是生活随笔為你收集整理的mysql 枚举_是否推荐使用MySQL的enum类型？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。