I'm using MySQL 8.0 Community Edition. I have an attribute where I want to store a TRUE or FALSE status and I want it to use as little space as possible.
After reading the answers in this question:
MySQL: Smallest datatype for one bit and the MySQL documentation Bit-Value Type - BIT and Integer Types (Exact Value), I understand that at storage level it is better is to use BIT(1) because BOOL is actually a TINYINT(1) and therefore uses full 1 byte.
At storage level it is clear that BIT(1) is the best option, but at performance level when searching for true or false?
If I understand correctly BIT would store 1 or 0 while BOOL stores TRUE or FALSE.
That difference makes that when searching between both possibilities one of the types is better optimized for it?
Thanks.
BIT(1) also requires minimum 1 byte, so you're not saving any space compared to BOOL/TINYINT. Both take 1 byte.
Speaking to MySQL developers, they usually wince when I bring up the BIT data type. It's full of known bugs, and likely undiscovered bugs. The internal code is poorly understood. They told me to just use TINYINT.
By the way, MySQL doesn't have a true BOOL type. BOOL is just an alias for TINYINT(1), and there is no true or false value. The "false" value is literally the integer 0, and the "true" value is the integer 1. In other words, you can SUM() a column that is supposedly boolean, and you get an integer sum equal to the number of rows where the column is "true." This is not compliant with standard SQL (it makes no sense to SUM() a boolean column), but it's the way BOOL is implemented in MySQL.