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″https://stackoverflow.com/”1’ or ‘true”https://stackoverflow.com/”false’
- and finally enum Fields containing the two options ‘true”https://stackoverflow.com/”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?
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
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
BOOLEAN are synonyms of
TINYINT(1). Zero is
false, anything else is
true. More information here.
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
(This method is described in “High Performance MySQL: Optimization, Backups, Replication, and More” by Jon Warren Lentz, Baron Schwartz and Arjen Lentz.)
This question has been answered but I figured I’d throw in my $0.02.
I often use a
'' == true and NULL == false.
From MySQL docs:
CHAR(0)is also quite nice when you need a column that can take only
two values: A column that is defined as
NULLoccupies only one
bit and can take only the values
''(the empty string).
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.
I use TINYINT(1) in order to store boolean values in Mysql.
I don’t know if there is any advantage to use this… But if i’m not wrong, mysql can store boolean (BOOL) and it store it as a tinyint(1)
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.
Until MySQL implements a bit datatype, if your processing is truly pressed for space and/or time, such as with high volume transactions, create a TINYINT field called
bit_flags for all your boolean variables, and mask and shift the boolean bit you desire in your SQL query.
For instance, if your left-most bit represents your bool field, and the 7 rightmost bits represent nothing, then your
bit_flags field will equal 128 (binary 10000000). Mask (hide) the seven rightmost bits (using the bitwise operator
&), and shift the 8th bit seven spaces to the right, ending up with 00000001. Now the entire number (which, in this case, is 1) is your value.
SELECT (t.bit_flags & 128) >> 7 AS myBool FROM myTable t; if bit_flags = 128 ==> 1 (true) if bit_flags = 0 ==> 0 (false)
You can run statements like these as you test
SELECT (128 & 128) >> 7; SELECT (0 & 128) >> 7;
Since you have 8 bits, you have potentially 8 boolean variables from one byte. Some future programmer will invariably use the next seven bits, so you must mask. Don’t just shift, or you will create hell for yourself and others in the future. Make sure you have MySQL do your masking and shifting — this will be significantly faster than having the web-scripting language (PHP, ASP, etc.) do it. Also, make sure that you place a comment in the MySQL comment field for your
You’ll find these sites useful when implementing this method:
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.
Since MySQL (8.0.16) and MariaDB (10.2.1) both implemented the CHECK constraint, I would now use
bool_val TINYINT CHECK(bool_val IN(0,1))
You will only be able to store
NULL, as well as values which can be converted to
1 without errors like
If you don’t want to permit NULLs, add the
NOT NULL option
bool_val TINYINT NOT NULL CHECK(bool_val IN(0,1))
Note that there is virtually no difference if you use
If you want your schema to be upwards compatible, you can also use
bool_val BOOL CHECK(bool_val IN(TRUE,FALSE))
Referring to this link
Boolean datatype in Mysql, according to the application usage, if one wants only 0 or 1 to be stored, bit(1) is the better choice.
After reading the answers here I decided to use
bit(1) and yes, it is somehow better in space/time, BUT after a while I changed my mind and I will never use it again. It complicated my development a lot, when using prepared statements, libraries etc (php).
Since then, I always use
tinyint(1), seems good enough.
You can use BOOL, BOOLEAN data type for storing boolean values.
These types are synonyms for TINYINT(1)
However, the BIT(1) data type makes more sense to store a boolean value (either true or false) but TINYINT(1) is easier to work with when you’re outputting the data, querying and so on and to achieve interoperability between MySQL and other databases. You can also check this answer or thread.
MySQL also converts BOOL, BOOLEAN data types to TINYINT(1).
Further, read documentation