I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table:
CREATE TABLE `myTable` ( `id` int(10) unsigned NOT NULL, `foo` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
And here's an example query I ran (not a production use, but shows what I'm trying to fix):
SELECT foo, CHAR_LENGTH(foo) FROM myTable WHERE foo='bar' GROUP BY foo
Which would return a result set:
| foo | CHAR_LENGTH(foo) | +------+-------------------+ | Bar | 4 |
All well and good, except 'Bar' is length three, not four. So I went to examing the individual entries (No group by):
SELECT foo, CHAR_LENGTH(foo) FROM myTable WHERE foo='bar' | foo | CHAR_LENGTH(foo) | +------+-------------------+ | Bar | 3 | | Bar | 3 | | Bar | 3 | | Bar | 4 | | Bar | 3 |
Now, there are five entries for Bar, four of which correctly report a length 3. But there's one that reports a length of four.
It turns out the one with length four has a space on the end: "Bar ". I would have expected
GROUP BY to treat this as a completely different value and not lump it with the other "Bar"s.
Is there a way to tell MySQL to consider values different if they have trailing (or leading) whitespace?
(running mysql 5.1.55 64-bit on mac osx)