Sanity, thy name is not MySQL
By Jeff Boes
April 10, 2014
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.
Comments