Enum or Bool in mysql?

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.