Enum or Bool in mysql?

Question

Simple silly question. What is better?

A Bool or an Enum('y','n') ?

Answer 1

If you think it's possible the field might ever need to be extended, you'll want to use an enum.

Say you've decided to use the boolean true/false for an isCompleted field. But maybe later, you realize you also want to track an intermediate state. Now you can either add another boolean isInProgress (bad idea: what if you want to add a 4th state? or 5th?), or change your field to an enum for state like [NOT_STARTED, IN_PROGRESS, COMPLETED].

The problem is now you have to go back into every place you are checking isCompleted and change it to check the new enum. You also have to migrate all the existing data to be consistent with the new enum.

Instead, if you used the enum for status originally, you could add in the new state without changing any of your existing logic.

Sometimes doing a little more work now will reduce a lot of work for your future self.

Answer 2

There are 8 reasons for not using ENUM data type;
So, instead of ENUM, either use boolean or a reference foreign table.

Answer 3

A lot of default advise is to use BOOL/TINYINT(1), but as stated in the answer at https://stackoverflow.com/a/4180982/2045006 this allow 9 variations of TRUE.

In many cases this does not matter, but if your column will be part of a unique index then this will become quite a problem.

In the case that you will use the column in a unique index, I would recommend using BIT(1).

ENUM would also work well with a unique index (provided you have a suitable SQL Mode set.) However, I would use ENUM only when you want to work with string representations of true/false rather than actual boolean values.

Answer 4

Neither are best for storing a single bit (or boolean). The enum has a lookup table, and stores the answer as an integer. The boolean is actually just an alias for "TINYINT(1)" which is technically 8 bits of information. The bit data type will only store as many bits as in its definition (like in the varchar type) so a bit(1) will literally only store one bit. However, if you only have one of these fields, then the question is moot, as nothing will fill the remaining bits, so they will be unused space on each row (amount of space each row is rounded up to at least a byte, typically 8 bits, per row).

Answer 5

Here's the problem with storing boolean values as an enum:

SELECT count(*) FROM people WHERE is_active = true; #=> Returns 0 because true != 'true'

Which is misleading because:

SELECT count(*) FROM people WHERE is_active = 'true'; #=> Returns 10

If you're writing all of your own SQL queries, then you would know to not to pass an expression into your query, but if you're using an ORM you're going to run into trouble since an ORM will typically convert the expression to something the database it's querying can understand ('t'/'f' for SQLite; 0/1 for MySQL etc.)

In short, while one may not be faster than the other at the byte level, booleans should be stored as expressions so they can be compared with other expressions.

At least, that's how I see it.

Answer 6

Depending on the language you're using to interface with the database, you can run into case sensitivity issues by using enum, for example if your database uses a lowercase 'y' but your code expects an uppercase 'Y'. A bool/tinyint will always be 0 or 1 (or NULL) so it avoids this problem.

Answer 7

BOOLEAN is an alias for TINYINT(1) and is stored as one byte of data.
ENUM('y','n') is also stored as 1 byte of data.

So from a storage size point of view, neither is better.
However you can store 9 in a BOOLEAN field and it will accept it. So if you want to force two states only, go for ENUM.

Answer 8

TINYINT(1) - it looks like a Boolean, so make it one.

Never compare internally to things like y when a Boolean (0/1) is available.