How to Set ENUM Value in MySQL?

The “ENUM” (short form of “Enumeration”) is a data type available in MySQL to define a list of possible values for a column. Each value of “ENUM” uses numeric indexes to represent string values, and these indexes start from “1”. To set the “ENUM” values, the user has to define the data type of the column at the time of table creation and also include a list of all possible values enclosed inside square brackets. The User can specify “65,535” distinct values in an “ENUM” column.

What is the Syntax of “ENUM” in MySQL?

The syntax for “ENUM” is given below:

[Column-name] ENUM([value1], [value2], [value3], ...)

Note: The values of “ENUM” should be placed inside the quoted string literal.

Create a Table With Single Column Containing “ENUM” Values

Create a table named “schedule” with three columns “id”, “day,” and “activity”. The details of the column include:

The “id” column is the primary key having auto-incremented integer values
The “activity” column has data type “VARCHAR” with a maximum size of “50” characters
The “day” column has data type “ENUM” and stores all the specified values this column can store.

Type this query for creating this table:

CREATE TABLE schedule (

id INT AUTO_INCREMENT PRIMARY KEY,

day ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'),

activity VARCHAR(50)

);

The message will display as “Query OK” after creating the table successfully:

Let’s insert values in this table, the values of “ENUM” can be provided with the values of other columns in the “INSERT” statement. Run this query for inserting “ENUM” values along with values of the “activity” column:

INSERT INTO schedule (day, activity) VALUES

('Monday', 'Work'),

('Tuesday', 'Gym'),

('Wednesday', 'Meetings'),

('Thursday', 'Work'),

('Friday', 'Happy hour'),

('Saturday', 'Relax'),

('Sunday', 'Family time');

The output is displaying a message that “Query OK, 7 rows affected”, it means values are inserted successfully:

To confirm that values are inserted in the “schedule” table, run this query to see data of the table:

select * from schedule;

The output is showing the “schedule” table containing the “ENUM” values along with the “activity” and “id” values:

Let’s see another example to use the numeric index while inserting the “ENUM” values in the table. To do so, firstly, create a table named “products” with three columns “id”, “name” and “category”. The details of the columns:

The “id” column is the primary key having auto-incremented integer values
The “name” has data type “VARCHAR” with a maximum size of “50” characters
The “category” has data type as “ENUM” with values “Electronics”, “Clothing”, “Books”, “Toys” and “Home goods”. Each value is assigned a numeric index such as “1” for the first value and so on.

Run this query provided below to create a table:

CREATE TABLE products (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50),

category ENUM('Electronics', 'Clothing', 'Books', 'Toys', 'Home goods')

);

The message will display as “Query OK” after creating the table successfully:

To insert data in this value, provide “name” for “products” and index of “ENUM” values, to insert its string value in the table. Run this query:

INSERT INTO products (name, category) VALUES

('iPhone', 1),

('T-shirt', 2),

('The Great Gatsby', 3),

('Lego set', 4),

('Throw pillow', 5);

The output is displaying a message that “Query OK, 5 rows affected”, it means values are inserted successfully:

To confirm if the data is inserted properly, run the given below query:

select * from products;

The table is displaying values available in the table. You can see that the “category” is having the “ENUM” values corresponding to its index you entered in the “INSERT” statement:

Create a Table with More Than One Column Containing “ENUM” Values

The table can contain multiple columns with “ENUM” data type. Create a table named “user_preferences” having four columns “id”, “user_id”, “language” and “theme”. The details of columns:

The “id” is the primary key having an integer data type with auto incremental
The “user_id” is a column having an integer data type
The “language” and “theme” have data type “ENUM” and contain all possible values for that column.

Run this query to create the table:

CREATE TABLE user_preferences (

id INT AUTO_INCREMENT PRIMARY KEY,

user_id INT,

language ENUM('English', 'Spanish', 'French', 'German'),

theme ENUM('Light', 'Dark')

);

The output displays the message as “Query OK”:

The next step is to insert the data in columns, so type the following command:

INSERT INTO user_preferences (user_id, language, theme) VALUES

(1, 'English', 'Light'),

(2, 'Spanish', 'Dark'),

(3, 'French', 'Light');

The output is displaying a message as “Query OK, 3 rows affected”, that indicated the data is inserted:

To see the data of the table run the query given below:

select * from user_preferences;

The table consist of two rows containing “ENUM” values along with other columns:

You have successfully learned about “ENUM” data type and how to set “ENUM” value in MySQL.

Conclusion

The “ENUM” is a data type in MySQL that stores all the possible values for a column. For example, the name of the days can contain only “7” values, so these “ENUM” values can be set at the time of table creation using “[Column-name] ENUM([value1], [value2], [value3], …)” syntax. Each “ENUM” value uses numeric indexes starting from “1”. This post discussed the “ENUM” data and how to set its value.

How to Set ENUM Value in MySQL?

What is the Syntax of “ENUM” in MySQL?

Create a Table With Single Column Containing “ENUM” Values

Create a Table with More Than One Column Containing “ENUM” Values

Conclusion

About the author

Abdul Mannan