Is there a way to have MySQL GROUP BY utilize trailing whitespace?

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)

char values are space-padded when they are stored, so all trailing whitespace is assumed to be padding and removed when retrieving values. varchar fields will retain trailing whitespace, but by default it is removed for comparisons. (see The CHAR and VARCHAR types)

To retain the trailing whitespace for varchar values, use the binary operator. Something like SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY BINARY foo should do what you want.

StackExchange Database Administrators Q#1607, answer score: 11