MySQL tinyint to boolean

Question

So I have a tinyint datatype column in my database where I store values from 0 to 2.

It is my understanding that MySQL treats any non-zero number in a tinyint column as true (in this case 1 and 2) and the 0 as false. However, when I perform a query that retrieves certain information from the table where the tinyint row is true, it only works for the rows that have 1 as the value. In other words, the rows that have 2 as a value are not seen as true by the query (and the 0 is seen as false). The query would go something like:

SELECT data FROM table WHERE active=true

Where active is of datatype tinyint and it has been previously assigned values of 0, 1 or 2 depending on the case.

I'm fairly new to MySQL, so I don't know if there's some detail that I could be missing here, but I can't figure out why it's not returning the requested data on the rows with 2 as the value. Any thoughts could help here.

Iulian Onofrei · Accepted Answer · 2020-10-07 09:19:31Z

6

To MySQL, because 2 is neither 1 nor 0, then 2 is neither TRUE nor FALSE. Consider:

SELECT 2 = TRUE, 2 = FALSE;

This returns:

| 2 = TRUE | 2 = FALSE |
| -------- | --------- |
| 0        | 0         |

You would need to express the condition differently, like:

SELECT data FROM table WHERE active > 0

This will also work (any non-zero value is considered true, see further):

SELECT data FROM table WHERE active;

This behavior is documented in the manual, which states:

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 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

edited Oct 7, 2020 at 9:19

Iulian Onofrei

9,85611 gold badges73 silver badges118 bronze badges

answered Mar 26, 2019 at 23:39

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick Over a year ago

SELECT data FROM table WHERE active will (not should) work as (as the manual says) any non-zero value is considered true. You might also want to consider adding SELECT IF(2, 'true', 'false'); and SELECT IF(0, 'true', 'false'); to your examples. Otherwise a really comprehensive answer which SO could use more of...

GMB Over a year ago

@Nick: thanks for your positive feedback and suggestions! I updated my answer accordingly.

dmc4 Over a year ago

Thank you so much! Using "WHERE active" instead of "WHERE active=true" worked perfectly.

Collectives™ on Stack Overflow

MySQL tinyint to boolean

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related