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

Procedure to loop through comma separated string is not working

提问by Microsoft Developer

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

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

代码如本文的答案中所提供。

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

我尝试使用 find_in_set() 函数，但它仅在我从头开始传递 ids 时才有效，如果我随机传递 Ids 则不起作用。这是我的桌子脚本

CREATE TABLE `testtable` (

Idint(11) DEFAULT NULL, Statusvarchar(255) COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Idint(11) DEFAULT NULL, Statusvarchar(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');

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

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

strIds is varchar type.

strIds 是 varchar 类型。

Now try @strIDs='2'

现在试试@strIDs='2'

采纳答案by Devart

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

试试这个（语法错误是固定的，没有 CAST 函数） -

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 ;

You can debug your procedure with Debugger for MySQL.

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

EDIT2:

编辑2：

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

我会在没有程序的情况下做。尝试使用FIND_IN_SET函数，例如 -

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

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

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

回答by Payou

Maybe this post is a bit too old but I have try the code presented by Devart and it is not working for me.

也许这篇文章有点太旧了，但我已经尝试了 Devart 提供的代码，但它对我不起作用。

With few modification, this version works for me :

稍加修改，这个版本对我有用：

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 = CHAR_LENGTH(strIDs);

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

    SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;
    SET strIDs = MID(strIDs, SubStrLen, strLen);

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

END
$$

DELIMITER ;

Explanations:

说明：

1) Why "+2" IN SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;

1）为什么“+2”输入 SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;

When you execute the MID function on the next line, string index begin with 1. If you have the following string '4500,2', with the Devart version, MID Looks like MID('4500,2',4,6) which is return ',2'.

在下一行执行 MID 函数时，字符串索引从 1 开始。如果你有下面的字符串 '4500,2'，在 Devart 版本中，MID 看起来像 MID('4500,2',4,6) 其中是返回',2'。

So if you add 1 at the substring length, you are on the delimiter. It's not enough. So you add the length of the delimiter. Now it's good.

因此，如果在子字符串长度处添加 1，则位于分隔符上。这不够。所以你添加了分隔符的长度。现在好了。

2) Why IF strIDs = '' THENin the loop condition?

2）为什么IF strIDs = '' THEN在循环条件中？

Because when you do MID you return a string even if this string are empty.

因为当你做 MID 时，即使这个字符串是空的，你也会返回一个字符串。

Devart procedure are patched ! Thank a lot for you're anwser :)

Devart 程序已打补丁！非常感谢您的回答:)

回答by Neurotransmitter

I didn't find any suitable method, so I ended up making my own. It's pretty simple.

我没有找到任何合适的方法，所以我最终自己做了。这很简单。

PROCEDURE db.loop_through_array()
BEGIN

  DECLARE strIDs varchar(150) DEFAULT 'one,two,three';
  DECLARE element varchar(150);

  WHILE strIDs != '' DO

    SET element = SUBSTRING_INDEX(strIDs, ',', 1);      

    UPDATE TestTable SET status = 'C' WHERE Id = element;

    IF LOCATE(',', strIDs) > 0 THEN
      SET strIDs = SUBSTRING(strIDs, LOCATE(',', strIDs) + 1);
    ELSE
      SET strIDs = '';
    END IF;

  END WHILE;

END