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.
Comments
4 Responses to “MySQL: case insesitive “GROUP BY””
Leave a Reply
Hey, that’s a good idea!
Thanks from a random Internet drifter.
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
says:
.jpg 291
.JPG 92
.tif 123
.TIF 2
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;
Just wanted to say THANK YOU!!!!! You saved me so much panic!