MySQL: Gotcha when filtering enum fields.

This article describes an interesting (and surprising) behaviour of MySQL enum fields that I’ve recently discovered during debugging process.

MySQL enum data type

Here’s a quote from MySQL official documentation:

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

Pretty straightforward, right? It’s a string field with restricted set of values.

Where’s the catch?

Imagine you have a field that represents a feature flag and it a feature can be in one of 2 states: enabled or disabled. You decide that you’ll store this information in MySQL database as an enum: '0' for disabled and '1' for enabled.

Exemplary DDL for such table could look like this:

create table feature_flags (
    id smallint not null auto_increment,
    name char(100) not null,
    activated enum('0', '1')
)

and some dummy data:

insert into feature_flags (name, activated)
values
('some_disabled_feature', '0'),
('some_enabled_feature', '1')

Now you need to retrieve names of all activated features and you write a simple SELECT query for this:

select name from feaute_flags where activated = 1

Do you see anything suspicious? If you haven’t worked with MySQL’s enums before, then this query might look correct to you and you’d expect to get the some_enabled_feature back from db results.

But that’s not what database gives back:

+-----------------------+
|         name          |
+-----------------------+
| some_disabled_feature |
+-----------------------+

Why is that happening? I’ve used enumeration values looking like numbers!

Again, let’s cite MySQL documentation:

If you make enumeration values that look like numbers, it is easy to mix up the literal values with their internal index numbers, as explained in Enumeration Limitations.

And the Enumeration Limitations section says:

We strongly recommend that you do not use numbers as enumeration values, because it does not save on storage over the appropriate TINYINT or SMALLINT type, and it is easy to mix up the strings and the underlying number values (which might not be the same) if you quote the ENUM values incorrectly. If you do use a number as an enumeration value, always enclose it in quotation marks. If the quotation marks are omitted, the number is regarded as an index. See Handling of Enumeration Literals to see how even a quoted number could be mistakenly used as a numeric index value.

To fix that problem, I should have used a string value in the where condition:

select name from feaute_flags where activated = '1'

Filtering by integer means referring to enum’s index (which starts from 1 - '0' has index value 1 and '1' has index value 2).

Closing remarks

Avoid enum values that looks like integers as they might be a potential source of bugs (especially for MySQL noobs like me)!

If you’d like to try out this example in the browser, here’s a link to the MySQL fiddle.

(10/52) This is a 10th post from my blogging challenge (publishing 52 posts in 2024).