BOOLEAN or TINYINT confusion
MysqlTypesBooleanTinyintMysql Problem Overview
I was designing a database for a site where I need to use a boolean datetype to store only 2 states, true or false. I am using MySQL.
While designing the database using phpMyAdmin, I found that I have both the BOOLEAN datatype and the TINYINT datatype.
I went through different articles, some said TINYINT is the same as BOOLEAN, no difference. Some say BOOLEAN is converted into TINYINT in MySQL.
MY question is, if they both are same why do there exist two? There should be only one of them.
Here is the reference to the articles I read:
http://www.careerride.com/MySQL-BOOL-TINYINT-BIT.aspx
http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
Mysql Solutions
Solution 1 - Mysql
MySQL does not have internal boolean data type. It uses the smallest integer data type - TINYINT.
The BOOLEAN and BOOL are equivalents of TINYINT(1), because they are synonyms.
Try to create this table -
CREATE TABLE table1 (
column1 BOOLEAN DEFAULT NULL
);
Then run SHOW CREATE TABLE, you will get this output -
CREATE TABLE `table1` (
`column1` tinyint(1) DEFAULT NULL
)
Solution 2 - Mysql
Just a note for php developers (I lack the necessary stackoverflow points to post this as a comment) ... the automagic (and silent) conversion to TINYINT means that php retrieves a value from a "BOOLEAN" column as a "0" or "1", not the expected (by me) true/false.
A developer who is looking at the SQL used to create a table and sees something like: "some_boolean BOOLEAN NOT NULL DEFAULT FALSE," might reasonably expect to see true/false results when a row containing that column is retrieved. Instead (at least in my version of PHP), the result will be "0" or "1" (yes, a string "0" or string "1", not an int 0/1, thank you php).
It's a nit, but enough to cause unit tests to fail.
Solution 3 - Mysql
The Newest MySQL Versions have the new BIT data type in which you can specify the number of bits in the field, for example BIT(1) to use as Boolean type, because it can be only 0 or 1.
Solution 4 - Mysql
As of MySql 5.1 version reference
BIT(M) = approximately (M+7)/8 bytes,
BIT(1) = (1+7)/8 = 1 bytes (8 bits)
=========================================================================
TINYINT(1) take 8 bits.
https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-numeric
Solution 5 - Mysql
The numeric type overview for MySQL states: BOOL, BOOLEAN: These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.
See here: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html