Since MySQL doesn’t seem to have any ‘boolean’ datatype, which datatype do we ‘abuse’ for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP-Script.
The several approaches are :
- 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’.
|Other Vendor Type||MySQL Type|
- For MySQL 5.0.3 and higher, we can use the BIT:
- 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, we 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.
- This is an elegant solution that it uses zero data bytes:
To set it to true, set some_flag = ” and to set it to false, set some_flag = NULL.
- using a CHAR(0), where ” == true and NULL == false.
- using TINYINT(1) in order to store boolean values in Mysql.
- 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 we 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 we should focus on.
- Unless we’re dealing with an enormous amount of data, those few bytes aren’t going to add up to much.
- If we’re using bit with PHP we need to typecast the values going in and out.
Until MySQL implements a bit datatype, if our processing is truly pressed for space and/or time, such as with high volume transactions, create a TINYINT field called bit_flags, for all our boolean variables, and mask and shift the boolean bit we decide in our SQL query.
According to the application usage, if one wants only 0 or 1 to be stored, bit(1) is the better choice.
Use TINYINT unsigned NOT NULL DEFAULT 0 (or 1).
- The most common is:
- TINYINT unsigned NOT NULL
- The trivial Boolean or Bool column type which is just a synonyms for TINYINT(1):
- BOOLEAN NOT NULL DEFAULT 0
- Declaring an enumeration with “true” and “false”:
- ENUM(‘false’, ‘true’) NOT NULL DEFAULT ‘false’
- The newest concept is:
- CHAR(0) NULL DEFAULT NULL
All of them are quite straightforward except the last one which might need some explanation. Char(0) means a zero length string where null value or lack of the value means false and empty string (”) means true.
We can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.
| FIELD | TYPE | NULL | KEY | DEFAULT | Extra |
| TYPE | tinyint(1) | YES | | NULL | |
| TYPE |
| 1 |
| 0 |
The results of the test demonstrate that only a 0 or 1 value validates against the false or true constants, as shown:
| Value | Unsigned | Value | Signed |
| 0 | False | 0 | False |
| 1 | True | 1 | True |
| 1 | True | -1 | Invalid |
| 2 | Invalid | 2 | Invalid |
Based on the comment, the 256 value range of TINYINT is found here.
- This article provides some of the basic informations on mysqldump database , mysql database , mysql php , create database mysql , mysql server , sql server data types , nodejs mysql , mysql select database , php mysql connect , php mysql query , mysql create database , mysql optimize , mysql license , mysql injection , mysql query , mysql download , download mysql , mysql dba , mysql tutorial pdf , mysql 5 , mysql syntax , mysql comment , mysql basics , mysql commands , mysql command line , what is mysql , list of data types , mysql setup , mysql operators , mysql interview questions , replication in mysql.