如何使用MySQL存储过程的游标创建逗号分隔的值?

Question

我想从游标中公式化一个变量，最终值类似于："1,2,3"。以下是简单的存储过程：

OPEN cursor1;
BEGIN
LOOP1: LOOP
FETCH cursor1 INTO main_account_id;
IF no_more_rows THEN
CLOSE cursor1;
LEAVE LOOP1;
END IF;
// Here I want to achieve something like "1,2,3" from main_account_id
END LOOP LOOP1;
END;

Answer 1

首先将变量定义为 NULL：

SET @comma_sep_value = NULL;

然后在循环中使用 CONCAT_WS((：

SET @comma_sep_value = CONCAT_WS(',', @comma_sep_value, @next_element);

不会有尾随逗号，因此不需要 TRIM。

Answer 2

以下是我如何实现它的伪

：

SET @comma_sep_value = "";
OPEN cursor1;
BEGIN
LOOP1: LOOP
FETCH cursor1 INTO main_account_id;
IF no_more_rows THEN
CLOSE cursor1;
LEAVE LOOP1;
END IF;
SET @comma_sep_value = CONCAT(@comma_sep_value,main_account_id,","); 
END LOOP LOOP1;
END;
SET @comma_sep_value = TRIM(BOTH ',' FROM @comma_sep_value); 
SELECT @comma_sep_value as final_val;

备注：倒数第二行是修剪尾随逗号。