Menu
Simple silly question. What is better?
A Bool or an Enum('y','n') ?
638
ENUM is great for data that you know will fall within a static set. If you are using Mysql 5+, storage is almost always better with an ENUM type for data in a static set, as the official MySQL reference shows. Not to mention that the data is readable, and you have an extra layer of validation.
In MySQL, an ENUM is a string object whose value is chosen from a list of permitted values defined at the time of column creation. The ENUM data type provides the following advantages: Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values.
MySQL does not have a boolean (or bool) data type. Instead, it converts boolean values into integer data types (TINYINT). When you create a table with a boolean data type, MySQL outputs data as 0, if false, and 1, if true.
ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a') . The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.
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.
102
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.
24
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.
6
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
