遍历逗号分隔字符串的过程不起作用

2021-12-20 00:00:00 mysql stored-procedures

我在堆栈溢出中给出的答案的帮助下更正了代码.我想遍历逗号分隔的 Id 字符串，但无法这样做.下面给出的过程只更新第一条记录，不更新其他记录.需要进行哪些更正，以便我可以遍历逗号分隔的字符串.这是我的 SP 的代码

I have corrected the code with the help of answer given in stack overflow. I want to loop through the comma separated string of Ids but not able to do so. Below given procedure only updates first record and not updating other records. What corrections are required so that I can loop through comma separated string. This is the code for my SP

BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
   SET strIDs = '';
  END IF;

do_this:
LOOP
SET strLen = LENGTH(strIDs);

UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
SET strIDs = MID(strIDs, SubStrLen, strLen);

IF strIDs = NULL THEN
  LEAVE do_this;
END IF;
END LOOP do_this;
END

代码在这篇文章的答案中提供.

The code is as provided in the answers for this post.

我尝试使用 find_in_set() 函数，但它仅在我从头开始传递 id 时才有效，如果我随机传递 Id 则不起作用.这是我的表格脚本

I tried with find_in_set() function but it works only if I pass ids start from beginning and not working if I pass Ids randomly. This is my script for the table

CREATE TABLE `testtable` (

Id int(11) DEFAULT NULL,状态 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Id int(11) DEFAULT NULL, Status varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of testtable
-- ----------------------------
INSERT INTO `testtable` VALUES ('1', 'O');
INSERT INTO `testtable` VALUES ('2', 'O');
INSERT INTO `testtable` VALUES ('3', 'O');
INSERT INTO `testtable` VALUES ('4', 'O');
INSERT INTO `testtable` VALUES ('5', 'O');

这是存储过程开始UPDATE TestTable SET status = 'C' WHERE Id = FIND_IN_SET(Id, strIDs);结束

This is the stored procedure BEGIN UPDATE TestTable SET status = 'C' WHERE Id = FIND_IN_SET(Id, strIDs); END

strIds 是 varchar 类型.

strIds is varchar type.

现在试试@strIDs='2'

Now try @strIDs='2'

推荐答案

试试这个(语法错误已修复，没有 CAST 函数)-

Try this one (syntax errors are fixed and without CAST function) -

DELIMITER $$

CREATE PROCEDURE procedure1(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = LENGTH(strIDs);

    UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = NULL THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

您可以使用 MySQL 调试器调试您的程序.

You can debug your procedure with Debugger for MySQL.

我会在没有程序的情况下做到这一点.尝试使用 FIND_IN_SET 函数，例如-

I'd do it without procedure. Try to use FIND_IN_SET function, e.g. -

SET @strIDs = '1,2,3'; -- your id values
UPDATE TestTable SET status = 'C' WHERE FIND_IN_SET(id, @strIDs);

或者创建一个临时文件.表，用 id 值填充它并在 UPDATE 语句中连接这两个表.

Or create a temp. table, fill it with id values and join these two tables in UPDATE statement.

相关文章