MySQL: ENUM’s and booleans

This is one i ran into a few years back, when an entire project was built on a database using ENUM’s as bools. Unfortunately, it was for me to figure out what caused some weird bugs involved. This is a run-down of what happens when you actually use ENUM’s as (evaluating) bools:

mysql> select '0'=0, '1'=1, 0='0', 1='1';
+-------+-------+-------+-------+
| '0'=0 | '1'=1 | 0='0' | 1='1' |
+-------+-------+-------+-------+
|     1 |     1 |     1 |     1 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)

ENUM‘s store strings, right? Let’s use them for booleans.

CREATE TEMPORARY TABLE t(b ENUM('0','1') NOT NULL);

Shouldn’t be a problem.

mysql> insert into t(b) values(1), (0);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1
 
mysql> select * from t;
+---+
| b |
+---+
| 0 |
|   |
+---+
2 rows in set (0.00 sec)

Hmm….

mysql> select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 0 |   1 |   0 |
|   |   0 |   1 |
+---+-----+-----+
2 rows in set (0.00 sec)

What … 😕 How … 😕

Well….

mysql> truncate t;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t values('1'),('0');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from t;
+---+
| b |
+---+
| 1 |
| 0 |
+---+
2 rows in set (0.00 sec)

Phew 😮 🙂

mysql> select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 1 |   0 |   0 |
| 0 |   1 |   0 |
+---+-----+-----+
2 rows in set (0.00 sec)

Excuse… what…. what?! 😕 8)7

Let’s see….

mysql> select b,IF(b,1,0),IF(b=1,1,0) from t;
+---+-----------+-------------+
| b | IF(b,1,0) | IF(b=1,1,0) |
+---+-----------+-------------+
| 1 |         1 |           0 |
| 0 |         1 |           1 |
+---+-----------+-------------+
2 rows in set (0.00 sec)

😮 😀 😮

mysql> SELECT NOT(b) FROM t;
+--------+
| NOT(b) |
+--------+
|      0 |
|      0 |
+--------+
2 rows in set (0.00 sec)

So, to sum up, please use INT’s for bools, not enums. And if you do care to use enum’s for whatever reason, never use numeric values to imply int-to-bool casting in expressions.

Original post at drm.tweakblogs.net

This entry was posted in Development and tagged . Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.

One Trackback