MySQL Change Collation Only on Insert or Unique Key

I have a column that's collated as utf8_unicode_ci. I have to keep this because there are a lot of queries already written against it that all assume that collation. My problem is that I want to be able to insert stuff with different accents (i.e. apfel and äpfel separately). Because there's a unique key on this column and utf8_unicode_ci thinks apfel and äpfel are the same, is there a simple way I can either change the collation only on the unique key constraint, or only on the insert to utf8_bin, so I can get stuff into the table that differs only in accents without changing the column's collation?

Answers


There is no "easy" solution - the column collation defines the equality and the order of values. You just can't have both apfel and äphel in the column without breaking the unique constraint.

One thing you can do is add a new column, collated with utf8_bin, and drop the unique key from the first column and make this new column key. Then add a trigger that copies values inserted or updated in the original column to the new column.


Need Your Help

How to manage multiple inserts based on a variable

php sql

So basically I have this game where you can choose between 4 classes. Now each class has its own set of items.

Rails, MySQL, Unicode data and latin1 tables - Where to go from here?

mysql ruby-on-rails utf-8 character-encoding

I'm not 100% sure on the particulars, so I'd love someone straightening me out, but I'll forge ahead with what I think is going on...