LINUX.ORG.RU

Регистронезависимый поиск в MySQL

 ,


0

1

Гуглил, экспериментировал, экскрементировал кирпичами весь день.
MySQL версии 5.1.66, БД была создана запросом:

CREATE DATABASE DbName CHARACTER SET utf8 COLLATE utf8_general_ci;
Не работает регистронезависмый поиск следующими способами:
SET NAMES 'utf8';

SELECT `id` FROM TableName WHERE LOWER(`title`) LIKE LOWER('%$search%');
SELECT `id` FROM TableName WHERE `title` LIKE COLLATE UTF8_GENERAL_CI = '%$search%';
Кто знает, как одолеть?

Точно не скажу, но. Второй вариант вообще непонятно как к регистронезависимости относится. Первый вариант я бы проверил в лоб - SELECT LOWER('%');

Ну и самый главный вопрос остался за кадром :-) $search - в какой кодировке?

Anoxemian ★★★★★ ()
Ответ на: комментарий от Anoxemian

Второй вариант вообще непонятно как к регистронезависимости относится.

UTF8_GENERAL_CI — case insensitive.

Первый вариант я бы проверил в лоб - SELECT LOWER('%');

Запрос работает, но чувствителен к регистру.

$search - в какой кодировке?

UTF-8, инфа 100% :)

metrokto ★★ ()
Ответ на: комментарий от metrokto

В рамках продолжения тупняка и не зная контекста - Замени ' на " :-)

Просто если это консоль, то не ясно само назначение $search.

Anoxemian ★★★★★ ()
Ответ на: комментарий от Anoxemian

Это в скрипте. Я пробовал в консольном клиенте набирать

SELECT * FROM TableName WHERE LOWER(`field`) LIKE LOWER('%Текст%');
Не работает. При этом среди полей field есть текст и Текст. Находятся только поля, в которых регистр текста совпадает (то есть lower не работает).

metrokto ★★ ()
Ответ на: комментарий от metrokto

т.е.

SELECT * FROM TableName WHERE `field` = 'текст';

отрабатывает некорректно? это фантастика же, если гарантированно есть совпадающая запись.

Anoxemian ★★★★★ ()
Ответ на: комментарий от Anoxemian

Если упростить, то

SELECT LOWER('Текст') = LOWER('Текст');
-- работает

SELECT LOWER('Текст') = LOWER('текст');
-- не возвращает результат

metrokto ★★ ()
Ответ на: комментарий от metrokto

упрости дальше. убери заветное LOWER. Результат меняется ?

Anoxemian ★★★★★ ()
Ответ на: комментарий от Anoxemian

В консоли (на «холостом» запросе) это работает, с реальной таблицей — нет.

metrokto ★★ ()
Ответ на: комментарий от metrokto

А в консоли utf?

Лучше перестрахуйся и проверь, как mysql воспринимает строку:

SELECT COLLATION('%$search%')

Кстати в документации COLLATE ... ставят после аргумента. Может это имеет значение?

ziemin ★★ ()
Ответ на: комментарий от metrokto

Ну, значит, mysql отрабатывает правильно. Проблема в данных. Т.е. при создании базы и таблицы можно указать, конечно utf8_general_ci. И при этом записать туда вполне себе не utf8.

Anoxemian ★★★★★ ()
Ответ на: комментарий от ziemin

Кстати в документации COLLATE ... ставят после аргумента. Может это имеет значение?

Этот вариант на stackoverflow'е нашел. Пробовал как в документации, все равно не работает. Думаю, что дело в данных, которые берутся из таблицы, но в чем именно, не догоняю.

Лучше перестрахуйся и проверь, как mysql воспринимает строку: SELECT COLLATION('%$search%')

mysql> SELECT COLLATION('%Тест%');
+-------------------------+
| COLLATION('%Тест%') |
+-------------------------+
| utf8_general_ci         |
+-------------------------+
1 row in set (0.00 sec)
metrokto ★★ ()
Ответ на: комментарий от Anoxemian

Если там не utf8, то как регистрозависимый поиск срабатывает? Уже мозг вывихнул, размышляя об этом.

metrokto ★★ ()
Ответ на: комментарий от metrokto

Это ты в консоли проверял? Там же всё работает - проверь из скрипта.

ziemin ★★ ()
Ответ на: комментарий от metrokto

Ну сам представь. При прямой проверке из консоли, поиск соответствует спецификации. Как только контекст меняется - неправильная выборка. ИМХО проблема в данных, нет пути.

Anoxemian ★★★★★ ()
Ответ на: комментарий от Anoxemian

