3

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.

Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
Chung Do
  • 79
  • 9

1 Answers1

3

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.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • thanks for answer, if possible 'あ' = 'ア', what collation or something. – Chung Do Aug 21 '16 at 13:37
  • Should a Hiragana A be treated as equal to a Katakana A? `...unicode_520_ci` does treat them equal. That is the best available in utf8/utf8mb4. See `SHOW COLLATION LIKE '%jap%';` for what is available in other _character sets_. – Rick James Aug 21 '16 at 15:30