Нормализация акцентированных символов в MySQL-запросах

Я хотел бы иметь возможность делать запросы, которые нормализуют символы с акцентом, так что, например:

é, è, and ê

все рассматриваются как "e", в запросах с использованием "=" и "like". У меня есть строка с полем "Имя пользователя", установленным в " rené", и я хотел бы иметь возможность сопоставлять ее с " Rene" и " rené.

Я пытаюсь сделать это с помощью предложения 'collate' в MySQL 5.0.8. Я получаю следующую ошибку:

mysql> select * from User where username = 'rené' collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

FWIW, моя таблица была создана с помощью

CREATE TABLE `User` (
  `id` bigint(19) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uniqueUsername` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=56790 DEFAULT CHARSET=utf8

Ответ 1

Причиной ошибки является не таблица, а набор символов вашего ввода, т.е. "rené" в вашем запросе. Поведение зависит от переменной character_set_connection:

Набор символов, используемый для литералов, у которых нет набора символов и для преобразования числа в строку.

Используя MySQL-клиент, измените его с помощью SET NAMES:

Инструкция SET NAMES 'charset_name' эквивалентна этим трем операторам:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

(из http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html)

Пример вывода:

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User where username = 'rené' collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User where username = 'rené' collate utf8_general_ci;
Empty set (0.00 sec)

Напоследок, использование может явно установить набор символов с помощью "набора символов":

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User where username = _utf8'rené' collate utf8_general_ci;
Empty set (0.00 sec)

Я знаю, что этот вопрос довольно старый, но поскольку Google привел меня сюда по соответствующему вопросу, я все же заслуживаю ответа:)

Ответ 2

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

Если вы используете PHP, вы можете использовать iconv() для обработки преобразования:

$username = 'rené';
$normalized = iconv('UTF-8', 'ASCII//TRANSLIT', $string);

Затем вы просто сохраните обе версии и используйте нормализованную версию для поиска и обычного имени пользователя для отображения. Сравнение и выбор будет намного быстрее из нормализованного столбца при условии, что вы также нормализуете строку поиска:

$search = mysql_real_escape_string(iconv('UTF-8', 'ASCII//TRANSLIT', $_GET['search']));
mysql_query("SELECT * FROM User WHERE normalized LIKE '%".$search."%'");

Конечно, этот метод может оказаться нецелесообразным, если у вас есть несколько столбцов, которые нуждаются в нормализации, но в вашем конкретном случае это может работать нормально.

Ответ 3

Я реализовал команду strtr php function/tr unix в MySQL, вы можете получить источник здесь

Вы можете использовать как:

SELECT tr(name, 'áäèëî', 'aaeei') FROM persons

или разбить некоторые символы

SELECT tr(name, 'áäèëî', null) FROM persons

Ответ 4

$normalized = iconv('UTF-8', 'ASCII//TRANSLIT', $string);

- идеальное решение php, но в mysql? CONVERT?

в mysql

SELECT 'Álvaro José' as accented, (CONVERT ('Álvaro José' USING ascii)) as notaccented

Produce:

Álvaro José     ?lvaro Jos?

Акцентированные слова не преобразуются без акцентированных слов, это не эквивалентно транслиту iconv.

RegExp не работает с UTF-8.

Не какое-либо решение.