Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

ENUM

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

 ENUM
ENUM (Enumerator) is a user defined datatype which stores a list of values as strings. These values are specified when you define the ENUM column. The user can choose values from this predefined list while inserting values into this column.
Each string value defined in an ENUM column is implicitly assigned a numerical value starting from 1. These numerical values are used internally by MySQL to represent the ENUM values.
The MySQL ENUM Data Type
The MySQL ENUM data type allow you to select one or more values from a predefined list during insertion or update operations. The selected values are stored as strings in the table, and when you retrieve data from the ENUM column, the values are presented in a human-readable format.
ENUM columns can accept values of various data types, including integers, floating-point numbers, decimals, and strings. However, internally, MySQL will convert these values to the closest matching ENUM value based on its predefined list.
Syntax
Following is the syntax to define the ENUM data type on a column −
CREATE TABLE table_name (  
   Column1,  
   Column2 ENUM ('value1','value2','value3', ...),  
   Column3...  
);
﻿
Note: An enum column can have maximum 65,535 values.
Attributes of ENUM
The ENUM datatype in MySQL has three attributes. The same is described below −
Default − The default value of enum data type is NULL. If no value is provided for the enum field at the time of insertion, Null value will be inserted.
NULL − It works the same as the DEFAULT value if this attribute is set for the enum field. If it is set, the index value is always NULL.
NOT NULL − MySQL will generate a warning message if this attribute is set for the enum field and no value is provided at the insertion time.
Example
First of all, let us create a table named STUDENTS. In this table, we are specifying ENUM string object in the BRANCH column using the following query −
CREATE TABLE STUDENTS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   BRANCH ENUM ('CSE', 'ECE', 'MECH'),
   FEES int NOT NULL,
   PRIMARY KEY (ID)
);
﻿
Following is the output obtained −
Query OK, 0 rows affected (0.04 sec)
﻿
Now, we retrieve the structure of the STUDENTS table, revealing that the "BRANCH" field has an enum data type −
DESCRIBE STUDENTS;
﻿
The output indicates that the BRANCH field's data type is ENUM, which stores the values ('CSE', 'ECE', 'MECH') −
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(30)
NO
﻿
NULL
﻿
BRANCH
enum('CSE','ECE','MECH')
YES
﻿
NULL
﻿
FEES
int
NO
﻿
NULL
﻿
Now, let us insert records into the STUDENTS table using the following INSERT query −
INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
('Anirudh', 'CSE', 500000),
('Yuvan', 'ECE', 350000),
('Harris', 'MECH', 400000);
﻿
In these insertion queries, we have used values ('CSE', 'ECE', and 'MECH') for the "BRANCH" field, which are valid enum values. Hence, the queries executed without any errors −
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
﻿
Using the below query, we can display all the values in the table −
SELECT * FROM STUDENTS;
﻿
Following are the records of STUDENTS table −
ID
NAME
BRANCH
FEES
1
Anirudh
CSE
500000
2
Yuvan
ECE
350000
3
Harris
MECH
400000
Inserting Records with Numerical ENUM Values
We can insert the enum list values to the ENUM column of table using the respective numeric index. The numeric index starts from 1 but not from 0.
Example
In the query below, we are inserting the value 'CSE' from the ENUM list into the 'BRANCH' column using its numeric index. Since 'CSE' is located at position 1 in the ENUM list, we use 1 as the numeric index in the query.
INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES 
('Devi', 1, 380000);
﻿
Output
The insertion query executes without any errors −
Query OK, 1 row affected (0.01 sec)
﻿
Verification
Let us verify whether the above insertion is successful or not by retrieving all the records of the table using the below query −
SELECT * FROM STUDENTS;
﻿
The STUDENTS table displayed is as follows −
ID
NAME
BRANCH
FEES
1
Anirudh
CSE
500000
2
Yuvan
ECE
350000
3
Harris
MECH
400000
4
Devi
CSE
380000
Inserting Invalid Records
In MySQL, if we try to insert a value into a column with an ENUM data type that does not match any of the specified enum values, it will result in an error.
Example
In the following query, we are referring to the 6th value in enum list, which does not exist. So, the following query will generate an error −
INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES 
('Thaman', 6, 200000); 
﻿
Output
As we can see the output, an error is generated, and no new record has been inserted −
ERROR 1265 (01000): Data truncated for column 'BRANCH' at row 1
﻿
Filtering Records by Numeric ENUM Value
In MySQL, you can retrieve records from an ENUM column based on either the string value or the numeric index. The numeric index starts from 1, not 0.
Example
The numeric index of 1 in enum list is 'CSE'. So, the following query will fetch the records where the BRANCH column contains the value as 'CSE'.
SELECT * FROM STUDENTS WHERE BRANCH = 1;
﻿
Output
The resulting output displays records where the 'BRANCH' column contains the value 'CSE' −
ID
NAME
BRANCH
FEES
1
Anirudh
CSE
500000
4
Devi
CSE
380000
Filtering Records by Human-Readable ENUM Value
There can be some instances where the enum list will have large number of values. It can be difficult to remember the numeric index for every value in the list. In such cases, it is more convenient to use the human-readable string value of the ENUM item in your query to retrieve records based on the ENUM field's value.
Example
In the following query, we are filtering the records where the BRANCH column contains the value "Mech".
SELECT * FROM STUDENTS WHERE BRANCH = "MECH";
﻿
Output
Following is the output obtained −
ID
NAME
BRANCH
FEES
3
Harris
MECH
400000
Disadvantages of ENUM Data Type
Following are the disadvantages of ENUM data type in MySQL −
If we wish to modify the values in enum list, we need to re-create the complete table using the ALTER TABLE command, which is quite expensive in terms of used resources and time.
It is very complex to get the complete enum list because we need to access the inform_schema database.
Expressions cannot be used with enumeration values. For instance, the following CREATE statement will return an error because it used the CONCAT() function for creating enumeration value −
CREATE TABLE Students (  
   ID int PRIMARY KEY AUTO_INCREMENT,   
   NAME varchar(30),   
   BRANCH ENUM('CSE', CONCAT('ME','CH'))
);
﻿
User variables cannot be used for an enumeration value. For instance, look at the following query −
mysql> SET @mybranch = 'EEE';  
mysql> CREATE TABLE Students (  
   ID int PRIMARY KEY AUTO_INCREMENT,   
   NAME varchar(30),   
   BRANCH ENUM('CSE', 'MECH', @mybranch)
);
﻿
It is recommended to not use the numeric values as enum values.
Enum Datatypes Using a Client Program
We can also create column of the Enum datatypes using the client program.
Syntax
PHPNodeJSJavaPython
To create a column of Enum datatypes through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
$sql = 'CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))';
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
﻿
//create table with boolean column    
$sql = 'CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))';
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!\n");
}
//insert data into created table    
$q = "INSERT INTO STUDENTS (NAME, BRANCH, FEES)
      VALUES ('Anirudh', 'CSE', 500000), ('Yuvan', 'ECE', 350000)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!\n");
}
//now display the table records    
$s = "SELECT BRANCH FROM STUDENTS";
if ($r = $mysqli->query($s)) {
    printf("Select query executed successfully...!\n");
    printf("following records belongs to Enum datatypes: \n");
    while ($row = $r->fetch_assoc()) {
        printf(" Branch Name: %s", $row["BRANCH"]);
        printf("\n");
    }
} else {
    printf('Failed');
}
$mysqli->close();
﻿
Output
The output obtained is as follows −
Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
following records belongs to Enum datatypes:
 Branch Name: CSE
 Branch Name: ECE         
﻿
﻿
﻿
﻿
﻿

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(30)	NO		NULL
BRANCH	enum('CSE','ECE','MECH')	YES		NULL
FEES	int	NO		NULL

ID	NAME	BRANCH	FEES
1	Anirudh	CSE	500000
2	Yuvan	ECE	350000
3	Harris	MECH	400000
4	Devi	CSE	380000

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

ENUM

The MySQL ENUM Data Type

Syntax

Attributes of ENUM

Example

Inserting Records with Numerical ENUM Values

Example

Output

Verification

Inserting Invalid Records

Example

Output

Filtering Records by Numeric ENUM Value

Example

Output

Filtering Records by Human-Readable ENUM Value

Example

Output

Disadvantages of ENUM Data Type

Enum Datatypes Using a Client Program

Syntax

Example

Output