Alex Rivera | Logout

Boolean vs tinyint(1) for boolean values in MySQL

Asked 2010-09-20T13:21:52.453
161

What column type is best to use in a MySQL database for boolean values? I use boolean but my colleague uses tinyint(1).

Edit
Report

1 Answer

48

boolean isn't a distinct datatype in MySQL; it's just a synonym for tinyint. See this page in the MySQL manual. See the quotes and examples down below from the dev.mysql.com/doc/

BOOL, BOOLEAN These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

    mysql> SELECT IF(0, 'true', 'false');
    +------------------------+
    | IF(0, 'true', 'false') |
    +------------------------+
    | false                  |
    +------------------------+
    mysql> SELECT IF(1, 'true', 'false');
    +------------------------+
    | IF(1, 'true', 'false') |
    +------------------------+
    | true                   |
    +------------------------+
    mysql> SELECT IF(2, 'true', 'false');
    +------------------------+
    | IF(2, 'true', 'false') |
    +------------------------+
    | true                   |
    +------------------------+

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+
mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+
mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+
mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 
answered 2010-09-20T13:26:57.200

Your Answer