我有一个 Stored Procedure
需要三个参数,其中一个是 TEXT
它应该包含逗号分隔的值 ids
,类似这样的-> '12345,54321,11111,22222'
,这将插入一行,其中包含列表中每个id的数据。下面是 Stored Procedure
:
DELIMITER //
-- Create Stored Procedure
CREATE PROCEDURE MyProcedure(
IN ItemUUID VARCHAR(255),
IN ReceiverIds TEXT,
IN ItemCreated VARCHAR(255)
)
BEGIN
DECLARE strLen INT DEFAULT 0;
DECLARE SubStrLen INT DEFAULT 0;
IF ReceiverIds IS NULL THEN
SET ReceiverIds = '';
END IF;
do_this:
LOOP
SET strLen = LENGTH(ReceiverIds);
INSERT INTO item_receiver (item_uuid, receiver_id, item_created)
VALUES (ItemUUID ,SUBSTRING_INDEX(ReceiverIds, ',', 1),ItemCreated);
SET SubStrLen = LENGTH(SUBSTRING_INDEX(ReceiverIds, ',', 1)) + 2;
SET ReceiverIds = MID(ReceiverIds, SubStrLen, strLen);
IF ReceiverIds = '' THEN
LEAVE do_this;
END IF;
END LOOP do_this;
END//
DELIMITER ;
获取逗号分隔值的步骤 ids
,类似这样的-> '12345,54321,11111,22222'
我执行 subquery
然而,当我称之为 Stored Procedure
我得到这个错误-> Error Code: 1242. Subquery returns more than 1 row
```
SET group_concat_max_len = 2048;
call MyProcedure('random_test_uuid',(
SELECT CAST(GROUP_CONCAT(receiver_id SEPARATOR ',') AS CHAR) AS receiver_ids FROM receiver
WHERE user_id LIKE (SELECT user_id FROM user WHERE user_name LIKE 'myName')
GROUP BY receiver_id ),
'2017-09-24 23:44:32');
1条答案
按热度按时间j1dl9f461#
问题是子查询。拆下
group by
:与
group by
,您将为每个receiver_id
. 这个group_concat()
什么都没做。此外,还有
CAST()
是不必要的。这通常写为:如果
'myName'
没有使用通配符,那么=
比…更合适like
.如果
receiver_id
不是唯一的receiver
,则可能需要添加distinct
到group_concat()
.