A2-04-07.MySQL DATA TYPES-A Comprehensive Guide to Using MySQL ENUM

转载自：http://www.mysqltutorial.org/mysql-enum/

Home / Basic MySQL Tutorial / A Comprehensive Guide to Using MySQL ENUM

A Comprehensive Guide to Using MySQL ENUM

 

Summary: in this tutorial, you will learn how to use MySQL ENUM data type for defining columns that store enumeration values.

Introduction to MySQL ENUM data type

In MySQL, an ENUM is a string object whose value is chosen from a list of permitted values defined at the time of column creation.

The ENUM data type provides the following advantages:

• Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values.
• Readable queries and output.

To define an ENUM column, you use the following syntax:

 

1 2 3 4 5

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

In this syntax, you can have more than three enumeration values. However, it is a good practice to keep the number of enumeration values under 20.

Let’s see the following example.

Suppose, we have to store ticket information with the priority: low, medium, and high. To assign the priority column the ENUM type, you use the following CREATE TABLE statement:

 

1 2 3 4 5

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

The priority column will accept only three values Low, Medium and High. Behind the scenes, MySQL maps each enumeration member to a numeric index. In this case, Low, Medium, and High are map to 1, 2 and 3 respectively.

Inserting MySQL ENUM values

To insert data into an ENUM column, you use the enumeration values in the predefined list. For example, the following statement inserts a new row into the tickets table.

 

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

Besides the enumeration values, you can use the numeric index of the enumeration member for inserting data into an ENUM column. For instance, the following statement inserts a new ticket with the Low priority:

 

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

In this example, instead of using the Low enumeration value, we used value 1. Since Low is mapped to 1, it is acceptable.

Let’s add some more rows to the tickets table:

 

1 2 3

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

Because we defined the priority as a NOT NULL column, when you insert a new row without specifying the value for the priority column, MySQL will use the first enumeration member as the default value.

See the following statement:

 

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

In the non-strict SQL mode, if you insert an invalid value into an ENUM column, MySQL will use an empty string '' with the numeric index 0 for inserting. In case the strict SQL mode is enabled, trying to insert an invalid ENUM value will result in an error.

Note that an ENUM column can accept NULL values if it is defined as a null-able column.

Filtering MySQL ENUM values

The following statement gets all high priority tickets:

 

1 2 3 4 5 6

SELECT     * FROM     tickets WHERE     priority = 'High';

Because the enumeration member ‘High’ is mapped to 3, the following query returns the same result set:

 

1 2 3 4 5 6

SELECT     * FROM     tickets WHERE     priority = 3;

 

Sorting MySQL ENUM values

MySQL sorts ENUM values based on their index numbers. Therefore, the order of member depends on how they were defined in the enumeration list.

The following query selects the tickets and sorts them by the priority from High to Low:

 

1 2 3 4 5

SELECT     title, priority FROM     tickets ORDER BY priority DESC;

It’s always a good practice to define the enumeration values in the order that you want to sort when you create the column.

MySQL ENUM disadvantages

MySQL ENUM has the following disadvantages:

1. Changing enumeration members requires rebuilding the entire table using the ALTER TABLEstatement, which is expensive in terms of resources and time.
2. Getting the complete enumeration list is complex because you need to access the information_schema database:
   
    

   1 2 3 4 5 6 7

   SELECT     column_type FROM     information_schema.COLUMNS WHERE     TABLE_NAME = 'tickets'         AND COLUMN_NAME = 'priority';

3. Porting to other RDBMS could be an issue because ENUM is not SQL-standard and not many database system support it.
4. Adding more attributes to the enumeration list is impossible. Suppose you want to add a service agreement for each priority e.g., High (24h), Medium (1-2 days), Low (1 week), it is not possible with ENUM. In this case, you need to have a separate table for storing priority list e.g., priorities(id, name, sort_order, description) and replace the priority field in the tickets table by priority_idthat references to the id field of the priorities table.
5. Comparing to the look-up table (priorities), an enumeration list is not reusable. For example, if you want to create a new table named tasks and want to reuse the priority list, it is not possible.

In this tutorial, we have introduced you to MySQL ENUM data type and how to use it for defining columns that store enumeration values.