Разделите строку MYSQL из GROUP_CONCAT на (массив, например, выражение, список), который IN () может понять

Этот вопрос следует из набора результатов соединения MYSQL, стертых результаты во время IN() в предложении where?

Итак, короткая версия вопроса. Как превратить строку, возвращаемую GROUP_CONCAT, в список выражений, разделенных запятыми, который IN() будет рассматривать как список из нескольких элементов для циклического просмотра?

Н.Б. Документы MySQL ссылаются на «(запятая, разделенные, списки)», используемые IN() как на «списки выражений», и, что интересно, страницы в IN() кажутся более или менее единственными страницами в документах MySQL для когда-либо обращаться к спискам выражений. Поэтому я не уверен, что функции, предназначенные для создания массивов или временных таблиц, будут здесь полезны.

Длинная версия вопроса, основанная на примере: из базы данных с двумя таблицами, например:

SELECT id, name, GROUP_CONCAT(tag_id) FROM person INNER JOIN tag ON person.id = tag.person_id GROUP BY person.id;
+----+------+----------------------+
| id | name | GROUP_CONCAT(tag_id) |
+----+------+----------------------+
|  1 | Bob  | 1,2                  |
|  2 | Jill | 2,3                  |
+----+------+----------------------+

Как я могу повернуть это, которое, поскольку оно использует строку, рассматривается как логический эквивалент ( 1 = X ) AND ( 2 = X )...

SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id 
GROUP BY person.id HAVING ( ( 1 IN (GROUP_CONCAT(tag.tag_id) ) ) AND ( 2 IN (GROUP_CONCAT(tag.tag_id) ) ) );
Empty set (0.01 sec)

... во что-то, где результат GROUP_CONCAT обрабатывается как список, так что для Боба это будет эквивалентно:

SELECT name, GROUP_CONCAT(tag.tag_id) FROM person INNER JOIN tag ON person.id = tag.person_id AND person.id = 1 
GROUP BY person.id HAVING ( ( 1 IN (1,2) ) AND ( 2 IN (1,2) ) );
+------+--------------------------+
| name | GROUP_CONCAT(tag.tag_id) |
+------+--------------------------+
| Bob  | 1,2                      |
+------+--------------------------+
1 row in set (0.00 sec)

...и для Джилл это было бы эквивалентно:

SELECT name, GROUP_CONCAT(tag.tag_id) FROM person INNER JOIN tag ON person.id = tag.person_id AND person.id = 2 
GROUP BY person.id HAVING ( ( 1 IN (2,3) ) AND ( 2 IN (2,3) ) );
Empty set (0.00 sec)

... таким образом, общим результатом будет исключительное предложение поиска, требующее всех перечисленных тегов, которые не используют HAVING COUNT(DISTINCT...)?

(примечание: эта логика работает без И, применяя к первому символу строки. например.

SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id 
  GROUP BY person.id HAVING ( ( 2 IN (GROUP_CONCAT(tag.tag_id) ) ) );
+------+--------------------------+
| name | GROUP_CONCAT(tag.tag_id) |
+------+--------------------------+
| Jill | 2,3                      |
+------+--------------------------+
1 row in set (0.00 sec)

mysql list expression explode
person user56reinstatemonica8    schedule 10.01.2011    source источник
comment
Это хорошо, но стоит отметить, что вам не нужно делать GROUP_CONCAT() во всех FIND_IN_SET(). Вы можете просто SELECT GROUP_CONCAT(tag.tag_id) AS tags_list, а затем HAVING FIND_IN_SET(20, tags_list)   -  person Treffynnon    schedule 03.03.2011

Ответы (2)


arrow_upward
43
arrow_downward

Вместо использования IN() можно ли использовать FIND_IN_SET()?

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

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

Вот полный пример, основанный на примере проблемы в вопросе, проверенном автором вопроса в более раннем редактировании вопроса:

SELECT name FROM person LEFT JOIN tag ON person.id = tag.person_id GROUP BY person.id 
  HAVING ( FIND_IN_SET(1, GROUP_CONCAT(tag.tag_id)) ) AND ( FIND_IN_SET(2, GROUP_CONCAT(tag.tag_id)) );
+------+
| name |
+------+
| Bob  |
+------+
person Wolph    schedule 10.01.2011
comment
Благодарю вас! Мне это тоже нужно. - person Gia Duong Duc Minh; 06.12.2012
comment
Идеальный! Один совет: не забывайте, что, поскольку это похоже на вызов функции, не должно быть отсутствия пробела перед открывающей скобкой/круглой скобкой, иначе вы получите ошибку Function dbname.FIND_IN_SET does not exist. - person user56reinstatemonica8; 09.09.2016
comment
Что ж, это решение, но я обнаружил, что FIND_IN_SET чрезвычайно менее эффективен, чем IN ... кто-нибудь знает, есть ли способ действительно достичь того, о чем говорится в вопросе, способ разделения строки на массивоподобную переменную ? - person Andrés Monge Moreno; 17.01.2017
comment
@AndrésMongeMoreno: с Mysql 5.0 (которая была последней версией на момент ответа на этот вопрос) вариантов не так много. В текущей версии я, вероятно, предложил бы вместо этого использовать функции JSON: dev.mysql.com/doc/refman/5.7/en/json-functions.html - person Wolph; 18.01.2017
comment
@Wolph Я понимаю, это не проблема = D. Просто хотел оставить кое-что для будущих читателей. По моему опыту, FIND_IN_SET ЧРЕЗВЫЧАЙНО менее эффективен, чем любой другой выбор (даже зацикливание с курсором на результатах до группировки). - person Andrés Monge Moreno; 18.01.2017
comment
@AndrésMongeMoreno, если это даже удаленный вариант ... Я бы рекомендовал заменить mysql/mariadb на postgresql. Это определенно даст вам гораздо лучшую производительность - person Wolph; 18.01.2017

arrow_upward
5
arrow_downward

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

Для тривиального примера, если у вас есть массив строк, подобный этому: «один|два|дерево|четыре|пять» и вы хотите знать, есть ли два в массиве, вы можете сделать это следующим образом:

create function str_in_array( split_index varchar(10), arr_str varchar(200), compares varchar(20) )
  returns boolean
  begin
  declare resp boolean default 0;
  declare arr_data varchar(20);

  -- While the string is not empty
  while( length( arr_str ) > 0  ) do

  -- if the split index is in the string
  if( locate( split_index, arr_str ) ) then

      -- get the last data in the string
    set arr_data = ( select substring_index(arr_str, split_index, -1) );

    -- remove the last data in the string
    set arr_str = ( select
      replace(arr_str,
        concat(split_index,
          substring_index(arr_str, split_index, -1)
        )
      ,'')
    );
  --  if the split index is not in the string
  else
    -- get the unique data in the string
    set arr_data = arr_str;
    -- empties the string
    set arr_str = '';
  end if;

  -- in this trivial example, it returns if a string is in the array
  if arr_data = compares then
    set resp = 1;
  end if;

 end while;

return resp;
end
|

delimiter ;

Я хочу создать набор полезных функций mysql для работы с этим методом. Кто заинтересован, пожалуйста, свяжитесь со мной.

Дополнительные примеры см. на странице http://blog.idealmind.com.br/mysql/how-to-use-string-as-array-in-mysql-and-work-with/

person Idealmind    schedule 22.07.2012