MySQL - 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') −

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 −

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 −

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 6^th 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' −

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 −

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

$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);

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) )";
con.query(sql);

String 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))";
statement.execute(sql);

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)  )'    
cursorObj.execute(sql)

Example

Following are the programs −

$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();

Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
following records belongs to Enum datatypes:
 Branch Name: CSE
 Branch Name: ECE         

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a customers that accepts one column enum type.
  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) )";
  con.query(sql);

  //insert data into created table
  sql ="INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES ('Anirudh', 'CSE', 500000),  ('Yuvan', 'ECE', 350000)";
  con.query(sql);
  //select datatypes of branch
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'STUDENTS' AND COLUMN_NAME = 'BRANCH'`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});  

[ { DATA_TYPE: 'enum' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Enum {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //ENUM data types...!;
         String 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))";
         statement.execute(sql);
         System.out.println("column of a ENUM type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE STUDENTS");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}  

Connected successfully...!
column of a ENUM type created successfully...!
ID int
NAME varchar(30)
BRANCH enum('CSE','ECE','MECH')
FEES int  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with enum 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)
);
'''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    ('Anirudh', 'CSE', 500000),
    ('Yuvan', 'ECE', 350000),
    ('Harris', 'MECH', 400000)
]
# Insert data into the created table
insert_query = "INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES (%s, %s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM STUDENTS"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()  

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, 'Anirudh', 'CSE', 500000)
(2, 'Yuvan', 'ECE', 350000)
(3, 'Harris', 'MECH', 400000)