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.

Answer 1

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                          |
+--------------------------------+