MySQL变音符号不敏感搜索(阿拉伯语)

2021-11-17 00:00:00 arabic mysql diacritics

我无法使用阿拉伯文本进行变音符号不敏感搜索.

I have trouble making a diacritic insensitive search with arabic text.

我已经为相关表测试了多种设置:utf8 和 utf16 中的编码以及 utf8_general_ci、utf16_general_ci 和 utf16_unicode_ci 中的排序规则.

I have tested multiple setups for the table in question: encodings in utf8 and utf16 as well as collations in utf8_general_ci, utf16_general_ci and utf16_unicode_ci.

该搜索适用于 åä 特殊字符.即:

The search works for åä special characters. I.e:

select * from test where text like '%a%'

将返回文本为 a、å 或 ä 的列.但它不适用于阿拉伯语变音符号.即，如果文本是 بِسْمِ 并且我搜索 بسم，我没有得到任何点击.

Would return columns where text is a, å or ä. But it won't work with the Arabic diacritics. I.e if the text is بِسْمِ and I search for بسم, I don't get any hits.

任何想法如何通过这个?

Any ideas how to get pass this?

真正的用途稍后将是 PHP(一个搜索功能)，但我直接在 MySQL 数据库中工作，只是为了在将其移植到 PHP 之前进行测试.

The real usage will later be PHP (a search function), but I'm working directly in the MySQL db just for testing before I port it over to PHP.

(来自评论)

CREATE TABLE test (
    ↵ id int(11) unsigned NOT NULL AUTO_INCREMENT,
    ↵ text text COLLATE utf8_unicode_ci,
    ↵ PRIMARY KEY (id)↵
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

推荐答案

(这不是答案"，而是解决方案".)

(This is not an "answer", but a "resolution".)

LIKE 似乎不适用于您的阿拉伯语字符串.我不知道它失败了多少.我建议您在 http://bugs.mysql.com 上编写错误报告.这是一个测试用例，表明 LIKE '...' 和 LIKE '%...%' 都找不到两个字符串，而 '=' 有效:

It seems that LIKE does not work with your Arabic string. I don't know how much more it fails on. I recommend you write a bug report at http://bugs.mysql.com . Here is a test case that shows that neither LIKE '...' nor LIKE '%...%' finds both strings, whereas '=' works:

CREATE  TABLE so28863402 (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    txt text COLLATE utf8_unicode_ci,   -- deliberate choice of COLLATION
    PRIMARY KEY (id)
) ENGINE=InnoDB
        DEFAULT CHARSET=utf8;
INSERT INTO so28863402 (txt) VALUES
    (UNHEX('D8A8D990D8B3D992D985D990')),  -- Using hex to avoid any copy/paste issues
    (UNHEX('D8A8D8B3D985'));  -- The values should compare equal
SELECT id, txt, HEX(txt) FROM so28863402;
SELECT txt, COUNT(*) FROM so28863402 GROUP BY txt; -- GROUP BY finds them equal.
SELECT * from so28863402
    WHERE txt = 'بسم';   -- Finds both rows (correct)
SELECT * from so28863402
    WHERE txt LIKE '%بسم%';  -- Finds one row (incorrect)
-- Further checks:
SELECT * FROM so28863402 WHERE txt  =   UNHEX(  'D8A8D8B3D985'  );
SELECT * FROM so28863402 WHERE txt LIKE UNHEX(  'D8A8D8B3D985'  );
SELECT * FROM so28863402 WHERE txt LIKE UNHEX('25D8A8D8B3D98525'); -- x25 is '%'

相关文章