user2022678 user2022678 - 1 year ago 251
MySQL Question

Split/explode mysql string in numeric values

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

Answer Source

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.


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

  "id" INT,

INSERT INTO tmp_users (...);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download