I’ve come across a real "gotcha" when using MySQL 4.0* to store string data. It appears that when using TEXT, VARCHAR or CHAR fields, MySQL does not treat trailing spaces as significant. In other words, the following two queries will always return exactly the same rowset, even though the comparisons are different:
SELECT * FROM table WHERE entry = ‘M’;
SELECT * FROM table WHERE entry = ‘M ‘;
The MySQL folks insist this isn’t a bug but a known feature (http://bugs.mysql.com/bug.php?id=5412), although I am yet to find an explanation of why they have decided it should be this way. Especially in the case of a TEXT field, where you might expect the comparison would be byte-for-byte. Incidentally, I have found a related problem - often, you can’t query on empty TEXT fields. Even if you have a number of fields in your table that have an empty (zero bytes) TEXT entry field, the following query does not work:
SELECT * FROM table WHERE entry = ‘’
You have to do something else, like this:
SELECT * FROM table WHERE LENGTH(entry)=0
Which is, of course, far uglier and slower as well.
But there is a solution. The trick is to convert your field from TEXT (or VARCHAR) to a BLOB. A BLOB field is similar to a TEXT field, but string comparisons are done more exactly by matching byte values. Comparisons on a BLOB field do NOT ignore trailing spaces. The only caveat is that because it is comparing byte values, string comparisons on a BLOB field are case-sensitive, unlike on a TEXT of VARCHAR field which are case-INsensitive. If you still need a case-insensitive comparison, there is a workaround, however: cast the value to a regular "cased" character set (such as Latin) before comparison:
SELECT * FROM table WHERE CONVERT(entry USING latin1) = ‘M ‘
*I am not sure whether this problem occurs in MySQL 5.0+ as well - I think it does, since the MySQL developers have obviously seen no reason to change things.