MySQL enum型別

2019-10-16 22:58:43

在本教學中,您將學習如何使用MySQL ENUM資料型別定義儲存列舉值的列。

MySQL ENUM資料型別簡介

在MySQL中,ENUM是一個字串物件,其值是從列建立時定義的允許值列表中選擇的。

ENUM資料型別提供以下優點:

  • 緊湊型資料儲存,MySQL ENUM使用數位索引(1,2,3,…)來表示字串值。
  • 可讀查詢和輸出。

要定義ENUM列,請使用以下語法:

CREATE TABLE table_name (
    ...
    col ENUM ('value1','value2','value3'),
    ...
);

在這種語法中,可以有三個以上的列舉值。但是,將列舉值的數量保持在20以下是一個很好的做法。

下面來看看看下面的例子。

假設我們必須儲存優先順序為:low, mediumhigh 的票據資訊。 要將priority列分配給ENUM型別,請使用以下CREATE TABLE語句:

USE testdb;

CREATE TABLE tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    priority ENUM('Low', 'Medium', 'High') NOT NULL
);

priority列只接受三個Low, Medium, High值。 在後台,MySQL將每個列舉成員對映到數位索引。在這種情況下,LowMediumHigh分別對映到1,23(注意:與陣列不同,這不是從0開始的)。

插入MySQL ENUM值

要將資料插入ENUM列中,可以使用預定義列表中的列舉值。 例如,以下語句在tickets表中插入一個新行。

INSERT INTO tickets(title, priority)
VALUES('Scan virus for computer A', 'High');

除了列舉值之外,還可以使用列舉成員的數位索引將資料插入ENUM列。 例如,以下語句插入優先順序為Low的新機票資料:

INSERT INTO tickets(title, priority)
VALUES('Upgrade Windows OS for all computers', 1);

在這個例子中,我們使用的值為:1,而不是使用Low列舉值,因為Low被對映到1,這是可以接受的。

我們再向ticketa表新增一些行資料:

INSERT INTO tickets(title, priority)
VALUES('Install Google Chrome for Mr. John', 'Medium'),
      ('Create a new user for the new employee David', 'High');

因為我們將優先順序定義為NOT NULL列,當插入新行而不指定優先順序列的值時,MySQL將使用第一個列舉成員作為預設值。

參見下列語句宣告:

INSERT INTO tickets(title)
VALUES('Refresh the computer of Ms. Lily');

在非嚴格的SQL模式下,如果在ENUM列中插入無效值,MySQL將使用空字串'',插入數位索引為0。 如果啟用了嚴格的SQL模式,嘗試插入無效的ENUM值將導致錯誤。

請注意,如果ENUM列定義為可空列,則可以接受NULL值。

過濾MySQL ENUM值

以下語句查詢獲得所有高優先順序機票:

SELECT 
    *
FROM
    tickets
WHERE
    priority = 'High';

執行上面查詢語句,得到以下結果 -

+----+----------------------------------------------+----------+
| id | title                                        | priority |
+----+----------------------------------------------+----------+
|  1 | Scan virus for computer A                    | High     |
|  4 | Create a new user for the new employee David | High     |
+----+----------------------------------------------+----------+
2 rows in set

由於列舉成員’High‘對映到3,因此以下查詢返回相同的結果集:

SELECT 
    *
FROM
    tickets
WHERE
    priority = 3;

也可以使用比較運算子來查詢,比如 -

SELECT 
    *
FROM
    tickets
WHERE
    priority >= 2;

排序MySQL ENUM值

MySQL根據索引號排序ENUM值。 因此,成員的順序取決於它們在列舉列表中的定義。

以下查詢選擇門票並按優先順序從高到低進行排序:

SELECT 
    title, priority
FROM
    tickets
ORDER BY priority DESC;

執行上面查詢語句,得到以下結果 -

+----------------------------------------------+----------+
| title                                        | priority |
+----------------------------------------------+----------+
| Scan virus for computer A                    | High     |
| Create a new user for the new employee David | High     |
| Install Google Chrome for Mr. John           | Medium   |
| Upgrade Windows OS for all computers         | Low      |
| Refresh the computer of Ms. Lily             | Low      |
+----------------------------------------------+----------+
5 rows in set

在建立列時,按順序定義列舉值是一個很好的做法。

MySQL ENUM的缺點

MySQL ENUM有以下缺點:

  • 更改列舉成員需要使用ALTER TABLE語句重建整個表,這在資源和時間方面是昂貴的。
  • 獲取完整的列舉列表很複雜,因為需要存取information_schema資料庫:
    SELECT 
      column_type
    FROM
      information_schema.COLUMNS
    WHERE
      TABLE_NAME = 'tickets'
          AND COLUMN_NAME = 'priority';
    
  • 遷移到其他RDBMS可能是一個問題,因為ENUM不是SQL標準的,並且資料庫系統不支援它。
  • 向列舉列表新增更多屬性是不可能的。假設您要為每個優先順序新增服務協定,例如High(24h)Medium(1-2天)Low(1週),則不可以使用ENUM型別的。 在這種情況下,需要有一個單獨的表來儲存優先順序列表,例如priority(id,name,sort_order,description),並且通過參照了priority表的id欄位的priority_id來替換tickets表中的priority欄位。
  • 與查詢表(priorities)相比,列舉列表不可重用。 例如,如果要建立一個名為tasks並且要重用優先順序列表的新表,則是不可能的。

在本教學中,我們向您介紹了MySQL ENUM資料型別以及如何使用它來定義儲存列舉值的列。