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

Question

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

é, è, and ê

все рассматриваются как 'e', ​​в запросах, использующих '=' и 'like'. У меня есть строка с полем username, установленным на '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

Answer 1

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

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

Используя MySQL Client, измените его, используя 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)

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

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 привел меня сюда для связанного вопроса, я все же заслуживаю ответа:)

Answer 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."%'");

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

Answer 3

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

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

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

или раздеть некоторых персонажей

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

Answer 4

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

такое идеальное решение PHP, но в MySQL? ПЕРЕРАБАТЫВАТЬ?

в MySQL

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

Производить:

Álvaro José     ?lvaro Jos?

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

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

Не любое решение.

Answer 5

Возвращает ли поиск с использованием английских символов результаты с иностранными символами? Я написал следующий сценарий для сравнения сопоставлений в MySQL 5.7 (также должен работать для MariaDB 10.2+):

        $db->query('CREATE TABLE IF NOT EXISTS test (name varchar(20))
         Engine=InnoDB character set utf8mb4 collate utf8mb4_unicode_520_ci');

        $db->query('CREATE TABLE IF NOT EXISTS test2 (name varchar(20))
         Engine=InnoDB character set utf8mb4 collate utf8mb4_unicode_ci');

        $db->query("insert into test values('Łove 520')");
        $db->query("insert into test2 values('Łove 520')");

        $types = ['utf8mb4_unicode_520_ci', 'utf8mb4_unicode_ci'];
        $tables = ['test' => 'utf8mb4_unicode_520_ci', 'test2' => 'utf8mb4_unicode_ci'];
        foreach($types as $n)
        {
            foreach($tables as $ta => $tc)
            {
                $db->query("SET NAMES 'utf8mb4' COLLATE '$n'");
                $res = $db->query("Select * from $ta where name like 'Love%'"); // Ł equal
                echo "\ntable $ta($tc), names($n): ".$res->fetchColumn(0);
            }
        }

Вот результаты:

table test(utf8mb4_unicode_520_ci), names(utf8mb4_unicode_520_ci): Łove 520
table test2(utf8mb4_unicode_ci), names(utf8mb4_unicode_520_ci):
table test(utf8mb4_unicode_520_ci), names(utf8mb4_unicode_ci): Łove 520
table test2(utf8mb4_unicode_ci), names(utf8mb4_unicode_ci):

(Примечание: я запустил сценарий из командной строки, поэтому он отображается как ┼üove 520 вместо Łove 520)

Похоже, что L == Ł, когда сопоставление таблицы - utf8mb4_unicode_520_ci, независимо от сопоставления соединений. Однако это не эквивалентно, если вы используете только utf8mb4_unicode_ci.