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