Dividir una cadena y recorrer los valores en el procedimiento MySql

stored-procedures

Question

Tengo una situación en la que tengo que pasar una cadena separada por comas al procedimiento MySQL y dividir esa cadena e insertar esos valores como filas en una tabla.

Como se muestra a continuación

Por ejemplo, si pasé la cadena ''jhon, swetha, sitha'' al procedimiento mysql, entonces debe dividir esa cadena por comas e insertar esos valores como 3 registros en una tabla.

 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

Pero esto no funcionó ninguna solución por favor.

Answer 1

Tendrás que ser un poco más cuidadoso con tu manipulación de cuerdas. No puede usar REPLACE() para esto, porque eso reemplazará múltiples ocurrencias, corrompiendo sus datos si un elemento en la lista separada por comas es una subcadena de otro elemento. La función de cadena INSERT() es mejor para esto, no debe confundirse con la INSERT utilizada para insertar en una tabla.

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 ;

A continuación, una tabla para probar:

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

La nueva mesa está vacía.

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

Llame al procedimiento.

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

Tenga en cuenta que "1 fila afectada" no significa lo que esperaría. Se refiere al último inserto que hicimos. Como insertamos una fila a la vez, si el procedimiento inserta al menos una fila, siempre obtendrá un recuento de filas de 1; Si el procedimiento no inserta nada, obtendrá 0 filas afectadas.

¿Funcionó?

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