将 GROUP_CONCAT 中的 MYSQL 字符串拆分为 IN() 可以理解的 (array, like, expression, list)

Split a MYSQL string from GROUP_CONCAT into an ( array, like, expression, list) that IN () can understand

提问by user56reinstatemonica8

This question follows on from MYSQL join results set wiped results during IN () in where clause?

这个问题是从MYSQL join results setwiped results during IN() in where 子句继而来的？

So, short version of the question. How do you turn the string returned by GROUP_CONCAT into a comma-seperated expression list that IN() will treat as a list of multiple items to loop over?

所以，这个问题的简短版本。如何将 GROUP_CONCAT 返回的字符串转换为逗号分隔的表达式列表，IN() 会将其视为要循环的多个项目的列表？

N.B. The MySQL docs appear to refer to the "( comma, seperated, lists )" used by IN () as 'expression lists', and interestingly the pages on IN() seem to be more or less the only pages in the MySQL docs to ever refer to expression lists. So I'm not sure if functions intended for making arrays or temp tables would be any use here.

注意 MySQL 文档似乎将 IN () 使用的“（逗号，分隔，列表）”称为“表达式列表”，有趣的是 IN() 上的页面似乎或多或少是 MySQL 文档中的唯一页面永远引用表达式列表。所以我不确定用于创建数组或临时表的函数在这里是否有用。

---

---

Long example-based version of the question: From a 2-table DB like this:

问题的基于示例的长版本：来自这样的 2-table DB：

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                  |
+----+------+----------------------+

How can I turn this, which since it uses a string is treated as logical equivalent of ( 1 = X ) AND ( 2 = X )...

我该如何转动它，因为它使用字符串被视为 ( 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)

...into something where the GROUP_CONCAT result is treated as a list, so that for Bob, it would be equivalent to:

...进入将 GROUP_CONCAT 结果视为列表的内容，因此对于 Bob，它相当于：

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)

...and for Jill, it would be equivalent to:

......对于吉尔来说，它相当于：

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)

...so the overall result would be an exclusive search clause requiring all listed tags that doesn't use HAVING COUNT(DISTINCT ... ) ?

...所以总体结果将是一个排他搜索子句，要求所有列出的标签不使用 HAVING COUNT(DISTINCT ... ) ？

(note: This logic works without the AND, applying to the first character of the string. e.g.

（注意：此逻辑无需 AND，适用于字符串的第一个字符。例如

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)

回答by Wolph

Instead of using IN(), would using FIND_IN_SET()be an option too?

而不是使用IN()，使用FIND_IN_SET()也是一种选择吗？

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_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

---

---

Here's a full example based on the example problem in the question, confirmed as tested by the asker in an earlier edit to the question:

这是一个基于问题中示例问题的完整示例，经提问者在对该问题的较早编辑中进行了测试：

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  |
+------+

回答by Idealmind

You can pass a string as array, using a split separator, and explode it in a function, that will work with the results.

您可以使用拆分分隔符将字符串作为数组传递，并将其分解为一个函数，该函数将处理结果。

For a trivial example, if you have a string array like this: 'one|two|tree|four|five', and want to know if two is in the array, you can do this way:

举个简单的例子，如果你有一个这样的字符串数组：'one|two|tree|four|five'，并且想知道数组中是否有两个，你可以这样做：

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 ;

I want to create a set of usefull mysql functions to work with this method. Anyone interested please contact me.

我想创建一组有用的 mysql 函数来使用这种方法。有兴趣者请联络我。

For more examples, visit http://blog.idealmind.com.br/mysql/how-to-use-string-as-array-in-mysql-and-work-with/

更多示例，请访问http://blog.idealmind.com.br/mysql/how-to-use-string-as-array-in-mysql-and-work-with/