MySQL: case insesitive “GROUP BY”
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)
i.e.
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: http://bugs.mysql.com/bug.php?id=24838
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.