{"id":68,"date":"2010-01-12T23:06:42","date_gmt":"2010-01-13T04:06:42","guid":{"rendered":"http:\/\/signaturewebmedia.com\/blog\/?p=68"},"modified":"2010-01-13T03:59:57","modified_gmt":"2010-01-13T08:59:57","slug":"mysql-case-insesitive-group-by","status":"publish","type":"post","link":"http:\/\/signaturewebmedia.com\/blog\/68\/mysql-case-insesitive-group-by\/","title":{"rendered":"MySQL: case insesitive “GROUP BY”"},"content":{"rendered":"

<\/a><\/p>

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)<\/p>\n

i.e.<\/p>\n

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

+—————+———-+
\n| keyword\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | count(*) |
\n+—————+———-+
\n| secured loans |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 |
\n+—————+———-+
\n1 row in set (0.50 sec)<\/p>\n

I know the max count(*) result should be 3 if the key is case sensitive.<\/p><\/blockquote>\n

We found that this note: http:\/\/bugs.mysql.com\/bug.php?id=24838<\/a><\/p>\n

According to the note above, it is NOT a bug; however, it does not seem like it is giving a workaround.<\/p>\n

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,\u00a0 used hex() function:<\/p>\n

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

+—————+—————————-+———-+
\n| keyword\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | hex(keyword)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | count(*) |
\n+—————+—————————-+———-+
\n| Secured Loans | 53656375726564204C6F616E73 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |
\n| secured loans | 73656375726564206C6F616E73 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |
\n+—————+—————————-+———-+
\n2 rows in set (0.48 sec)<\/p><\/blockquote>\n

We are sure that you can use some other functions to achieve case sensitive group by effects.<\/p>\n","protected":false},"excerpt":{"rendered":"

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)
\ni.e.
\nmysql> select keyword, count(*) from TopKeywords
\n-> where lower(keyword) like ‘secured loans’
\n-> group by keyword;
\n+—————+———-+
\n| keyword\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | count(*) |
\n+—————+———-+
\n| secured loans |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 |
\n+—————+———-+
\n1 row in set (0.50 sec)
\nI know the max count(*) result should be 3 if the key is case sensitive.
\nWe found that this note: http:\/\/bugs.mysql.com\/bug.php?id=24838<\/p>\n

According to the note above, it is NOT a bug; however, it does not seem like it is giving a workaround.
\nScratching 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,\u00a0 used hex() function:
\nmysql> select keyword, hex(keyword), count(*) from TopKeywords
\n-> where lower(keyword) like ‘secured loans’
\n-> group by hex(keyword);
\n+—————+—————————-+———-+
\n| keyword\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | hex(keyword)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | count(*) |
\n+—————+—————————-+———-+
\n| Secured Loans | 53656375726564204C6F616E73 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |
\n| secured loans | 73656375726564206C6F616E73 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |
\n+—————+—————————-+———-+
\n2 rows in set (0.48 sec)
\nWe are sure that you can use some other functions to achieve case sensitive group by effects.<\/p>\n

\r\n\r\n<\/a><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[20,21],"tags":[24,23,196,26,27,25],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts\/68"}],"collection":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":4,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"predecessor-version":[{"id":70,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/posts\/68\/revisions\/70"}],"wp:attachment":[{"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/signaturewebmedia.com\/blog\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}