Как использовать значения, возвращаемые из хранимой функции mysql, с функцией in()?

У меня есть поле с именем «dealBusinessLocations» (в таблице «dp_deals»), которое содержит некоторые идентификаторы другой таблицы (dp_business_locations) в формате, разделенном запятыми.

dealBusinessLocations
----------------------
0,20,21,22,23,24,25,26

Мне нужно использовать эти значения в функции in() запроса.

как

select * from dp_deals as d left join dp_business_locations as b on(b.businessLocID IN (d.dealBusinessLocations) ;

Sine mysql не поддерживает функцию взрыва строки, я создал хранимую функцию

delimiter //
DROP FUNCTION IF EXISTS BusinessList;
create function BusinessList(BusinessIds text) returns text deterministic
BEGIN
  declare i int default 0;
  declare TmpBid text;
  declare result text default '';
  set TmpBid = BusinessIds;
  WHILE LENGTH(TmpBid) > 0 DO
           SET i = LOCATE(',', TmpBid);
           IF (i = 0)
                   THEN SET i = LENGTH(TmpBid) + 1;
           END IF;
           set result =  CONCAT(result,CONCAT('\'',SUBSTRING(TmpBid, 1, i - 1),'\'\,'));
           SET TmpBid =  SUBSTRING(TmpBid, i + 1, LENGTH(TmpBid));
  END WHILE;
  IF(LENGTH(result) > 0)
      THEN SET result = SUBSTRING(result,1,LENGTH(result)-1);
  END IF;
  return result;
END// 
delimiter  ;

Функция работает идеально.

mysql> BusinessList( '21,22' )
BusinessList( '21,22' )
-----------------------
'21','22'

Но запрос с использованием функции тоже не сработал. вот запрос.

select * from dp_deals as d left join dp_business_locations as b on(b.businessLocID IN (BusinessList(d.dealBusinessLocations)));

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

select * from dp_deals as d left join dp_business_locations as b on(b.businessLocID IN (BusinessList('21,22')));

Кажется, есть некоторая проблема с использованием значения, возвращаемого функцией.


mysql sql stored-functions
person Sijo Kurian    schedule 01.12.2011    source источник
comment
Нормализуйте свою таблицу. До тех пор используйте функцию FIND_IN_SET().   -  person ypercubeᵀᴹ    schedule 01.12.2011

Ответы (3)


arrow_upward
3
arrow_downward

Во-первых, прочитайте это:

Is storing a comma separated list in a database column really that bad?
Yes, it is

Затем идите и нормализуйте свои таблицы.

Теперь, если вы действительно не можете поступить иначе или пока не нормализуетесь, используйте FIND_IN_SET():

select * 
from dp_deals as d 
  left join dp_business_locations as b 
    on FIND_IN_SET(b.businessLocID, d.dealBusinessLocations)

Затем прочитайте эту статью еще раз. Если запрос медленный или у вас есть другие проблемы с этой таблицей, то вы знаете, почему:

Is storing a comma separated list in a database column really that bad?
Yes, it is

person ypercubeᵀᴹ    schedule 01.12.2011
comment
+1 CSV в базе данных — чистое зло и отправит вас прямо в ад. - person Johan; 01.12.2011
comment
@Johan - Хуже того, это не будет прямо в ад, это заставит вас проделать долгий путь, чтобы добраться туда! - person MatBailie; 01.12.2011
comment
Я знаю, что поле csv - большая проблема, но я не могу изменить схему базы данных. спасибо за помощь - person Sijo Kurian; 01.12.2011

arrow_upward
2
arrow_downward

Просто используйте find_in_set() вместо этого.

SELECT * 
FROM dp_deals as d 
LEFT JOIN dp_business_locations as b 
       ON (FIND_IN_SET(b.businessLocID,d.dealBusinessLocations) > 0);

См.: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Обратите внимание, что если вы отбросите CSV и выберетесь из ада, вы можете использовать простое соединение, например:

SELECT d.*, GROUP_CONCAT(b.dealBusinessLocation) as locations
FROM dp_deals as d 
LEFT JOIN dp_business_location as b 
       ON (d.dealBusinessLocation = b.businessLocID);

Что будет намного быстрее и нормализовано в качестве бонуса.

person Johan    schedule 01.12.2011
comment
Я думаю, что у вас есть 2 параметра в обратном порядке. - person ypercubeᵀᴹ; 01.12.2011
comment
Меня беспокоит проблема csv, но я не могу изменить дизайн базы данных. find_in_set() работает нормально. спасибо за помощь - person Sijo Kurian; 01.12.2011

arrow_upward
0
arrow_downward

Я думаю, ваша проблема в том, что IN() ожидает получить не одну строку с большим количеством полей, а множество полей.

С помощью вашей функции вы отправляете это:

WHERE something IN ('\'21\',\'22\''); /* i.e. a single text containing "'21','22'" */

И не ожидаемый

WHERE something IN ('21','22');
person Cylindric    schedule 01.12.2011
comment
Пожалуйста, проверьте вывод функции, опубликованной выше. т.е. mysql› BusinessList('21,22') BusinessList('21,22') ----------------------- '21','22' . Он возвращает правильный формат. - person Sijo Kurian; 01.12.2011
comment
@SijoKurian - ваша строка действительно в том формате, который вы предполагали. Но смысл ответа @Cylindric заключается в том, что это всего лишь одна строка. Это не список чисел. Просто потому, что строка содержит символы, представляющие числа, запятые и т. д., она не становится списком из множества элементов, это просто одна строка со всеми этими символами в ней. Что вам нужно сделать, так это вернуть ТАБЛИЦУ многих строк вместо одной строки. Затем вы можете сделать WHERE x IN (SELECT * FROM function()) - person MatBailie; 01.12.2011
comment
Как говорит @Dems, это не так. Вам нужно вернуть несколько строк или чисел, а не одну строку, которая выглядит как несколько чисел с запятыми между ними. - person Cylindric; 01.12.2011