Related Articles Date, Time & Timestamp
Integer, Float and Bit
Enums and Sets (this one) Date, Time & Timestamp Date, Time & Timestamp Integer, Float and Bit Integer, Float and Bit Enums and Sets (this one) (this one) Introduction This article continues my series on MySQL data types. Previously, I covered numeric types and date/time types. This article focuses on SET and ENUM types, specialized data types that can be used for limited lists of data and make schemas more understandable, but can also cause problems if not used carefully. I'll first look at what they are, then give examples of common mistakes and how to avoid them. SET ENUM What Are ENUM and SET Types? ENUM ENUM A string object that can hold a single value chosen from a predefined list.
Example use case: order statuses ('pending', 'paid', 'shipped', 'cancelled').
Internally stored as an integer index (1-based).
Supports up to 65,535 elements (like SMALLINT). A string object that can hold a single value chosen from a predefined list. Example use case: order statuses ('pending', 'paid', 'shipped', 'cancelled'). 'pending' 'paid' 'shipped' 'cancelled' Internally stored as an integer index (1-based). Supports up to 65,535 elements (like SMALLINT). SMALLINT SET SET A string object that can hold any combination of values from a predefined list.
Example use case: user permissions ('read', 'write', 'delete').
Internally stored as a bitmap (each bit represents whether a value is present).
Supports up to 64 elements (like BIT(64)). A string object that can hold any combination of values from a predefined list. Example use case: user permissions ('read', 'write', 'delete'). 'read' 'write' 'delete' Internally stored as a bitmap (each bit represents whether a value is present). Supports up to 64 elements (like BIT(64)). BIT(64) Syntax and Examples Here is an example table for orders with status field that used ENUM type to specify available order statuses: ENUM CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending'
); CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending'
); To insert two orders to the table: INSERT INTO orders () VALUES ();
INSERT INTO orders (status) VALUES ('paid'); INSERT INTO orders () VALUES ();
INSERT INTO orders (status) VALUES ('paid'); Selecting all rows with an additional numeric representation of the status field: SELECT id, status, status+0 FROM orders;
+----+---------+----------+
| id | status  | status+0 |
+----+---------+----------+
|  1 | pending |        1 |
|  2 | paid    |        2 |
+----+---------+----------+
2 rows in set (0.000 sec) SELECT id, status, status+0 FROM orders;
+----+---------+----------+
| id | status  | status+0 |
+----+---------+----------+
|  1 | pending |        1 |
|  2 | paid    |        2 |
+----+---------+----------+
2 rows in set (0.000 sec) Here is an example table with SET field: SET CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    permissions SET('read','write','delete') NOT NULL
); CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    permissions SET('read','write','delete') NOT NULL
); Inserting a user with multiple permissions: INSERT INTO users (permissions) VALUES ('read,write'); INSERT INTO users (permissions) VALUES ('read,write'); Common Pitfalls And How To Avoid Them Of course, I can begin by noting that if you are using very old MySQL versions or settings that are not sufficiently strict, you may overlook errors related to writing values that are not included in the field definition. In this case, an empty value will be written into the field. But I hope you can use fairly recent MySQL versions. I also hope that you understand these field types range limitation and do not try to store more than 64 values in sets. So, the most common problem is related to changes in field definitions. Imagine someone adding a new value to the middle of the ENUM variants list: ENUM CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending','paid','shipped') NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid');

SELECT id, status, status+0 FROM orders;
+----+---------+----------+
| id | status  | status+0 |
+----+---------+----------+
|  1 | paid    |        2 |
+----+---------+----------+
1 rows in set (0.000 sec)

ALTER TABLE orders MODIFY status ENUM('pending','accepted','paid','shipped') NOT NULL DEFAULT 'pending'; CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending','paid','shipped') NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid');

SELECT id, status, status+0 FROM orders;
+----+---------+----------+
| id | status  | status+0 |
+----+---------+----------+
|  1 | paid    |        2 |
+----+---------+----------+
1 rows in set (0.000 sec)

ALTER TABLE orders MODIFY status ENUM('pending','accepted','paid','shipped') NOT NULL DEFAULT 'pending'; As MySQL stores ENUM fields as integers internally, here is what we will have: ENUM SELECT id, status, status+0 FROM orders;
+----+----------+----------+
| id | status   | status+0 |
+----+----------+----------+
|  1 | accepted |        2 |
+----+----------+----------+
1 rows in set (0.000 sec) SELECT id, status, status+0 FROM orders;
+----+----------+----------+
| id | status   | status+0 |
+----+----------+----------+
|  1 | accepted |        2 |
+----+----------+----------+
1 rows in set (0.000 sec) OOOPS! You need to remember that reordering or inserting a new variant anywhere but the end changes the index mapping, leading to data mismatches. OOOPS! You need to remember that reordering or inserting a new variant anywhere but the end changes the index mapping, leading to data mismatches. The second thing I want to mention is that while MySQL stores enums and sets as integers, it sorts and compares them as collated strings when selecting. This can surprise developers expecting numeric order. Conclusion ENUM and SET can be powerful tools for constraining values directly in your schema. But with their convenience come caveats around ordering, defaults, and silent coercion. By understanding their behavior and following best practices, you can avoid many of the common pitfalls and keep your data consistent. ENUM SET Stay tuned for more articles in this series exploring other MySQL data types and how to use them effectively.

This story contains new, firsthand information uncovered by the writer.

Navigating MySQL data types: numeric

Too Long; Didn't Read

Get Free Unlimited IP Geo Data With IPinfo Lite

Navigating MySQL Data Types: Sets and Enums

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Asynchronous Runtimes: A Primer for the Perplexed Developer

Here Are the 7 Productivity Tips Pro Software Engineers Use to Stay Ahead

Pocket Guide to SEO: Title, Keywords, Headings, and Meta Descriptions

TLS Cipher Suites: Which Ones to Let Go in 2023?

What Are Convolution Neural Networks? [ELI5]

Asynchronous Runtimes: A Primer for the Perplexed Developer

Here Are the 7 Productivity Tips Pro Software Engineers Use to Stay Ahead

Pocket Guide to SEO: Title, Keywords, Headings, and Meta Descriptions

TLS Cipher Suites: Which Ones to Let Go in 2023?

What Are Convolution Neural Networks? [ELI5]

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps