将like与mariadb和汉字一起使用

rpppsulh  于 2023-03-02  发布在  其他
关注(0)|答案(2)|浏览(120)

我回来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和汉字?谢谢你提前

nxagd54h

nxagd54h1#

utf8mb4_general_ci是一个简化的快速排序/比较实现。它不遵循Unicode规则,可能导致不需要的排序或比较。
您应该使用utf8mb4_unicode_ci,而不是,它基于、排序和比较的官方unicode规则。

select * from chartable where k1 like '%𣅀%' collate utf8mb4_unicode_ci;

hex('%𣅀%')显示为hex('?'),因为在标识符名称中使用的unicode字符仅限于〈+U10000的字符(另请参见https://mariadb.com/kb/en/identifier-names/)。

liwlm1x9

liwlm1x92#

感谢您的回复。问题是我已经从utf8mb4_unicode_ci更改为utf8mb4_general_ci,因为utf8mb4_unicode_ci也发生了类似的问题,正如我在此线程中被告知的:[https://stackoverflow.com/questions/74975518/false-duplicate-with-mariadb-and-chinese-characters]让我们在图表中添加以下两个字符:

INSERT INTO chartable VALUES ('⺌'), ('⺍');

然后使用这两个归类测试SELECT

MariaDB [test]> select * from chartable  where k1='⺌' collate utf8mb4_unicode_ci;
+------+
| k1   |
+------+
| ⺌   |
| ⺍   |
+------+

MariaDB [test]> select * from chartable  where k1='⺌' collate utf8mb4_general_ci;
+------+
| k1   |
+------+
| ⺌   |
+------+

这里我注意到正确的答案来自utf8mb4_general_ci排序规则,而utf8mb4_unicode_ci没有返回正确的答案。这两个排序规则似乎都适合某些unicode字符,而不适合其他字符。如何管理这个问题?

相关问题