mysql – 循环逗号分隔字符串的过程无效

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

代码如此帖子的答案中所提供.

我尝试使用find_in_set()函数,但只有当我从开始时传递id并且如果我随机传递ID时不工作它才有效.
这是我的表格脚本

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;

-- ----------------------------
-- 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);
    结束

strIds是varchar类型.

现在试试@ strIDs =’2′

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 ;

您可以使用Debugger for MySQL调试您的过程.

EDIT2：

我没有程序就这样做.尝试使用FIND_IN_SET功能,例如 –

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

或者创造一个临时. table,用id值填充它并在UPDATE语句中连接这两个表.