MySQL: case insesitive “GROUP BY”

January 12, 2010 · Posted in database, MySQL 

When we were building some SQL statements to gather some statistics and realized that the “GROUP BY” clause is NOT case sensitive on MySQL… (we are using utf8_unicode_ci as a character set collation)


mysql> select keyword, count(*) from TopKeywords
-> where lower(keyword) like ‘secured loans’
-> group by keyword;

| keyword       | count(*) |
| secured loans |        4 |
1 row in set (0.50 sec)

I know the max count(*) result should be 3 if the key is case sensitive.

We found that this note:

According to the note above, it is NOT a bug; however, it does not seem like it is giving a workaround.

Scratching our head a little and found a easy solution if you need to use the case sensitive GROUP BY clause. The thing is that you need to group by absolutely unique value. In our case,  used hex() function:

mysql> select keyword, hex(keyword), count(*) from TopKeywords
-> where lower(keyword) like ‘secured loans’
-> group by hex(keyword);

| keyword       | hex(keyword)               | count(*) |
| Secured Loans | 53656375726564204C6F616E73 |        1 |
| secured loans | 73656375726564206C6F616E73 |        3 |
2 rows in set (0.48 sec)

We are sure that you can use some other functions to achieve case sensitive group by effects.


4 Responses to “MySQL: case insesitive “GROUP BY””

  1. Justin on June 6th, 2011 10:14 am

    Hey, that’s a good idea!

    Thanks from a random Internet drifter.

  2. kbc on January 30th, 2012 6:02 am

    neat, simple and useful.
    my thanks!!

    an example of mine:

    select substr(name,-4) as ext,count(*)
    from file
    group by hex(ext)
    order by ext


    .jpg 291
    .JPG 92
    .tif 123
    .TIF 2

  3. Peter on November 15th, 2013 9:10 am

    Another way to do this is to simply add the keyword BINARY just before the column you need to test for case sensitivity:

    select keyword, count(*) from TopKeywords
    -> where keyword like ‘secured loans’
    -> group by BINARY keyword;

  4. Gabby on January 13th, 2015 5:20 pm

    Just wanted to say THANK YOU!!!!! You saved me so much panic!

Leave a Reply

Security Code: