Как проверить, существует ли значение из строки, разделенной запятыми, в строке MySQL
У меня есть таблица со столбцом с именем tags. В этом столбце у меня есть список, разделенный запятыми, например air conditioner, tv, fridge.
Я хочу проверить, существует ли какое-либо слово в столбце, разделенном запятыми, в строке типа
Is there an air conditioner in the room?
Итак, запрос должен вернуть совпадение, как в столбце tags, значение air conditioner существует.
Я думал использовать find_in_set, но в данном случае это не сработает, поскольку логика должна быть обратной.
select * from products where find_in_set('Is there an air conditioner in the room?',tags) <> 0
ОБНОВЛЯТЬ
Я мог бы использовать find_in_set, разделив строку на слова и затем проверив столбец тегов, но это не сработает для такой комбинации, как «кондиционер», поскольку она будет выглядеть как air, conditioner.
MySQL версия 5.7.44
Рассматривали ли вы возможность использования полнотекстового поиска по тексту?
@SalmanA Полный текст MySQL не может использовать ссылку на столбец в качестве шаблона поиска.
Я уже пробовал полнотекстовый режим, но это не сработало
Ответы 3
Вы можете проверить это следующим образом:
```
create table mytags (
id int(11),
tags varchar(64)
);
insert into mytags values(1,"air conditioner, tv, fridge");
SELECT * from mytags;
```
| id | tags |
|---:|:-----|
| 1 | air conditioner, tv, fridge |
```
SELECT * FROM mytags WHERE FIND_IN_SET("air conditioner",tags);
```
| id | tags |
|---:|:-----|
| 1 | air conditioner, tv, fridge |
Или всегда удаляйте все пробелы. Затем вы можете найти его с пробелами или без них. Например:
```
SELECT * FROM mytags WHERE
FIND_IN_SET(REPLACE("airconditioner"," ",""),REPLACE(tags," ",""));
| id | tags |
|---:|:-----|
| 1 | air conditioner, tv, fridge |
Это нормально, но невозможно научиться разделять слова в предложении. Если мы используем пробел в качестве разделителя, он разделит air conditioner на два слова, например air и conditioner. Так что это не сработает, пока я не найду какую-то логику (разделение строки на значимые части), которая будет работать во всех случаях.
@lStoilov - у меня в ОТВЕТЕ
Как это поможет, если строка поиска будет is there an air conditioner in the room?, а не просто air conditioner?
Кстати, после запятых не следует ставить пробел. FIND_IN_SET() относится к ним буквально.
@Бармер - спасибо за совет. Это моя ошибка
SELECT * FROM products
CROSS JOIN JSON_TABLE(CONCAT('["', REGEXP_REPLACE(tags, ', *', '","'), '"]'),
'$[*]' COLUMNS(tag VARCHAR(20) PATH '$')
) AS j
WHERE LOCATE(j.tag, 'Is there an air conditioner in the room?');
Демо: https://dbfiddle.uk/ayNuWEqQ
Эту задачу невозможно оптимизировать с помощью индекса, как и любую другую задачу сопоставления подстрок.
Для приведенного выше решения с JSON_TABLE() требуется MySQL 8.0 или более поздняя версия. Срок службы MySQL 5.x уже истек, но я понимаю, что некоторые люди еще не обновились. Я оставлю этот ответ здесь для будущих людей, которые прочитают эту ветку.
Когда я пытаюсь выполнить ваш запрос, я получаю эту ошибку № 1064. У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, чтобы узнать правильный синтаксис для использования рядом с '(CONCAT('["', REGEXP_REPLACE(tags, ', ', '","'), '"]'), '$[ ]' COLUMNS(тег ' в строке 2
Возможно, потому, что я использую MySQL 5.7.44.
Попробовал изменить версию в вашем dbfiddle, и я получил ту же ошибку.
Что ж, вам придется либо перейти на MySQL 8.0, либо изменить структуру таблицы, чтобы избежать списков, разделенных запятыми. В этой теме вы получили два хороших ответа. Удачи.
Согласен с вами... Пойду со сменой версии, так как это проще, чем структура базы данных. Спасибо.
У меня есть еще один вопрос. Когда я пытаюсь выполнить запрос, я получаю эту ошибку #3141 - Invalid JSON text in argument 1 to function json_table: "Missing a comma or ']' after an array element." at position 271.
Что ж, моя демо-версия dbfiddle работает (см. ссылку, которую я привел выше). Я не знаю, как выглядят ваши данные и какой запрос вы пытаетесь выполнить.
Спасибо, Билл, только что понял, что проблема была в опечатке с моей стороны.
Я обнаружил странную проблему... иногда в некоторых запросах выскакивает эта ошибка #3141 - Invalid JSON text in argument 1 to function json_table: "Invalid encoding in string." at position 429. Есть ли у вас какие-либо идеи, в чем может быть проблема? Я вижу, что проблема связана с кодировкой в строке, но означает ли это кодировку ключевых слов в тегах или кодировку самого поля? Текущая кодировка поля — это utf8mb4_general_ci сопоставление таблицы после обновления до MySQL 8, которое стало utf8mb3_general_ci
Опять же, я понятия не имею, какие значения данных вы используете или какой код пытаетесь использовать.
Вот демонстрационные данные dbfiddle.uk/85sJIh77 вместе с моим запросом. Я не могу воспроизвести полученную ошибку, но запрос в образце ничего не улавливает. Данные не на английском языке, но это реальные данные.
Я протестировал и сузил его до символа табуляции (^I), следующего за текстом JSON. Удалите символ табуляции, и все будет работать нормально.
Это сделало это. Удаление всех вкладок решило проблему. Еще раз спасибо, Билл!
Не используйте поля, разделенные запятыми. Нормализуйте свою схему, поместив теги в отдельную таблицу с внешним ключом, указывающим на таблицу products.
CREATE TABLE product_tags (
tag VARCHAR(100),
product_id INT,
PRIMARY KEY (tag, product_id),
FOREIGN KEY (product_id) REFERENCES products (id)
);
Тогда вы можете использовать
SELECT p.*
FROM products AS p
JOIN product_tags AS pt ON p.id = pt.product_id
WHERE LOCATE(pt.tag, 'Is there an air conditioner in the room?')
Это подошло бы, но я не могу изменить структуру таблицы.
Не используйте список, разделенный запятыми: stackoverflow.com/questions/3653462/…