您需要更加谨慎地进行字符串操作。您不能使用
REPLACe()它，因为如果用逗号分隔的列表中的一个元素是另一元素的子字符串，它将替换多次出现的数据，从而破坏数据。该
INSERT()字符串函数是为了这个美好的，不要与混淆
INSERT用于插入到表的语句。
DELIMITER $$DROP PROCEDURE IF EXISTS `insert_csv` $$CREATE PROCEDURE `insert_csv`(_list MEDIUMTEXT)BEGINDECLARE _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)
