Boolean vs Tinyint vs Bit

I was using MySQL to design database, and I need to use boolean datatype where two states are required true or false. I find the following differences among them.

TINYINT is an 8-bit integer value, a BIT field can store between 1 bit, BIT(1), and 64 bits, BIT(64). For a boolean values, BIT(1) is pretty common. The range of this data type is -128 - +127 or 0 – 256 and occupies 1 byte.

Boolean is synonym for TINYITNT(1).  0 is considered false and 1 is considered true. MySQL sets them as TINYINT type.

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. Bit uses 8 bytes and stores only binary data.

Small short Example:

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 )

If I have missed any points here, please do comment.

Thanks for reading the blog.