拆分字符串并遍历MySql过程中的值

我遇到一种情况,我必须将逗号分隔的字符串传递给MySQL过程,然后拆分该字符串,并将这些值作为行插入表中。

如下图所示

例如,如果我将’jhon,swetha,sitha’字符串传递给mysql过程,则它必须用逗号分割该字符串,并将这些值作为3条记录插入表中。

  CREATE PROCEDURE new_routine (IN str varchar(30))   

BEGIN

DECLARE tmp varchar(10);

DECLARE inc INT DEFAULT 0;

WHILE INSTR(str, ',') DO

SET tmp = SUBSTRING(SUBSTRING_INDEX(str,',',inc),LENGTH(SUBSTRING_INDEX(str,',',inc-1))+1),',','');

SET str = REPLACE(str, tmp, '');

//insert tmp into a table.

END WHILE;

END

但这请没有任何解决方案。

回答:

您需要更加谨慎地进行字符串操作。您不能使用REPLACE()它,因为如果用逗号分隔的列表中的一个元素是另一元素的子字符串,它将替换多次出现的数据,从而破坏数据。该INSERT()字符串函数是为了这个美好的,不要与混淆INSERT用于插入到表的语句。

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_csv` $$

CREATE PROCEDURE `insert_csv`(_list MEDIUMTEXT)

BEGIN

DECLARE _next TEXT DEFAULT NULL;

DECLARE _nextlen INT DEFAULT NULL;

DECLARE _value TEXT DEFAULT NULL;

iterator:

LOOP

-- exit the loop if the list seems empty or was null;

-- this extra caution is necessary to avoid an endless loop in the proc.

IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN

LEAVE iterator;

END IF;

-- capture the next value from the list

SET _next = SUBSTRING_INDEX(_list,',',1);

-- save the length of the captured value; we will need to remove this

-- many characters + 1 from the beginning of the string

-- before the next iteration

SET _nextlen = LENGTH(_next);

-- trim the value of leading and trailing spaces, in case of sloppy CSV strings

SET _value = TRIM(_next);

-- insert the extracted value into the target table

INSERT INTO t1 (c1) VALUES (_value);

-- rewrite the original string using the `INSERT()` string function,

-- args are original string, start position, how many characters to remove,

-- and what to "insert" in their place (in this case, we "insert"

-- an empty string, which removes _nextlen + 1 characters)

SET _list = INSERT(_list,1,_nextlen + 1,'');

END LOOP;

END $$

DELIMITER ;

接下来,一个测试表:

CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c1` varchar(64) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

新表为空。

mysql> SELECT * FROM t1;

Empty set (0.00 sec)

调用过程。

mysql> CALL insert_csv('foo,bar,buzz,fizz');

Query OK, 1 row affected (0.00 sec)

请注意“受影响的第一行”并不意味着您所期望的。它指的是我们所做的最后一次插入。由于我们一次插入一行,因此,如果该过程 至少

插入一行,那么您将始终获得1的行计数。如果该过程不插入任何内容,则将影响0行。

奏效了吗?

mysql> SELECT * FROM t1;

+----+------+

| id | c1 |

+----+------+

| 1 | foo |

| 2 | bar |

| 3 | buzz |

| 4 | fizz |

+----+------+

4 rows in set (0.00 sec)

以上是 拆分字符串并遍历MySql过程中的值 的全部内容, 来源链接: utcz.com/qa/423631.html

回到顶部