Php – How to store boolean datatype in MySQL

boolean-logicdatabasemysqlmysqliphp

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?

Best Solution

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.