Если из скрипта - не забудь начать сессию set names......

у клиента абсолютно свои понимания :-)

Anoxemian ★★★★★ ()
Ответ на: комментарий от ziemin

Да. В скрипте проверю завтра на работе, но думаю дело не в подставляемых данных, а в тех, которые берутся [как-то криво] из таблицы.

metrokto ★★ ()
Ответ на: комментарий от metrokto

Тогда ещё проверь, что mysql думает о том, что ты пишешь в базу и кодировку таблиц (вроде она наследуется от базы, но мало ли).

ziemin ★★ ()

А чем не нравится заюзщать Sphinx или Solr для поиска?

pi11 ★★★★★ ()
Ответ на: комментарий от metrokto

SELECT LOWER('Текст') = LOWER('Текст'); -- работает

SELECT LOWER('Текст') = LOWER('текст'); -- не возвращает результат

mysql> SELECT LOWER('Текст') = LOWER('текст');
+-------------------------------------------+
| LOWER('Текст') = LOWER('текст') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET NAMES utf8; 
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LOWER('Текст') = LOWER('текст');
+-------------------------------------------+
| LOWER('Текст') = LOWER('текст') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)
sjinks ★★★ ()

CREATE DATABASE DbName CHARACTER SET utf8 COLLATE utf8_general_ci;

Это всё хорошо, а SHOW CREATE TABLE TableName какой чарсет показывает? Для самой таблицы и для title.

sjinks ★★★ ()
Ответ на: комментарий от sjinks

Это всё хорошо, а SHOW CREATE TABLE TableName какой чарсет показывает? Для самой таблицы и для title.

mysql> SHOW CREATE TABLE TableName;
... ENGINE=InnoDB DEFAULT CHARSET=utf8 ...

mysql> SHOW FULL COLUMNS FROM TableName;
... utf8_general_ci ...
metrokto ★★ ()
Ответ на: комментарий от sjinks

mysql> SELECT LOWER('Текст') = LOWER('текст');
1 row in set (0.00 sec)

Если вручную вводить текст, то все работает, если брать из таблицы, то нет.

metrokto ★★ ()
Ответ на: комментарий от pi11

А чем не нравится заюзщать Sphinx или Solr для поиска?

Поделие будет использоваться на дешевых хостингах, где кроме php и mysql ничего не доступно.

metrokto ★★ ()

LOWER и UPPER разве в mysql уже умеет выполнятся над НЕ ASCII символами??

Alve ★★★★★ ()
Ответ на: комментарий от ziemin

Это ты в консоли проверял? Там же всё работает - проверь из скрипта.

$sql = "SELECT LOWER(`ТЕКСТ`);";
// текст
$sql = "SELECT LOWER(`title`) FROM TableName WHERE `id` = 1648932;";
// ðºð¾ðºð¾ñ ð¿ñ€ðµð·ðµñ€ð²ð°ñ‚ð¸ð²ñ‹ 
metrokto ★★ ()
Ответ на: комментарий от Alve

LOWER и UPPER разве в mysql уже умеет выполнятся над НЕ ASCII символами??

Да. =)

metrokto ★★ ()

Если таблица будет больше 100к строк, то надо использовать sphinx или solr.

Иначе каждый такой запрос, полный скан таблицы => большая загрузка CPU => медленное приложение/сайт

anonymous ()
Ответ на: комментарий от anonymous

Если таблица будет больше 100к строк, то надо использовать sphinx или solr.

Нет возможности.

Иначе каждый такой запрос, полный скан таблицы => большая загрузка CPU => медленное приложение/сайт

Там есть другие ограничивающие условия, которые я не стал приводить, чтобы они не отвлекали внимания.

metrokto ★★ ()

Вот что нашел. Если не выполнять

$db -> set_charset ('utf8');
то данные из таблицы берутся в пригодном виде, но подставляемые скриптом данные выглядят так:
����

metrokto ★★ ()

Нашел источник проблемы. Данные были неизвестным образом изменены при экспорте в mysql дампа postgresql.

metrokto ★★ ()
Ответ на: комментарий от metrokto
SHOW variables like '%char%';

Скорее всего, при заливке дампа во все поля был latin1

Теперь, если не делать client character set utf8, не происходит конвертации latin1 -> utf8

Чтобы всё было правильно надо:

  • задампить базу mysqldump, как есть
  • Добиться чтобы при подключении mysql в character set во все поля было utf8
  • поменять в дампе latin1 на utf8 через sed или perl
  • залить дамп
anonymous ()
Ответ на: комментарий от anonymous

...либо _general_ci (регистронезависимый)

anonymous ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.