MySQL ENUM Data Type

Introduction

In this chapter, we will learn about the ENUM data type in MySQL. The ENUM data type is used to store a predefined set of values, making it useful for columns that have a specific set of possible values. This is particularly useful for storing categorical data, such as status values, categories, or types. We will cover the syntax for defining ENUM columns, examples, and important considerations for using ENUM in MySQL.

Syntax

The basic syntax for defining an ENUM column in MySQL is:

ENUM('value1', 'value2', ..., 'valueN')

• Each value in the ENUM definition must be a string.
• The ENUM type can have up to 65,535 distinct values.

Example

Here is an example of how to define an ENUM column in a table:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

In this example, order_status is defined as an ENUM column to store the status of orders with predefined values.

Full Example

Let’s go through a complete example where we create a database and a table with ENUM columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE shop;

2. Select the Database:

USE shop;

3. Create the Orders Table with ENUM Columns:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

4. Insert Data into the Orders Table:

INSERT INTO orders (customer_name, order_status) VALUES
('Rahul Sharma', 'pending'),
('Priya Singh', 'processing'),
('Amit Kumar', 'shipped'),
('Neha Verma', 'delivered'),
('Sahil Mehta', 'cancelled');

5. Query the Orders Table:

SELECT * FROM orders;

Output

In this example, the order_status column is defined as ENUM to store the status of various orders.

Using ENUM Values in Queries

You can use ENUM values in queries to filter and manipulate categorical data.

Example: Query Orders with a Specific Status

SELECT * FROM orders WHERE order_status = 'shipped';

Output

Example: Query Orders by Status and Sort by Status

SELECT * FROM orders ORDER BY FIELD(order_status, 'pending', 'processing', 'shipped', 'delivered', 'cancelled');

Output

Important Considerations

• Predefined Values: The ENUM data type ensures that only predefined values can be stored in the column. This helps maintain data integrity by preventing invalid values.

• Storage Requirements: The ENUM data type uses 1 byte of storage for up to 255 values. For more than 255 values, it uses 2 bytes.

• Case Sensitivity: By default, ENUM values are case-insensitive. You can make them case-sensitive by using the BINARY keyword.

  CREATE TABLE case_sensitive_orders (
      id INT PRIMARY KEY AUTO_INCREMENT,
      order_status ENUM('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED') BINARY
  );
  

• Default Values: You can set a default value for an ENUM column. If no default value is specified, the default is the first value in the list.

  CREATE TABLE orders_with_default (
      id INT PRIMARY KEY AUTO_INCREMENT,
      customer_name VARCHAR(100) NOT NULL,
      order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'
  );
  

Conclusion

The ENUM data type is useful for storing categorical data with a predefined set of values in MySQL. This chapter covered the syntax for defining ENUM columns, provided a full example with different categorical attributes, and discussed important considerations.