Значения, разделенные запятыми, в предложении MySQL «IN»

Основываясь на примере FIND_IN_SET () от @Jeremy Smith, вы можете сделать это с помощью соединения, поэтому вам не нужно запускать подзапрос.

 SELECT * FROM table t JOIN locations l ON FIND_IN_SET(t.e_ID, l.city) > 0 WHERE l.e_ID = ? 

Это, как известно, выполняется очень плохо, поскольку оно должно выполнять сканирование таблицы, оценивая функцию FIND_IN_SET () для каждой комбинации строк в table и locations . Он не может использовать индекс, и нет возможности его улучшить.

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

Объяснение: Предположим, я попрошу вас найти всех в телефонной книге, чей первый, средний или последний начальный «Я» В этом случае порядок сортировки книги невозможен, так как в любом случае вам нужно сканировать каждую страницу.

Решение LIKE данное @fthiella, имеет аналогичную проблему в отношении производительности. Он не может быть проиндексирован.

Также см. Мой ответ « Сохраняет ли список с разделителями в столбце базы данных действительно так плохо? для других ловушек этого способа хранения денормализованных данных.

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

 CREATE TABLE location2city ( location INT, city INT, PRIMARY KEY (location, city) ); 

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

 INSERT INTO location2city (location, city) SELECT l.e_ID, c.e_ID FROM cities c JOIN locations l ON FIND_IN_SET(c.e_ID, l.city) > 0; 

Теперь вы можете запустить гораздо более эффективный запрос для поиска записей в table :

 SELECT * FROM location2city l JOIN table t ON t.e_ID = l.city WHERE l.e_ID = ?; 

Это может использовать индекс. Теперь вам просто нужно позаботиться о том, чтобы любые INSERT / UPDATE / DELETE строк в locations также вставляли соответствующие строки сопоставления в location2city .

С точки зрения MySQL вы не храните несколько идентификаторов, разделенных запятой – вы сохраняете текстовое значение, которое имеет то же самое, что и «Hello World», или «Мне нравятся торты!». – т. е. он не имеет никакого зазора.

Вам нужно создать отдельную таблицу, которая свяжет два объекта из базы данных вместе. Подробнее о взаимоотношениях « многие-ко-многим» или « один ко многим» (в зависимости от ваших требований) в SQL-базах данных.

Вместо использования IN по вашему запросу используйте FIND_IN_SET ( docs ):

 SELECT * FROM table WHERE 0 < FIND_IN_SET(e_ID, ( SELECT city FROM locations WHERE e_ID=?)) 

Применяются обычные оговорки о нормализации первой формы (база данных не должна хранить несколько значений в одном столбце), но если вы застряли с ней, то вышеприведенное утверждение должно помочь.

Это не использует предложение IN, но оно должно делать то, что вам нужно:

 Select * from table where CONCAT(',', (Select city from locations where e_Id=?), ',') LIKE CONCAT('%,', e_ID, ',%') 

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

например

 CONCAT(',', '6,7,8,16,21,2', ',') returns ',6,7,8,16,21,2,' e_ID=1 --> ',6,7,8,16,21,2,' LIKE '%,1,%' ? FALSE e_ID=6 --> ',6,7,8,16,21,2,' LIKE '%,6,%' ? TRUE e_ID=21 --> ',6,7,8,16,21,2,' LIKE '%,21,%' ? TRUE e_ID=2 --> ',6,7,8,16,21,2,' LIKE '%,2,%' ? TRUE e_ID=3 --> ',6,7,8,16,21,2,' LIKE '%,3,%' ? FALSE etc. 

это один для оракула .. здесь конкатенация строк выполняется wm_concat

 select * from table where e_ID in (Select wm_concat(city) from locations where e_Id=?) 

да, я согласен с raheel shan .., чтобы поставить это предложение «в», нам нужно сделать этот столбец в строку ниже кода, который делает эту работу.

 select * from table where to_char(e_ID) in ( select substr(city,instr(city,',',1,rownum)+1,instr(city,',',1,rownum+1)-instr(city,',',1,rownum)-1) from ( select ','||WM_CONCAT(city)||',' city,length(WM_CONCAT(city))-length(replace(WM_CONCAT(city),','))+1 CNT from locations where e_Id=? ) TST ,ALL_OBJECTS OBJ where TST.CNT>=rownum ) ; 

вы должны использовать

FIND_IN_SET Возвращает позицию значения в строке значений, разделенных запятыми

 mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 

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

Вы можете попробовать что-то вроде этого:

 select * from table where e_ID in (Select GROUP_CONCAT(city SEPARATOR ',') from locations where e_Id=?) 

IN принимает строки, поэтому разделение разделенных запятыми столбцов для поиска не будет делать то, что вы хотите, но если вы предоставите такие данные («1», «2», «3»), это будет работать, но вы не можете сохранять данные, подобные этому в своей области независимо от того, что вы вставляете в столбец, все это будет восприниматься как строка.

Вам нужно «РАЗДЕЛИТЬ» значения столбца города. Это будет выглядеть так:

 SELECT * FROM table WHERE e_ID IN (SELECT TO_NUMBER( SPLIT_STR(city /*string*/ , ',' /*delimiter*/ , 1 /*start_position*/ ) ) FROM locations); 

Подробнее о функции split_str для MySQL вы можете прочитать здесь: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Кроме того, я использовал функцию TO_NUMBER для Oracle здесь. Пожалуйста, замените его на правильную функцию MySQL.

Динамически можно создать подготовленный оператор

 set @sql = concat('select * from city where city_id in (', (select cities from location where location_id = 3), ')'); prepare in_stmt from @sql; execute in_stmt; deallocate prepare in_stmt;