MySQL is clinically insane
It’s not like I needed much persuasion, but I’ve come to the conclusion that MySQL is a complete pain to work with — especially if you were expecting the kind of sane behaviour that you get from almost any other relational database.
I’ve just spent the last twenty minutes trying to figure out why my query (apparently) wasn’t working. Here’s something to demonstrate what I was seeing:
CREATE TABLE insanity
(
value VARCHAR(20),
description VARCHAR(50)
);
INSERT INTO insanity VALUES
('Hello', 'a string'),
('', 'an empty string'),
(' ', 'a space'),
(NULL, 'a NULL');
Okay, four rows into a simple table. Only one has a NULL
value. Except
that:
SELECT * FROM insanity;
+-------+-----------------+
| value | description |
+-------+-----------------+
| Hello | a string |
| NULL | an empty string |
| | a space |
| NULL | a NULL |
+-------+-----------------+
Oh, so it looks like MySQL treats blank strings as equivalent to NULL
?
That’s annoying, but I can live with it. So that means:
SELECT * FROM insanity WHERE value IS NOT NULL;
+-------+-----------------+
| value | description |
+-------+-----------------+
| Hello | a string |
| NULL | an empty string |
| | a space |
+-------+-----------------+
Eh? So empty strings aren’t equal to NULL
? Why does it say ‘NULL
’, then?
I wonder what happens if I check for an empty string explicitly…
SELECT * FROM insanity WHERE value = '';
+-------+-----------------+
| value | description |
+-------+-----------------+
| NULL | an empty string |
| | a space |
+-------+-----------------+
No, it really is an empty string (Oh, and strings are also compared without
regard to trailing spaces, which is pretty normal, but good to know). The
problem here seems to be that an empty string is displayed as NULL
by the
mysql
client. This appears to be a known bug, but it’s also supposed
to be fixed in 5.0.22, which is what I appear to be using. Update: The
problem is definitely fixed in the 5.0.41 client. I couldn’t figure out
exactly which bug was the cause, though, so I can’t tell which version the fix
appeared in.
Happily, there seems to be a whole list of gotchas after that one is fixed, so I won’t run out of fun!