The best data type to store 0, 1, null values
UPDATE
Since MySQL (8.0.16) and MariaDB (10.2.1) both implemented the CHECK constraint, I would now go with
bool_val TINYINT UNSIGNED CHECK(bool_val <= 1)
db<>fiddle
or
bool_val TINYINT CHECK(bool_val IN(0,1))
db<>fiddle
Original answer
I would use Bit-Value Type - BIT
BIT(1) NULL DEFAULT NULL
BIT(1)
needs 1 Byte of storage, wich is the same as TINYINT(1)
does. The difference is that BIT(1)
only accepts the values 0
and 1
(or b'0'
and b'1'
) while TINYINT(1) UNSIGNED
accepts values from 0
up to 255
. The length defined in the brackets for TINYINT
does not take any affect to values that can be stored. It is only an information for clients, how to display the values (e.g. if you use ZEROFILL
).
It's 2019! This was marked correct years ago. Please see Paul Spiegel's answer directly below this one for a more modern answer. #
TINYINT(1) UNSIGNED NULL
tinyint's are perfect for booleans