MySQL SELECT DISTINCT should be case sensitive?

Question

How do I make MySQL's SELECT DISTINCT case sensitive?

create temporary table X (name varchar(50) NULL);
insert into X values ('this'), ('This');

Now this query:

select distinct(name) from X;

Results in:

this

What's going on here? I'd like SELECT DISTINCT to be case sensitive. Shouldn't that be the default?

Solution 1

Use BINARY operator for that:

SELECT DISTINCT(BINARY name) AS Name FROM X;

You can also CAST it while selecting:

SELECT DISTINCT 
(CAST(name AS CHAR CHARACTER SET utf8) COLLATE utf8_bin) AS Name FROM X;

See this SQLFiddle

Solution 2

I would rather update the column definition to be case sensitive collision.

Like this:

create table X (name VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NULL);
insert into X values ('this'), ('This'); 

SQLFiddle: http://sqlfiddle.com/#!2/add276/2/0

Solution 3

You can use a hashing function (MD5) and then group on that.

SELECT Distinct(MD5(Cat)), Cat FROM (
  SELECT 'Cat'
    UNION ALL
  SELECT 'cat'
) AS BOW

SQL Output:

Comments

[ishmael]

This is nice and simple, but it turns out BINARY messes up non-ASCII characters. If, for example, instead of ('this', 'This') we use (entrée, Entrée'), then SELECT DISTINCT(BINARY(...)) returns blobs. Instead, you can use COLLATE as @roberto-navarro suggested (except not in the CREATE statement) as follows: SELECT DISTINCT NAME COLLATE latin1_bin FROM X;

[openwonk]

Learn something new everyday... Had no idea that this was occurring... Life is complete now