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

Question

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.

lol you are right, it works. Do you want to answer the question? — BassMHL
– BassMHL, Commented Nov 30, 2016 at 20:43

Uueerdo · Accepted Answer · 2016-11-30 21:15:23Z

2

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.

answered Nov 30, 2016 at 21:15

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BassMHL Over a year ago

I changed the collation of the field from utf8_unicode_ci to utf8_bin and all the WHEREs and JOINs behave as expected (case-sensitive)

ScaisEdge · Accepted Answer · 2016-11-30 20:54:25Z

1

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'

answered Nov 30, 2016 at 20:54

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

BassMHL Over a year ago

I changed the collation of the field from utf8_unicode_ci to utf8_bin and all the WHEREs and JOINs behave as expected (case-sensitive)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related