Enum or Bool in mysql?

Simple silly question. What is better?

A Bool 开发者_高级运维or an Enum('y','n') ?

---

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.

---

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.

---

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.

---

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).

---

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.

---

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.

---

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