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.