Как сделать MySQL осведомленным о многобайтовых символах в LIKE и REGEXP?

У меня есть таблица MySQL с двумя столбцами, как с utf8_unicode_ci. Он содержит следующие строки. За исключением ASCII, второе поле также содержит кодовые точки Unicode, такие как U + 02C8 (MODIFIED LETTER VERTICAL LINE) и U + 02D0 (MODIFIED LETTER TRIANGULAR COLON).

 word   | ipa
--------+----------
 Hallo  | haˈloː
 IPA    | ˌiːpeːˈʔaː

Мне нужно искать второе поле с LIKE и REGEXP, но MySQL (5.0.77), кажется, интерпретирует эти поля как байты, а не как символы.

SELECT * FROM pronunciation WHERE ipa LIKE '%ha?lo%';  -- 0 rows
SELECT * FROM pronunciation WHERE ipa LIKE '%ha??lo%'; -- 1 row

SELECT * FROM pronunciation WHERE ipa REGEXP 'ha.lo';  -- 0 rows
SELECT * FROM pronunciation WHERE ipa REGEXP 'ha..lo'; -- 1 row

Я абсолютно уверен, что данные хранятся правильно, так как кажется хорошим, когда я его извлекаю и хорошо отображается в phpMyAdmin. Я нахожусь на общем хосте, поэтому я не могу установить программы.

Как я могу решить эту проблему? Если это невозможно: существует ли правдоподобная работа, которая не требует обработки всей базы данных с помощью PHP каждый раз? Есть 40 000 строк, и я не настроен на использование MySQL (или UTF8, если на то пошло). У меня есть только доступ к PHP и MySQL на хосте.

Изменить: Существует открытый 4-летний отчет об ошибке MySQL, Ошибка # 30241 Проблемы с регулярным выражением, который отмечает, что двигатель regexp работает побайтно. Таким образом, я ищу работу.

Ответы

Ответ 1

EDITED, чтобы включить исправление в действительный критизм

Используйте функцию HEX(), чтобы сделать ваши байты шестнадцатеричными, а затем используйте RLIKE, например:

select * from mytable
where hex(ipa) rlike concat('(..)*', hex('needle'), '(..)*'); -- looking for 'needle' in haystack, but maintaining hex-pair alignment.

Нечетные символы unicode последовательно отображают их шестнадцатеричные значения, поэтому вы просматриваете стандартные символы 0-9A-F.

Это работает и для "нормальных" столбцов, вам просто не нужно.

p.s. @Kieren (действительная) точка, адресованная с помощью RLIKE для принудительного использования пар char

Ответ 2

Я не настроен на использование MySQL

Postgres, похоже, справляется с этим довольно хорошо:

test=# select 'ˌˈʔ' like '___';
 ?column? 
----------
 t
(1 row)

test=# select 'ˌˈʔ' ~ '^.{3}$';
 ?column? 
----------
 t
(1 row)

Если вы спуститесь по этой дороге, обратите внимание, что оператор Postgres ilike совпадает с оператором MySQL like. (В Postgres like зависит от регистра.)

Для решения, специфичного для MySQL, вы не можете работать, привязав определенную функцию пользовательскую функцию (возможно, привяжите библиотека ICU?) в MySQL.

Ответ 3

У вас проблемы с UTF8? Устраните их.

Сколько специальных символов вы используете? Вы используете только локализационные буквы, верно? Итак, мой совет: Напишите функцию, которая преобразует spec-символы в обычные символы, например. "æ" → "A" и т.д. и добавьте столбец в таблицу, в которой хранится это преобразованное значение (вам нужно сначала преобразовать все значения и каждую вставку/обновление). При поиске вам просто нужно преобразовать строку поиска с той же функцией и использовать ее в этом поле с регулярным выражением.

Если слишком много специальных символов, вы должны преобразовать его в multi- char. 1. Избегайте нахождения "aa" в последовательности "ba ab", используя некоторый префикс, например "@ba @ab". 2. Избегайте нахождения "@a" в "@ab" использовать токены фиксированной длины, скажем, 2.