分割/爆炸mysql字符串的数值

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.

返回的值是一个逗号分隔的字符串。因为在子查询中我也使用concat()，所以值是一个字符串。

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

在同一个SQL中，我想在另一个子查询中使用这个变量，比如:

where table.mycolumn IN (@mylist)

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

问题是，因为@mylist是一个字符串，Mysql读取查询如下:

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

PS:我用的是mysql 5.1

1 个解决方案

#1

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)

您可以使用函数FIND_IN_SET(它在您指定的mysql版本中可用)。它将所提供的字符串(在您的案例中存储在变量中)使用逗号作为分隔符，并返回指定值第一次出现的索引的索引(如果没有找到的话)

/* 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

虽然它回答了您的问题，但是当查找大量的id时，所提出的解决方案可能会变慢。一种更好的解决方案是放弃使用变量并将结果存储在临时表中

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

INSERT INTO tmp_users (...);

#1

/* 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);