MySQL and Collation

Question

I have a table with a column using utf8_unicode_ci character set. This table stores Japanese data and my problem is that using this character set, I'm not able to store the same word written in katakana and hiragana because it's considered to be the same word.

For example わたし and ワタシ, which mean I, me.

I know that I can change the character set to utf8_general_ci to resolve this problem but is it possible to bypass this limitation ? I mean, keep utf8_unicode_ci character set and make those two words be inserted?

Is it possible to make this work using CONVERT or CAST operators?

Thanks.

Answer 1

I'm guessing that you are not able to insert these values because they go into column that has PRIMARY or UNIQUE key on it. If you change index type to regular index that doesn't require value uniqueness your problem should go away without having to change the collation.