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

Tags:

Mysql

Php