Jump to content

TRUE, FALSE or 0 ,1?


Recommended Posts

I know this may seem like a pretty newbie question to most of you but I have seen some databases which use the variable 'INT'(roughly ranging around 2147483647) to hold the most miniscule of values(0&1) when they could simply use the: TRUE&FALSE . I just wanna know, is there any specific reason or advantage for going about it this way, or is it simply the result from lack- of-concern ?

Link to comment
Share on other sites

A lot of databases MySQL included does not have a BOOL type, rather they revert to synonyms, often by using TINYINT(x) which is one byte wide. A full INT(x) is 4 bytes wide so there is a little saving to be had in using the smaller variant, but that is very much schema dependant. If you needed to save space, then you wouldn't be asking the question.


is possibly a reasonable bool value - 0 being false, anything else (usually 1) being true. There is some argument for using the BIT(x) field, but I'd question its portability and support in 3rd party tools.

See 11.2 Numeric Types

Link to comment
Share on other sites

Octarine, very well explained, thanks for replying. I was actually under the notion that the-BOOL( TRUE&FALSE) was actually a more concrete method of going about it since there's only two values. But I guess TINYINT being 1 byte wide and fused with the logic: 0 being false and anything else true(1+) is an equally appropriate method; given that MySQL doesn't support BOOL type, right?

Link to comment
Share on other sites

You could use an ENUM('True', 'False') but its not overly elegant and in fact needs two bytes (ENUMs are stored as a 16-bit unsigned internally iirc), or even a (var)char field with 1/0, yes/no, true/false etc but your in-code logic I find is simplified by the TINYINT(1) method since PHP especially thinks that '0' and false are loosely equivalent.

Enums also have some unexpected properties since the database doesn't constrain their contents:

create table `test`
 `id`   int unsigned not null,
 `demo` enum('true', 'false'),

 primary key (`id`)

insert into `test` (`id`, `demo`) values (1, 0), (2, 1), (3, 2);
select `id`, `demo`, `demo` + 0 from `test`;

| id | demo  | `demo` + 0 |
|  1 | 0     |            |
|  2 | true  |          1 |
|  3 | false |          2 |


Stick to TINYINT(1) ! :D

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...