TRUE, FALSE or 0 ,1?

[SHAD]

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 ?

[Octarine]

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.

TINYINT(1) UNSIGNED NOT NULL DEFAULT 0;

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

[SHAD]

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?

[Octarine]

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

[SHAD]

Octarine,thank-you for your time and advice, much appreciated!

TINYINT(1) it is :D