mysql中实现字符串分割sp_split

 

sp_split :

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DELIMITER $$   CREATE DEFINER = 'test_user'@'%' PROCEDURE sp_split (IN p_str varchar(2000), IN p_split varchar(50), p_table_insert varchar(50), p_col_insert varchar(50)) BEGIN   IF p_str > '' THEN     WHILE (INSTR(p_str, p_split) <> 0) DO       SET @value = SUBSTRING(p_str, 1, INSTR(p_str, p_split) - 1);       SET @sql = CONCAT('INSERT ', p_table_insert, '(', p_col_insert, ') VALUES (?)');       PREPARE stmt FROM @sql;       EXECUTE stmt USING @value;       SET p_str = INSERT(p_str, 1, INSTR(p_str, p_split), '');     END WHILE;     SET @value = p_str;     SET @sql = CONCAT('INSERT ', p_table_insert, '(', p_col_insert, ') VALUES (?)');     PREPARE stmt FROM @sql;     EXECUTE stmt USING @value;     DEALLOCATE PREPARE stmt;   END IF; END $$   DELIMITER ;

 

 

e.g.

 

 

?

1 2 3

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_stock (stock_id int); TRUNCATE TABLE tmp_ou;   CALL sp_split(p_ouid_list,',','tmp_ou','ou_id');