Which MySQL Datatype to use for storing boolean values from/to PHP?

Question

Since MySQL doesn't seem to have any 'boolean' datatype, which datatype 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 datatype 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

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 2

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.

BTW: this is just a matter of http://www.google.ch/search?q=mysql+boolean+datatype.

Answer 3

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

Answer 4

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 5

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.