MySQL SELECT DISTINCT should be case sensitive?

mysql sql distinct case-sensitive

ishmael · Oct 19, 2013 · Viewed 12.4k times · Source

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?

Answer

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;