27

It seems I should use tinyint(); but I don't know how to implement it?

The question is what is your recommendation if I need to have a boolean field in MySQL DB and modify it´s value with PHP

Johan
  • 74,508
  • 24
  • 191
  • 319
Trufa
  • 39,971
  • 43
  • 126
  • 190
  • A boolean field can also be interpreted as 1's and 0's so having a tinyint field will result in a faster database. And if you index it even faster. All of these answers below are off value to you! @Felix @Matthew @Haim good job – Etienne Marais Oct 14 '10 at 07:43
  • @etbal Yes indeed, all VERY helpful! – Trufa Oct 14 '10 at 07:45

5 Answers5

46

Yep, TINYINT(1) is the way to go... you can also use BOOL or BOOLEAN which are synonyms (so it does not make a difference).

0 evaluates to false in PHP and 1 to true (actually, any other number than 0 evaluates to true, but 1 is normally used).

Felix Kling
  • 795,719
  • 175
  • 1,089
  • 1,143
4

I prefer none of bool, BIT, TINYINT(1). because none of them are actually boolean. You can check the following link for 'why':

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

I would better use : ENUM ('false', 'true') not null - as datatype. You can pass 'true' or 'false' (as strings) from PHP. And it will take only 1 byte to store it!

Muktadir
  • 115
  • 8
  • Why not ENUM ('0', '1') instead – tormuto Aug 26 '15 at 09:54
  • 3
    Actually the string `'false'` is, ironically, truey in PHP. – jonbaldie Dec 06 '15 at 20:24
  • @tormuto yes, you can. that would help auto-type casting in PHP when reading. But with ENUM it's not a safe practice to do it as you may forget to pass strings from PHP. What would happen if someone mistakenly declares ENUM('1', '0')? I saw many codes like ('yes', 'no'). – Muktadir Dec 25 '15 at 23:05
2

You're correct that the general solution is tinyint(1). You can use BOOL for short:

CREATE TABLE example (
         flag BOOL
       );
Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
2

you have option of tinyint(1) or bit

insert 0 or 1 to this field

see this post of the difference :

Tinyint vs Bit

Community
  • 1
  • 1
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
0

I think since you actually want to enforce a boolean (0,1) constraint on a mysql table field, the best shot is uning enum

CREATE TABLE table_name(
    boolean_field_name ENUM('0', '1')
);
tormuto
  • 587
  • 5
  • 16