Which MySQL data type to use for storing boolean values

Question

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?

Answer 1

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 or 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.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Answer 2

BOOL and BOOLEAN are synonyms of TINYINT(1). Zero is false, anything else is true. More information here.

Answer 3

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.)

Answer 4

This question has been answered but I figured I'd throw in my $0.02. I often use a CHAR(0), where '' == 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 CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

Answer 5

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.

Answer 6

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)

http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html

Answer 7

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.

Answer 8

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;

etc.

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 bit_flags field.

You’ll find these sites useful when implementing this method:

• MySQL — Bit Functions and Operators
• Decimal/Binary Conversion Tool

Answer 9

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.

Answer 10

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.

Answer 11

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 0, 1 or NULL, as well as values which can be converted to 0 or 1 without errors like '1', 0x00, b'1' or TRUE/FALSE.

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 TINYINT, TINYINT(1) or TINYINT(123).

If you want your schema to be upwards compatible, you can also use BOOL or BOOLEAN

bool_val BOOL CHECK(bool_val IN(TRUE,FALSE))

db<>fiddle demo

Answer 12

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.

Answer 13

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[1] or false[0]) 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