MySQL: ENUM Datatype

Today I discovered that MySQL has a data type which is an ENUM. It allows you to define up to 2^16 or 65536 different values. The peculiarity is that you can define a set of values such as “status” => “pending”, “wip”, “done”, and MySQL itself converts them into values 1, 2, 3... Important: the values start at 1 and not 0. Note that it can have either a DEFAULT value or it can be NULL and in this case, if it is not defined, the field value will be NULL.

To use it we can create an example table:

CREATE TABLE tasks
id int unsigned unsigned auto_increment,
status enum('pending','wip', 'done'),
primary key(id)
);

And we can insert, read and select data both filtering by string values:

INSERT INTO tasks(status) VALUES ('pending');
INSERT INTO tasks(status) VALUES ('wip');
INSERT INTO tasks(status) VALUES ('done');

SELECT * FROM tasks WHERE status = “pending”;
SELECT * FROM tasks WHERE status = 1;

It would also work using numeric values, although in this case I think it loses the magic.