mysqlsqldistinctcase-sensitive

MySQL SELECT DISTINCT should be case sensitive?

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

  • 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