Поиск столбца, содержащего данные CSV в таблице MySQL, на наличие входных значений

У меня есть таблица ITEM в MySQL, в которой хранятся данные следующим образом:

ID    FEATURES
--------------------
1     AB,CD,EF,XY
2     PQ,AC,A3,B3
3     AB,CDE
4     AB1,BC3
--------------------

В качестве входных данных я получу строку CSV, что-то вроде "AB,PQ". Я хочу получить записи, содержащие AB или PQ. Я понял, что для этого нужно написать функцию MySQL. Итак, если у нас есть эта волшебная функция MATCH_ANY, определенная в MySQL, которая делает это, я бы просто выполнил SQL следующим образом:

select * from ITEM where MATCH_ANY(FEAURES, "AB,PQ") = 0

Приведенный выше запрос вернет записи 1, 2 и 3.

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

Модернизация стола — это последний вариант для меня, так как это связано с множеством проблем.

Я также мог бы выполнять запросы, содержащие несколько функций MATCH_ANY, например:

select * from ITEM where MATCH_ANY(FEATURES, "AB,PQ") = 0 and MATCH_ANY(FEATURES, "CDE")

В приведенном выше случае мы получили бы пересечение записей (1, 2, 3) и (3), что было бы всего 3.

Любая помощь приветствуется.

Спасибо


mysql csv sql search procedure
person adarshr    schedule 18.05.2010    source источник

Ответы (5)


arrow_upward
8
arrow_downward

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

select distinct i.Itemid
from Item i
inner join ItemFeature f on f.ItemId = i.ItemId
where f.Feature in ('AB', 'PQ')

Вы можете сопоставить строки в значениях, разделенных запятыми, но это не очень эффективно:

select Id
from Item
where
  instr(concat(',', Features, ','), ',AB,') <> 0 or
  instr(concat(',', Features, ','), ',PQ,') <> 0
person Guffa    schedule 18.05.2010
comment
Ты сделал это! Спасибо Гуфа! Я был обеспокоен использованием LIKE или RLIKE, поскольку оба они, как известно, по своей природе медленнее. По крайней мере медленнее по сравнению с простым INSTR. Я написал свой генератор динамических запросов на основе этой логики, и он прекрасно работает. Я понимаю, что такая табличная модель не очень подходит с точки зрения нормализации. Но это лучшее, что я могу предложить на данный момент. - person adarshr; 18.05.2010
comment
@Adarsh: Да, он немного быстрее, чем like, но он по-прежнему не может использовать какой-либо индекс в поле, поэтому он никогда не будет быстрым. - person Guffa; 18.05.2010

arrow_upward
4
arrow_downward 

select * 
  from ITEM where 
 where CONCAT(',',FEAURES,',') LIKE '%,AB,%'
    or CONCAT(',',FEAURES,',') LIKE '%,PQ,%'

или создайте пользовательскую функцию для выполнения MATCH_ANY

person Mark Baker    schedule 18.05.2010

arrow_upward
4
arrow_downward

Для всех вас, любителей REGEXP, я решил добавить это в качестве решения:

SELECT * FROM ITEM WHERE FEATURES REGEXP '[[:<:]]AB|PQ[[:>:]]';

и для чувствительности к регистру:

SELECT * FROM ITEM WHERE FEATURES REGEXP BINARY '[[:<:]]AB|PQ[[:>:]]';

Для второго запроса:

SELECT * FROM ITEM WHERE FEATURES REGEXP '[[:<:]]AB|PQ[[:>:]]' AND FEATURES REGEXP '[[:<:]]CDE[[:>:]];

Ваше здоровье!

person Borgboy    schedule 09.11.2015
comment
Работает как шарм и сэкономил мне кучу кода и час времени на разработку. Я также предлагаю другим использовать этот метод, поскольку он компактен и содержит всего одно условие в предложении «где». - person Sasi varna kumar; 11.12.2015

arrow_upward
1
arrow_downward

В качестве альтернативы рассмотрите возможность использования RLIKE()

    select * 
      from ITEM
     where ','+FEATURES+',' RLIKE ',AB,|,PQ,';
person Mark Baker    schedule 18.05.2010

arrow_upward
0
arrow_downward

Просто мысль:

Это должно быть сделано в SQL? Это то, что вы обычно ожидаете написать на PHP или Python или на любом другом языке, который вы используете для взаимодействия с базой данных.

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

Бен

person Ben    schedule 18.05.2010
comment
Ну, что я действительно пытаюсь сделать, так это получить прогноз количества записей, соответствующих заданным критериям. Этот запрос будет запущен с использованием AJAX через PHP. Так что, чем быстрее вернется результат, тем лучше для меня. Это оставляет мне только один выбор — сделать одно обращение к базе данных. Вот где я застрял, я чувствую! Да, я могу построить сложную строку запроса, используя PHP. Но я не могу понять, как тогда будет выглядеть эта строка запроса. - person adarshr; 18.05.2010