aws rds mysql-group\u concat返回多行而不是逗号分隔的字符串

pcww981p 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(308)

我有一个 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');

sql mysql subquery stored-procedures group-concat

来源：https://stackoverflow.com/questions/50316356/aws-rds-mysql-group-concat-returns-multiple-rows-instead-of-comma-separated-st

1条答案

按热度按时间

j1dl9f461#

问题是子查询。拆下 group by :

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 . 这个 group_concat() 什么都没做。
此外，还有 CAST() 是不必要的。这通常写为：

SELECT GROUP_CONCAT(r.receiver_id SEPARATOR ',') AS receiver_ids
FROM receiver r JOIN
     user u
     ON u.user_id = r.user_id
WHERE u.user_name LIKE 'myName';

如果 'myName' 没有使用通配符，那么 = 比…更合适 like .
如果 receiver_id 不是唯一的 receiver ，则可能需要添加 distinct 到 group_concat() .

赞(0）回复(0）举报 2021-06-21

我来回答

aws rds mysql-group\u concat返回多行而不是逗号分隔的字符串

1条答案

相关问题

热门标签

最新问答