Как работать с MySQL "без учета регистра" и "без акцента" в UTF-8

У меня есть схема в "utf8 - UTF-8 Unicode" как кодировка и сортировка "utf8_spanish_ci".

Все внутренние таблицы - это InnoDB с одинаковой кодировкой и сортировкой, как указано.

Здесь возникает проблема:

с запросом типа

SELECT *
FROM people p
WHERE p.NAME LIKE '%jose%';

Я получаю 83 строки результатов. У меня должно быть 84 результата, потому что я это знаю.

Изменение где для:

WHERE p.NAME LIKE '%JOSE%';

Я получаю точные 83 строки. С такими комбинациями, как JoSe, Jose, JOSe и т.д. Все те же 83 строки сообщаются.

Проблема возникает, когда акценты играют в игру. Если:

WHERE p.NAME LIKE '%josé%';

Я не получаю результатов. 0 строк.

Но если я это сделаю:

WHERE p.NAME LIKE '%JOSÉ%';

Я получаю только одну результирующую строку, поэтому 1 строка. Это единственная строка, в которой акцентируется "jose" и капитализируется.

Я пробовал с josÉ, или JoSÉ, или любую другую комбинацию, которую я делаю, если акцентированное письмо остается заглавным или нет, поскольку оно действительно хранится в базе данных, и оно все еще возвращает единственную строку. Если я вдруг изменил "É" для "é" в любой комбинации, которую я делаю с заглавной буквой в JOSE, он не возвращает строк.

Итак, выводы:

Нечувствительность к регистру, если в игре не играют латинские символы.
С учетом регистра, если появляются латинские символы.
Акцент чувствителен, как будто я ищу JOSE или jose, я получаю только 83 строки вместо 84 строк, которые мне нужны.

Что я хочу?

Для поиска "jose", "JOSE", "José", "JOSÉ", "JSE", "jöse", "JoSÈ",... должны вернуть 84 строки, которые я знаю, которые существуют. Я, что бы превратить мои поиски в регистр без учета регистра и "латинский" нечувствительный.

Решения, подобные COLLATION на LIKE, не работают для меня, не знаю, почему...

Что я могу сделать?

Спасибо заранее!

EDIT:

Если я делаю sometingh как:

WHERE p.NAME LIKE '%jose%' COLLATE utf8_general_ci;

Я получаю сообщение об ошибке:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

И я также изменил все возможные сопоставления на столбцах!

И если я сделаю что-то вроде:

WHERE p.NAME LIKE _utf8 '%jose%' COLLATE utf8_general_ci;

Те же 83 строки сообщаются, как будто я ничего не сделал...

Ответ 1

Вы уже пытались использовать сортировку без учета акцентов для поиска и заказа.

http://dev.mysql.com/doc/refman/5.0/en/charset-collation-implementations.html

Дело в том, что ваш столбец NAME, похоже, хранится в наборе символов latin1 (8 бит). Вот почему mySQL ворчит на вас вот так:

  COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Вы можете получить желаемые результаты, если вы попробуете

 WHERE CONVERT(p.NAME USING utf8) LIKE _utf8 '%jose%' COLLATE utf8_general_ci;

Но будьте осторожны!

Когда вы используете какую-либо функцию (в этом примере, CONVERT) в столбце в инструкции WHERE, вы можете победить MySQL, чтобы оптимизировать ваш поиск с помощью индексов. Если этот проект станет большим (то есть, если у вас будет много строк в ваших таблицах), вам необходимо сохранить ваши данные в формате utf8, а не в latin1. (Вероятно, вы уже знаете, что ваш поисковый запрос LIKE '%whatever%' также поражает индексирование MySQL.)

Ответ 2

На всякий случай кто-то еще сталкивается с этой проблемой, я нашел способ решить эту проблему, по крайней мере для меня.

Я использую PHP для вставки и извлечения записей из базы данных. Хотя моя база данных, таблицы и столбцы - utf8, а также кодировка файлов PHP, правда состоит в том, что кодирование, используемое в соединении между PHP и MySQL, выполняется с использованием latin1. Мне удалось найти это, используя

$mysqli->character_set_name();

где $mysqli - ваш объект.

Для того чтобы поисковые запросы начали работать как ожидалось, возвращая нечувствительные к акценту и случайные записи для символов с акцентами или нет, я должен явно установить набор символов соединения.

Для этого вам просто нужно сделать следующее:

$mysqli->set_charset('utf8');

где $mysqli - ваш объект mysqli. Если у вас есть класс управления базой данных, который обертывает ваши базы данных, это легко применить к полному приложению. Если нет, вы должны установить это явно везде, где вы открываете соединение.

Я надеюсь, что это поможет кому-то, поскольку я уже волновался об этом!