我回来utf8mb4字符的问题。我使用的mariadb服务器版本:10.3.37-MariaDB-0ubuntu0.20.04.1在安装了Linux Mint 20.3的本地计算机上运行Ubuntu 20.04。下面是一个测试表:
CREATE TABLE chartable (
k1 VARCHAR(5)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO chartable (k1) values
('食𣅀C' ),
('食B𡃀'),
('亰BC');
现在的问题是:
x一个一个一个一个x一个一个二个x
一组1行(0,001秒)
I notice the first query doesn't return what I expected, which is 食𣅀C Second query displays a ? instead of 𣅀 I ensured that character set and collations are consistent, database and table have the same character set and collation, utf8mb4_general_ci as I was told in this thread https://stackoverflow.com/questions/74975518/false-duplicate-with-mariadb-and-chinese-characters/74975829#74975829
MariaDB [nihongo]> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
以及:
MariaDB [nihongo]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
我已经测试了其他表,并得到了相同类型的问题。你能告诉我,我做错了什么,以及如何正确地使用喜欢与mariadb和汉字?谢谢你提前
2条答案
按热度按时间nxagd54h1#
utf8mb4_general_ci是一个简化的快速排序/比较实现。它不遵循Unicode规则,可能导致不需要的排序或比较。
您应该使用utf8mb4_unicode_ci,而不是,它基于、排序和比较的官方unicode规则。
hex('%𣅀%')
显示为hex('?')
,因为在标识符名称中使用的unicode字符仅限于〈+U10000的字符(另请参见https://mariadb.com/kb/en/identifier-names/)。liwlm1x92#
感谢您的回复。问题是我已经从utf8mb4_unicode_ci更改为utf8mb4_general_ci,因为utf8mb4_unicode_ci也发生了类似的问题,正如我在此线程中被告知的:[https://stackoverflow.com/questions/74975518/false-duplicate-with-mariadb-and-chinese-characters]让我们在图表中添加以下两个字符:
然后使用这两个归类测试SELECT
这里我注意到正确的答案来自utf8mb4_general_ci排序规则,而utf8mb4_unicode_ci没有返回正确的答案。这两个排序规则似乎都适合某些unicode字符,而不适合其他字符。如何管理这个问题?