Friday, 2 November 2018

Which MySQL data type to use for storing boolean values

Since MySQL doesn't seem to have any 'boolean' data type, which data type do you 'abuse' for storing true/false information in MySQL?
Especially in the context of writing and reading from/to a PHP script.
Over time I have used and seen several approaches:
  • tinyint, varchar fields containing the values 0/1,
  • varchar fields containing the strings '0'/'1' or 'true'/'false'
  • and finally enum Fields containing the two options 'true'/'false'.
None of the above seems optimal. I tend to prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.
So which data type do you use? Is there a type designed for boolean values which I have overlooked? Do you see any advantages/disadvantages by using one type or another?

 Answers


For MySQL 5.0.3 and higher, you can use BIT. The manual says:
As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.
Otherwise, according to the MySQL manual you can use bool and boolean which are at the moment aliases of tinyint(1):
Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
MySQL also states that:
We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.
Funny isn't it, this link, posted a few years back, has become recursive.


This is an elegant solution that I quite appreciate because it uses zero data bytes:
some_flag CHAR(0) DEFAULT NULL
To set it to true, set some_flag = '' and to set it to false, set some_flag = NULL.
Then to test for true, check if some_flag IS NOT NULL, and to test for false, check if some_flag IS NULL.
(This method is described in "High Performance MySQL: Optimization, Backups, Replication, and More" by Jon Warren Lentz, Baron Schwartz and Arjen Lentz.)


If you use the BOOLEAN type, this is aliased to TINYINT(1). This is best if you want to use standardised SQL and don't mind that the field could contain an out of range value (basically anything that isn't 0 will be 'true').
ENUM('False', 'True') will let you use the strings in your SQL, and MySQL will store the field internally as an integer where 'False'=0 and 'True'=1 based on the order the Enum is specified.
In MySQL 5+ you can use a BIT(1) field to indicate a 1-bit numeric type. I don't believe this actually uses any less space in the storage but again allows you to constrain the possible values to 1 or 0.
All of the above will use approximately the same amount of storage, so it's best to pick the one you find easiest to work with.


Bit is only advantageous over the various byte options (tinyint, enum, char(1)) if you have a lot of boolean fields. One bit field still takes up a full byte. Two bit fields fit into that same byte. Three, four,five, six, seven, eight. After which they start filling up the next byte. Ultimately the savings are so small, there are thousands of other optimizations you should focus on. Unless you're dealing with an enormous amount of data, those few bytes aren't going to add up to much. If you're using bit with PHP you need to typecast the values going in and out.


I got fed up with trying to get zeroes, NULLS, and '' accurately round a loop of PHP, MySql and POST values, so I just use 'Yes' and 'No'.
This works flawlessly and needs no special treatment that isn't obvious and easy to do.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)