CODEWITHSUNDEEP
mysqluniqueidentifiercase-sensitivecase-insensitive
BassMHL
BassMHL

Reputation: 9047

MySQL transform case-sensitive unique field into unique case-insensitive field

This is an interesting challenge question as there can be multiple ways to solve this problem :)

I have an ID column that is unique but case-sensitive, ex: 

----- ID ------
0018000001K6dkh -> record 1
0018000001K6dkH -> record 2 (different from record 1)

As MySQL is case insensitive in utf8, it is considering the two ID values as identical: 

SELECT COUNT(*) FROM table WHERE id='0018000001K6dkh' //returns 2 instead of 1
SELECT COUNT(*) FROM table WHERE id='0018000001K6dkH' //returns 2 instead of 1

I need to build a MySQL function that I can apply to this column to transform all IDs into a case-insensitive and unique IDs, ex: 

function('0018000001K6dkh') = something
function('0018000001K6dkH') = something different
function('0018000000ttzlB') = something even different 
function('0018000000tTzlB') = something even more different

Note: I don't want to use collations, because I would have to change all the WHEREs and JOINs in my application (Laravel PHP). I want to change the IDs permanently.

Upvotes: 0

Views: 137

Answers (2)

Uueerdo
Uueerdo

Reputation: 15951

According the official MySQL documentation you can set the collation on a per column basis (*see data_type section for CHAR, VARCHAR, TEXT, ENUM, and SET types).

This should change the default manner in which they are compared to string literals and each other, but I am not familiar with the rules used when comparing fields of differing collation with each other.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

This is from mysql http://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html

To cause a case-sensitive comparison of nonbinary strings to be case insensitive, use COLLATE to name a case-insensitive collation. The strings in the following example normally are case sensitive, but COLLATE changes the comparison to be case insensitive:

SET @s1 = 'MySQL' COLLATE latin1_bin;
SET @s2 = 'mysql' COLLATE latin1_bin;
SELECT @s1 = @s2;

return false

using @s1 COLLATE latin1_swedish_ci = @s2;

SELECT @s1 COLLATE latin1_swedish_ci = @s2;

return true

or use BINARY 

SELECT COUNT(*) FROM table WHERE BINARY  id='0018000001K6dkh'
SELECT COUNT(*) FROM table WHERE  BINARY id='0018000001K6dkH'

Upvotes: 1

Related Questions