У меня есть столбец в одной из моих таблиц, где хранятся несколько идентификаторов, разделенных запятой. Есть ли способ, которым я могу использовать это значение столбца в предложении "IN" запроса.
Столбец (city) имеет такие значения, как 6,7,8,16,21,2
Мне нужно использовать
select * from table where e_ID in (Select city from locations where e_Id=?)
Я доволен ответом Крозина, но я открыт для предложений, взглядов и вариантов.
Не стесняйтесь делиться своими мнениями.
Основываясь на примере 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 вы не храните несколько идентификаторов, разделенных запятой - вы сохраняете значение text, которое имеет то же самое значение, что и "Hello World", или "Мне нравится торты"!" - т.е. у него нет никакого искажения.
Что вам нужно сделать, так это создать разделенную таблицу, которая свяжет два объекта из базы данных вместе. Подробнее о many-to-many или one-to-many (в зависимости от ваших требований) отношениях в базах данных на базе 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.., чтобы поставить это предложение "in", нам нужно сделать этот столбец в строке ниже кода, который делает эту работу.
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.
Вы можете создать подготовленный оператор динамически, как this
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;