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:
#!shell
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.
#!shell
CREATE TEMPORARY TABLE t(b ENUM('0','1') NOT NULL);
Shouldn't be a problem.
#!shell
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....
#!shell
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....
#!shell
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 :o :)
#!shell
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....
#!shell
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)
:o :D :o
#!shell
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.