# 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

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

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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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

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