Как преобразовать столбец в ASCII на лету без сохранения, чтобы проверить совпадения с внешней строкой ASCII?

Question

У меня есть функция поиска членов, в которой вы можете указывать части имен, и в возвращении должны быть все участники, имеющие хотя бы одно из имени пользователя, имени или фамилии, совпадающих с этим входом. Проблема в том, что некоторые имена имеют странные символы, такие как é в Renée и пользователь не хочет вводить странный символ, но нормальную замену ASCII e,

В PHP я преобразую входную строку в ASCII, используя iconv (на тот случай, если кто-то вводит странные символы). Однако в базе данных я также должен преобразовать странные символы в ASCII (очевидно) для соответствия строк.

Я попробовал следующее:

SELECT
  CONVERT(_latin1'Renée' USING ascii) t1, 
  CAST(_latin1'Renée' AS CHAR CHARACTER SET ASCII) t2;

(Это две попытки.) Оба не работают. Как есть Ren?e в качестве вывода. Вопросительный знак должен быть e, Все в порядке, если он выводит Ren?ee так как я могу просто удалить все знаки вопроса после конвертации.

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

Благодарю.

Answer 1

Вам не нужно ничего конвертировать. Ваше требование - сравнить две строки и спросить, равны ли они, игнорируя акценты; сервер базы данных может использовать сопоставление, чтобы сделать это для вас:

Не сопоставления UCA имеют однозначное сопоставление кода символа с весом. В MySQL такие параметры сортировки нечувствительны к регистру и акценту. Пример utf8_general_ci: "a", "A", "À" и "á" имеют разные коды символов, но все имеют вес 0x0041 и сравниваются как равные.

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

mysql> SELECT 'a' = 'A', 'a' = 'À', 'a' = 'á';
+-----------+-----------+-----------+
| 'a' = 'A' | 'a' = 'À' | 'a' = 'á' |
+-----------+-----------+-----------+
|         1 |         1 |         1 |
+-----------+-----------+-----------+
1 row in set (0.06 sec)

Answer 2

Во-первых, это должно работать так:

SELECT * FROM `test` WHERE `name` COLLATE utf8_general_ci LIKE '%renee%';

Где test таблица есть:

+-----+--------+
| id  | name   |
+-----+--------+
|  1  | Renée  |
|  2  | Renêe  |
|  3  | Renee  |
+-----+--------+

Какая у вас версия MySQL, и как вы пытаетесь соответствовать?

---

Одним из других возможных решений является транслитерация.

Связанный: Транслитерация PHP

Транслитерация входных данных не должна быть проблемой, но транслитерация значений из постоянного хранилища (например, дБ) в реальном времени во время поиска может оказаться невозможной. Таким образом, вы можете добавить еще три поля, такие как: username_slug, firstname_slug а также lastname_slug, При вставке / изменении записи, установите значения слагов соответственно. И при поиске ищите транслитерированный ввод по этим полям слагов.

+------+----------+---------------+----------+---------------+ ...
| id   | username | username_slug | lastname | lastname_slug | ...
+------+----------+---------------+----------+---------------+ ...
|    1 | Renée    |    renee      | La Niña  | la-nina       | ...
|    2 | Renêe    |    renee      | ...      | ...           | ...
|    3 | Renee    |    renee      | ...      | ...           | ...
+------+----------+---------------+----------+---------------+ ...

Поиск по "Рене" или "Рене" будет соответствовать всем записям.

Как побочный эффект, вы можете использовать эти поля для создания ссылок SEF (дружественных для поисковых систем), поэтому они названы,..._slugНапример, example.com/users/renee. Конечно, в этом случае вы должны проверить уникальность поля слаг.

Answer 3

Ответ @vincebowdren выше работает, я просто добавляю это как ответ для форматирования:

CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `lastname` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
);
insert into members values (1, 'test6ë');
select id from members where lastname like 'test6e%';

Урожайность

+------+
| id   |
+------+
|    1 |
+------+

И используя Latin1,

set names latin1;
CREATE TABLE `members2` (
  `id` int(11) DEFAULT NULL,
  `lastname` varchar(20) CHARACTER SET latin1 DEFAULT NULL
);
insert into members2 values (1, 'Renée');
select id from members2 where lastname like '%Renee%';

даст:

+------+
| id   |
+------+
|    1 |
+------+

Конечно, OP должен иметь ту же кодировку в приложении (PHP), соединение (MySQL в Linux используется по умолчанию для latin1 в 5.0, но по умолчанию UTF8 в 5.1), а в типе данных поля должно быть меньше неизвестных. Collation позаботится обо всем остальном.

РЕДАКТИРОВАТЬ: я написал должен иметь лучший контроль над всем, но следующее также работает:

set names latin1;
select id from members where lastname like 'test6ë%';

Потому что, как только кодировка соединения установлена, MySQL выполняет преобразование внутри. В этом случае он будет конвертировать каким-то образом, конвертировать и сравнивать строку UTF8 (из БД) с латиницей 1 (из запроса).

РЕДАКТИРОВАТЬ 2: Некоторый скептицизм требует, чтобы я предоставил еще более убедительный пример:

Учитывая заявления выше, вот что я сделал больше. Убедитесь, что терминал находится в UTF8.

set names utf8;
insert into members values (5, 'Renée'), (6, 'Renêe'), (7, 'Renèe');
select members.id, members.lastname, members2.id, members2.lastname
from members inner join members2 using (lastname);

Помни что members находится в utf8 и members2 находится на латыни1.

+ ------ + ---------- + ------ + ---------- +
| id | фамилия | id | фамилия |
+------+----------+------+----------+
|    5 | Рене | 1 | Рене |
|    6 | Рене | 1 | Рене |
|    7 | Рене | 1 | Рене |
+ ------ + ---------- + ------ + ---------- +

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

Answer 4

CAST() Оператор в контексте кодировки символов переводит из одного метода хранения символов в другой - он не меняет фактические символы, что вам и нужно. Символ é - это то, чем он является в любом наборе символов, это не e. Вам необходимо преобразовать символы с акцентом в символы без акцента, что является другой проблемой и задавалось несколько раз ранее ( нормализация символов с акцентом в запросах MySQL).

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