Solution to DISTINCT Case Problem

First off, take a look at my post from yesterday.  I was given to understand that DISTINCT is not case-sensitive.  The issue at hand actually has less to do with DISTINCT itself, and more to do with the character set and collation that are used when comparing strings.  The best way to describe this comes from the MySQL documentation:


So it’s more about how the table is configured to sort strings.  This is the collation.  In my case, the table is set to have a collation of “utf8_unicode_ci”.  When I grab all of the unique passwords, then, I can use a utf8 binary collation:

select distinct password collate utf8_bin from table;

This will maintain all of the upper-case and lower-case permutations of a given password.  By doing this, I can generate all of the hashes of the absolutely unique passwords known to have been used by someone from the data set that I have.

So the next time you use DISTINCT in a query, you may want to make sure you’re getting what you think you are.