End Point

News

Welcome to End Point's blog

Ongoing observations by End Point people.

Sanity, thy name is not MySQL

Probably old news, but I hit this MySQL oddity today after a long day dealing with unrelated crazy stuff and it just made me go cross-eyed:
CREATE TABLE foo (id integer, val enum('','1'));
INSERT INTO foo VALUES (1, '');
INSERT INTO foo VALUES (2, '1');
SELECT * FROM foo WHERE val = 1;
What row do you get? I'll wait while you second- and third-guess yourself. It turns out that the "enum" datatype in MySQL just translates to a set of unique integer values. In our case, that means:
  • '' == 1
  • '1' == 2
So you get the row with (1,''). Now, if that doesn't confuse readers of your code, I don't know what will.

1 comment:

Mark Johnson said...

The enum types always have a dual nature. I think of the list as an array, and you can either reference the value in string context but the subscript in numeric context.

But it's even more fun than that. The array indices run 1-N, and 0 is saved for any writes that aren't specified in the list, which translate in string context to empty string.

Thus, if you then add:

INSERT INTO foo (3, 'foo');

You will find that row with id 3 will match along with id 1 where val = '', *but* will diverge from id 1 between val = 1 and val = 0. Yay!

Further, if you GROUP BY val, you'll see '' occupy two rows, since under the hood it must be grouping according to the integer context, and not the string value.

Finally, you can use all of this to create a pseudo boolean field out of enum, since MySQL uses integer 1/0 as its true/false:

mysql> create table foo (id integer primary key not null auto_increment, bool enum('1') not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (bool) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bool) values (0);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from foo;
+----+------+
| id | bool |
+----+------+
| 1 | 1 |
| 2 | |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from foo where bool;
+----+------+
| id | bool |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from foo where not bool;
+----+------+
| id | bool |
+----+------+
| 2 | |
+----+------+
1 row in set (0.00 sec)

mysql>

Good times!