ENUM Type

ENUM is a list of strings, and ENUM is used to store a predefined set of discrete values. It can define a type with discrete values, each enumeration constant represents a specific value.

The ENUM data type column is suitable for storing a limited number of fixed values such as state and identification.

The ENUM data type has the following advantages:

Column values are more readable.
Compact data storage. When MatrixOne stores ENUM, only the numeric index (1, 2, 3, …) corresponding to the enum value.

Syntax Structure

ENUM ('value1', 'value2', ..., 'valuen')

For example, to define an ENUM column, you can use the following syntax:

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

Syntax Explanation

ENUM is a keyword used to declare an enum type.
value1 to value is an optional list of this ENUM type, and the values of columns using ENUM type can only be one of the above values.
The enum value can be of type string, int, or time.

Note: In the ENUM data type, you can have multiple enum values. However, it is recommended to keep the number of enum values below 20.

Example explanation

Values of type ENUM must be selected from a predefined list of values, and the following examples will help you understand:

CREATE TABLE enumtable (
    id INT NOT NULL AUTO_INCREMENT,
    color ENUM('red', 'green', 'blue'),
    PRIMARY KEY (id)
);

The above statement will create a table named enumtable containing an enum type field named color. The value of the color field must be one of red, green, or blue. At the same time, in the order when the column is defined, the indexes of red, green and blue are 1, 2, and 3, respectively.

Insert ENUM value

When inserting data into fields of enum type, only predefined enum values or NULL can be inserted. If the inserted value is not in the predefined list, an error occurs. For example:

INSERT INTO enumtable (id, color) VALUES ('01', 'red');
-- 'red' In the predefined list, insertion is successful
INSERT INTO enumtable (id, color) VALUES ('02', 'yellow');
-- 'yellow' is not in the predefined list, an error will be reported
INSERT INTO enumtable (id, color) VALUES ('03', NULL);
-- The enumeration member is not defined not null, the insertion is successful

In addition to enumeration values, you can also insert data into ENUM columns using the numeric index of the enumeration member. For example:

INSERT INTO enumtable (id, color) VALUES ('04', 2);
-- Since the index of `green` is 2, this data is inserted successfully

-Restrictions on ENUM by non-null constraints

If we define the color column NOT NULL when creating a table:

CREATE TABLE enumtable (
    id INT NOT NULL AUTO_INCREMENT,
    color ENUM('red', 'green', 'blue') NOT NULL,
    PRIMARY KEY (id)
);

When inserting a new row without specifying the value of the color column, MatrixOne uses the first enum member as the default value:

INSERT INTO enumtable (id) VALUES ('05');
-- Here, the first enumeration member `red` will be assigned to the column with id 05.

Differences from MySQL

Unlike MySQL, the ENUM type of MatrixOne can only be compared with string type in the WHERE condition.

See this example:

update orders set status= 2 where status='Processing';`

In this example, you need to update the status of the line with processing to 2. Due to the nature of the ENUM type, in the WHERE condition, MatrixOne implicitly converts 2 to the string 2 and then compares it with Processing.

limit

Currently modifying ENUM enumeration members requires the table to be reconstructed using the ALTER TABLE statement.