BOOLEAN or TINYINT confusion

Question

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

User · Accepted Answer

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
)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] BOOLEAN or TINYINT confusion

Examples related to mysql

Examples related to types

Examples related to boolean

Examples related to tinyint