How to set schema collation in MySQL for Japanese

问题:

I am having a problem with collation. I want to set collation to support the Japanese language. For example, when table.firstname has 'あ', a query with 'ぁ' should return the record. Thanks in advance.

回答1:

That's like "uppercase" and "lowercase", correct?

mysql> SELECT 'あ' = 'ぁ' COLLATE utf8_general_ci;
+---------------------------------------+
| 'あ' = 'ぁ' COLLATE utf8_general_ci   |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

mysql> SELECT 'あ' = 'ぁ' COLLATE utf8_unicode_ci;
+---------------------------------------+
| 'あ' = 'ぁ' COLLATE utf8_unicode_ci   |
+---------------------------------------+
|                                     1 |
+---------------------------------------+

mysql> SELECT 'あ' = 'ぁ' COLLATE utf8_unicode_520_ci;
+-------------------------------------------+
| 'あ' = 'ぁ' COLLATE utf8_unicode_520_ci   |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+

I recommend changing your column to be COLLATION utf8_unicode_520_ci (or utf8mb4_unicode_520_ci).

If you expect to be including Chinese, then be sure to use utf8mb4. (Perhaps this advice applies to Kanji, too.)