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

Question

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

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?

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.

---

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

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 )...

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:

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 ... ) ?

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

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)

Melissiamelita

This is nice but it is worth noting that you do not need to do the GROUP_CONCAT() in all the FIND_IN_SET(). You can just SELECT GROUP_CONCAT(tag.tag_id) AS tags_list and then HAVING FIND_IN_SET(20, tags_list) – Samalla

Answer 1

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

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

Masterpiece

Perfect! One tip: don't forget that since it's like a function call, there must be no space before the opening bracket/parenthesis, else you'll get an error Function dbname.FIND_IN_SET does not exist. – Melissiamelita

Well this is a solution, but I have found that FIND_IN_SET is extremely less efficient than IN is ... do any know if is there any way of really achieving what the question says, a way of splitting a string into an array-like variable? – Graphophone

@AndrésMongeMoreno: with Mysql 5.0 (which was the latest version at the time I answered this question) there are not that many options. With a current release I would probably suggest using the JSON functions instead: dev.mysql.com/doc/refman/5.7/en/json-functions.html – Masterpiece

@Masterpiece I undestand it no problem =D. Just wanted to leave something for future readers. In my experience, FIND_IN_SET is EXTREMELY less performant than any other choice (even looping with a cursor on the results prior to group by). – Graphophone

@AndrésMongeMoreno if it's even a remote option... I would recommend replacing mysql/mariadb with postgresql. That'll definitely give you much better performance – Masterpiece

Answer 2

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:

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.

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

Epistemic