Which MySQL data type to use for storing boolean values

Question

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

BOOL and 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 IS NULL.

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

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)

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

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.