CODEWITHSUNDEEP
phpmysqldatabasemysqliboolean-logic
Ataboy Josef
Ataboy Josef

Reputation: 2101

How to store boolean datatype in MySQL?

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?

Upvotes: 2

Views: 6037

Answers (3)

spencer7593
spencer7593

Reputation: 108410

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.

Upvotes: 3

Bruce
Bruce

Reputation: 1671

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.

Upvotes: 1

aurelius
aurelius

Reputation: 4076

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

Here you have a more elaborated answer

Upvotes: 1

Related Questions