ENUM and SET types in MySQL

Introduction

ENUM and SET types help you store a list of allowed values in a column. They make sure only certain values can be saved.

You want to store a color choice like 'red', 'green', or 'blue' in a column.

You need to save multiple tags or categories for an item from a fixed list.

You want to limit user input to specific options to avoid mistakes.

You want to save space by using predefined values instead of long text.

You want to easily check if a value is part of a set of allowed options.

Syntax

MySQL

CREATE TABLE table_name (
  column_name ENUM('value1', 'value2', ...),
  another_column SET('value1', 'value2', ...)
);

ENUM stores one value from the list.

SET stores zero or more values from the list, combined.

Examples

This creates a column 'size' that can only be 'small', 'medium', or 'large'.

MySQL

CREATE TABLE shirts (
  size ENUM('small', 'medium', 'large')
);

This creates a column 'colors' that can store any combination of 'red', 'green', and 'blue'.

MySQL

CREATE TABLE products (
  colors SET('red', 'green', 'blue')
);

Inserts a shirt with size 'medium'.

MySQL

INSERT INTO shirts (size) VALUES ('medium');

Inserts a product with colors 'red' and 'blue'.

MySQL

INSERT INTO products (colors) VALUES ('red,blue');

Sample Program

This creates a table with ENUM and SET columns, inserts two rows, and selects all data.

MySQL

CREATE TABLE example (
  mood ENUM('happy', 'sad', 'neutral'),
  hobbies SET('reading', 'sports', 'music')
);

INSERT INTO example (mood, hobbies) VALUES ('happy', 'reading,music');
INSERT INTO example (mood, hobbies) VALUES ('sad', 'sports');

SELECT * FROM example;

Important Notes

ENUM stores only one value, SET can store multiple values separated by commas.

Values in ENUM and SET are case sensitive.

Trying to insert a value not in the list will cause an error or store an empty string.

Summary

ENUM limits a column to one value from a list.

SET allows multiple values from a list in one column.

Both help keep data clean and save space.