将查询结果分配到MySQL过程中的变量中

Here is the piece of sql from my procedure:

这是我的程序中的一段sql：

   .... 
   SET @list := (
        SELECT
            `i`.`id` 
        FROM
            `Item` AS `i`
        Order by RAND()
        LIMIT 10
        );
    RETURN CONCAT_WS( '-', @list );

Inside procedure, I need to set query's results (yes, query returns multiple rows as a result) into some variable.

在内部过程中，我需要将查询结果（是的，查询返回多行作为结果）设置为某个变量。

Then as a second variable, I need to concatenate previous results into one string.

然后作为第二个变量，我需要将以前的结果连接成一个字符串。

But when I do it, I get following error:

但当我这样做时，我得到以下错误：

Sub-query returns more than 1 row

子查询返回多于1行

So the question is, what am I doing wrong?

所以问题是，我做错了什么？

By the way, I know about group_concat. In second part of procedure, there is a requirement for checking if some id exists on this @list variable: find_in_set(item_id, @list ) And the query returns random results every time when I call it. That's why, calling sub-query 2 times: 1 time as group concat string, second time just as list of results is not a solution for me. So, I need them as a set stored in variable.

顺便说一句，我知道group_concat。在过程的第二部分中，需要检查此@list变量上是否存在某个id：find_in_set（item_id，@ list）每次调用时，查询都会返回随机结果。这就是为什么，调用子查询2次：1次作为组连接字符串，第二次就像结果列表不是我的解决方案。所以，我需要它们作为存储在变量中的集合。

2 个解决方案

#1

You are approaching this is exactly the wrong way. There is no reason to store the ids in a list.

你正在接近这是完全错误的方式。没有理由将ID存储在列表中。

In the second part of the procedure, you should just be using a subquery:

在该过程的第二部分中，您应该只使用子查询：

where exists (select 1
              from item i
              where i.id = <outer table>.item_id
             )

If you really did want to put things into a list, you would use group_concat() as you allude to:

如果你真的想把东西放到列表中，你可以使用group_concat（）作为你的提示：

    SELECT @list := GROUP_CONCAT(i.id  ORDER BY RAND() SEPARATOR '-') as ids
    FROM Item i;

I don't see a use for storing the value in a variable, nor for ordering the ids randomly.

我没有看到将值存储在变量中的用途，也没有用于随机排序ID。

#2

So the question is, what am I doing wrong?

所以问题是，我做错了什么？

What you're doing wrong is trying to store a result set in a user variable.

你做错了是试图将结果集存储在用户变量中。

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html says:

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html说：

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

可以从一组有限的数据类型为用户变量分配值：整数，十进制，浮点，二进制或非二进制字符串或NULL值。

This kind of variable cannot store an array or a result set. Only one scalar value.

这种变量不能存储数组或结果集。只有一个标量值。

This question has code that seems to be related to Checking and preventing similar strings while insertion in MySQL

这个问题的代码似乎与在MySQL中插入时检查和阻止类似字符串有关