Split string and loop the value in the MYSQL process

I encountered a situation , I have to pass the comma-separated string to the MySQL procedure and split the string and insert these values ​​into the table as rows.

As shown in the image below

For example, if I pass the'jhon, swetha, sitha' string to the mysql procedure, I must split the string with commas and insert these values ​​into the table as 3 records.

p>

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

But this does not solve any problems.

You need to be more careful with string manipulation. You cannot use REPLACE() because this will replace multiple occurrences, If one element in the comma-separated list is a substring of another element, it will corrupt your data. The INSERT() string function is better for this, don't use the INSER used to insert the table T statement confusion.

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 ;

Next, test the table:

CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c1` varchar(64) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

The new table is empty.

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

Call the program.

mysql> CALL insert_csv('foo,bar,buzz,fizz');
Query OK, 1 row affected (0.00 sec) 

Please note that "Affected 1 row" does not represent your expectation. It refers to the last insertion we made. Since we insert one row at a time, if the procedure inserts at least one row, it will always Get the number of rows 1 ;If the program does not insert anything, you will be affected by 0 lines.

Is it useful?

mysql> SELECT * FROM t1;
+----+------+
| id | c1 |
+----+------+
| 1 | foo |
| 2 | bar |
| 3 | buzz |
| 4 | fizz | 
+----+------+
4 rows in set (0.00 sec)