Mysql에 Data를 Insert할때, 특정 구분자로 구분되어져 있는 데이터를 쪼개어 Insert 하고자 할때 프로그램(JSP/PHP/C/Delphi/Etc)에서 직접 문자열을 잘라서, 각각 Insert 하는 방법도 있지만, Mysql Stored Procedure를 이용하여 Procedure내에서 잘라 Insert하는 방법도 있다는걸 보여주기 위한 예제임.
DELIMITER // DROP PROCEDURE IF EXISTS split_string // CREATE PROCEDURE split_string ( IN input TEXT , IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER COMMENT 'Splits a supplied string using using the given delimiter, placing values in a temporary table' BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END // DELIMITER ; /* Stored Procedure 호출예제 */ CALL split_string("홍길동,박문수,임꺽정,장길산",","); SELECT * FROM SplitValues; CREATE TEMPORARY TABLE TestJoin (join_field VARCHAR(20) PRIMARY KEY) ENGINE=MEMORY; INSERT INTO TestJoin VALUES ('Kaj'),('Colin'); SELECT tj.* FROM TestJoin tj INNER JOIN SplitValues sv ON tj.join_field = sv.value;