4

I've created a (complex) sub-query where I put the results in a variable like:

@mylist := (select .... )

The returned value is a comma-delimeted string.. Because in the sub-query I also use concat(), the value is a string.

In the same SQL I want to use this variable in another subquery like:

where table.mycolumn IN (@mylist)

The problem is that because @mylist is a string Mysql reads the query as:

where table.mycolumn IN('575030,655156,655157')

while I want it to be execute as 

where table.mycolumn IN(575030,655156,655157)

How can I convert the string to an (numeric) array?

PS: I am using mysql 5.1

Patrick
  • 383
  • 1
  • 2
  • 19

1 Answers1

3

You can use the function FIND_IN_SET (it is available in the mysql version you specified). It splits the provided string (in your case stored in the variable) using the comma as separator and returns the index of the index of the first occurrence of the specified value (0 if not found)

/* building the list */
> SELECT @mylist :=GROUP_CONCAT(id SEPARATOR ',') FROM users WHERE id < 10;
+-----------------------------------------+
| @mylist:=group_concat(id separator ',') |
+-----------------------------------------+
| 0,2,3,4,5,6,7,8,9                       |
+-----------------------------------------+

> SELECT id, mail FROM users WHERE FIND_IN_SET(id, @mylist);

The automatic casting is enough to automatically manage the comparison between the original type and the final string most of the cases.

UPDATE

Although it answers your question, the proposed solution can get slow when looking for huge amounts of ids. A way better solution would be to drop the use of variables and store the results in a temporary table

CREATE [TEMPORARY] TABLE IF NOT EXISTS tmp_users (
  "id" INT,
  PRIMARY KEY (id)
)

INSERT INTO tmp_users (...);
Cavaz
  • 2,996
  • 24
  • 38
  • Will this work irrespective of what the separator in @mylist is? – MontyPython Oct 12 '16 at 09:39
  • 1
    no, according to the documentation there is no parameter to change the separator. It is possible to add a `FIND_IN_SET(id, REPLACE(@mylist, 'my_separator', ','))` though, but it won't work if the values to extract can contain commas. – Cavaz Oct 12 '16 at 09:48
  • Given this limitation FIND_IN_SET is good to have. I don't remember how this is done in Oracle 11g. – MontyPython Oct 12 '16 at 09:50
  • Just one more question; is it better to use WHERE FIND_IN_SET(id, @mylist)>0 , because if no matches are found it returns '0' ? – Patrick Oct 12 '16 at 09:58
  • `0` evaluates to false ([`FALSE` in fact _is_ `0`](http://stackoverflow.com/a/10852538/1029516)), so since there is no way the function returns a negative value the `>0` is superfluous. – Cavaz Oct 12 '16 at 10:11
  • Does it use the indexes? For example if I hardcode the SQL like where FIND_IN_SET(table.mycolumn,'575030,655156,655157') it's fast, but using FIND_IN_SET(table.mycolumn,@mylist) takes ages. What could be the reason for the performance drop? – Patrick Oct 12 '16 at 10:14
  • the _SET_ in the function name seems to suggest an indexing, but it could be that such set if rebuilt for each comparison. If you want to store a substantial amount of ids to use in later queries the best way should be to store it in a indexed temporary table – Cavaz Oct 12 '16 at 10:34