How Does MySQL Store Enums?

Question

If I have a table like this:

CREATE TABLE sizes (
    name ENUM('small', 'medium', 'large')
);

Is MySQL going to store those strings in every row, or internally will it use something smaller like integers to know which enum value to refer to?

I want to use an enum in a table but I'm worried if it's as wasteful as storing a string in every row.

Answer 1

It converts them to integers on INSERT / UPDATE and back to strings on SELECT so the internal storage is as integers but you don't get exposed to that.

You can retrieve the integer like SELECT mycolumn + 0.

See ENUMs in MySQL 5

Answer 2

First, I wouldn't worry about space consumption when deciding whether or not to use an enum. Disk space is both plentiful and cheap.

Second, it stores the strings in the column definition and then stores the appropriate enum within each record. According to this the value stored with each record is a numeric representation of the values position within the enum.

Answer 3

As explained here, enums are stored as values between 0 (no valid value set) and 65,535 that are associated with an enumeration index. The value that is stored in the table is a 2 byte value, not a string.