Our Blog

Ongoing observations by End Point people

Sanity, thy name is not MySQL

By Jeff Boes · Thursday, April 10, 2014

Tags: 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.

Comments

Archive