MySQL enum data type

Many data types exist in MySQL to store different data into the database table. The enum data type is one of them. The full form of enum is an enumeration. When it is required to insert any particular value from the list of the predefined values into a field of a table, then an enum data type is used. The list of values is stored as the string for the enum field, and the number of selected values from the list values will be inserted into the enum field at the insert or update time. When the enum field is retrieved from the table, it will show the value from the number to the human-readable format. The uses of enum data type in MySQL have been shown in this tutorial.

Syntax:

The syntax of the enum data type is given below.

Maximum 65,535 values can be defined as enum values.

Enum Attributes:

Enum data type has three attributes. These are described below.

DEFAULT

The default value of the enum data type is NULL. If no value is provided for the enum field, then the NULL value will be inserted.

NULL

It works the same as the DEFAULT value if this attribute is set for the enum field.

NOT NULL

A warning message will appear if this attribute is set for the enum field and no value is provided at the insertion time.

Checking the uses of enum data type:

You have to create a table with enum data type in a MySQL database to check the use of enum data type. Open the terminal and connect with the MySQL server by executing the following command.

Run the following command to create a database named test_db.

Run the following command to select the database.

Run the following query to create a table named Book with five fields. The data type of the type field of this table is an enum that will store any one of three values (Engineering, Medical, and Business).

Run the following command to check the structure of the Book table.

Output:

The following output will appear after executing the above command.

Insert valid data into the table:

Run the following insert query to insert three records into the Book table. ‘Engineering,’ ‘Medical,’ and ‘Business’ values have been used for the table’s enum field, which is valid enum values. So, the query will be executed without any error.

Read data from the table:

After executing the INSERT query, run the following SELECT query to check the content of the Book table.

Output:

The following output will appear after executing the above command.

Insert invalid data into the table:

The INSERT query will generate an error if the value that does not exist in the enum list will be tried insert. In the following INSERT query, the ‘BBA’ value is set for the enum field (type), but it does not match any of the enum items defined at the time of table creation. So, the following INSERT query will generate an error after the execution.

Output:

The following output will appear after executing the above command. Here, error no 1265 has been generated, and no new record will be inserted.

Filter data from the table based on numeric enum value:

The string value is used at the time of inserting data into the enum field of the table, but the inserted records of the table can be retrieved based on the numeric index or the string value of the enum field. The use of the numeric index of enum value to filter the data from the Book table has been shown in the following SELECT query. The numeric index 2 of the enum field contains the value, ‘Medical.’ So, the records that contain the value ‘Medical’ in the type field of the Book table will appear in the output. There is only one record in the table that matches the criteria.

Output:

The following output will appear after executing the above query.

Filter data from the table based on human-readable enum value:

It is not easier to remember the numeric index of each enum item value if the enum field contains a list of a large number of items. In this case, it is better to use the string value of the enum item in the query for retrieving the records based on the enum field’s value. The following SELECT query will filter the records where the type field contains the value, ‘Business.’ According to the Book table data, there is only one matching record with the type value, ‘Business.’

Output:

The following output will appear after executing the above query.

Conclusion:

The enum is a beneficial data type of MySQL. The insertion of invalid data can be prevented by using this data type. The way of declaring and inserting data into the enum field and the way of retrieving data based on the enum field have been shown in this tutorial by using examples.