How to store boolean datatype in MySQL?

Question

Which datatype is used for storing true/false information in MySQL?

Especially when writing and reading with a PHP-Script.

Over time I have used and seen several approaches:

tinyint, varchar fields containing the values 0/1,
varchar fields containing the strings '0'/'1' or 'true'/'false'
and finally enum Fields containing the two options 'true'/'false'.

Not one seems optimal, I prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.

So, is there a type designed for boolean values which I have overlooked?

Do you see any advantages/disadvantages by using one type or another?

Answer 1

There are lot's of options.

For compatibility with a lot of different MySQL clients, client libraries, ORM, etc. we've settled on

 col  TINYINT(1) UNSIGNED COMMENT 'boolean, ...'

And we don't use the TINYINT(1) UNSIGNED datatype for anything except boolean. (Where we can, we add NOT NULL.) We add a comment, with "boolean" as the first string, followed by the actual description.

Note that the (1) doesn't influence the range of values that can be stored. It can still hold integer values from 0 thru 255. We just include it to help distinguish our "boolean" type from other TINYINT values, which are not booleans.

We handle a NULL value as NULL, a value of 0 as "false", and any other value is considered "true"... the same way that MySQL interprets an integer value in a boolean context.

We've not encountered any clients that can't handle an integer type.

Answer 2

For MySQL 5.0.3 and higher, you can use BIT or TINYINT.

Here you have a more elaborated answer

Answer 3

FOR mysql use the type BOOLEAN NOT NULL type like

ALTER TABLE `products` ADD `test` BOOLEAN NOT NULL;

if Boolean not working use tiny int like TINYINT(1) NOT NULL eg. ALTER TABLEproductsADDtestTINYINT(1) NOT NULL;

important TINYINT(1) value inside tinyint is necessary.