MySQL 저장 프로시저(Stored Procedure)에서 문자열을 특정 구분자로 분할하고 각 값을 순회하며 처리해야 하는 경우가 종종 있습니다. 이때 REPLACE() 함수를 사용할 필요 없이 SUBSTRING_INDEX()와 LOOP 문을 활용하면 손쉽게 구현할 수 있습니다.
1. 저장 프로시저 생성
먼저 쉼표(,)로 구분된 문자열을 받아 개별 값으로 분할한 뒤 테이블에 삽입하는 저장 프로시저를 만들어 보겠습니다.
mysql> DELIMITER //
mysql> CREATE PROCEDURE SP_SplitString(Value longtext)
-> BEGIN
-> DECLARE front TEXT DEFAULT NULL;
-> DECLARE frontlen INT DEFAULT NULL;
-> DECLARE TempValue TEXT DEFAULT NULL;
-> iterator:
-> LOOP
-> IF LENGTH(TRIM(Value)) = 0 OR Value IS NULL THEN
-> LEAVE iterator;
-> END IF;
-> SET front = SUBSTRING_INDEX(Value,',',1);
-> SET frontlen = LENGTH(front);
-> SET TempValue = TRIM(front);
-> INSERT INTO store (allValues) VALUES (TempValue);
-> SET Value = INSERT(Value,1,frontlen + 1,'');
-> END LOOP;
-> END //
Query OK, 0 rows affected (0.22 sec)
mysql> DELIMITER ;
위 프로시저의 동작 방식은 다음과 같습니다.
- SUBSTRING_INDEX(Value, ',', 1): 첫 번째 쉼표 앞의 문자열을 추출합니다.
- TRIM(): 추출한 값의 불필요한 공백을 제거합니다.
- INSERT(Value, 1, frontlen + 1, ''): 이미 처리한 부분을 원본 문자열에서 제거하여 남은 문자열만 유지합니다.
- LEAVE iterator: 더 이상 분할할 값이 없으면 루프를 종료합니다.
2. 데이터를 저장할 테이블 생성
다음으로 프로시저에서 분할된 값을 저장할 테이블을 생성합니다.
mysql> CREATE TABLE store(
-> Id int NOT NULL AUTO_INCREMENT,
-> allValues varchar(30),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.63 sec)
테이블 생성 직후에는 아직 데이터가 없으므로 조회 결과가 비어 있는 것을 확인할 수 있습니다.
mysql> select *from store;
Empty set (0.00 sec)
3. 저장 프로시저 호출 및 결과 확인
이제 CALL 명령어를 사용하여 저장 프로시저를 호출하고, 쉼표로 구분된 문자열을 전달합니다.
mysql> call SP_SplitString('Hi,Hello,Good Morning,Bye');
Query OK, 1 row affected (1.02 sec)프로시저 호출이 완료되면 테이블에 분할된 값들이 정상적으로 저장되었는지 확인해 보겠습니다.
mysql> select *from store;
실행 결과는 다음과 같습니다.
+----+--------------+
| Id | allValues |
+----+--------------+
| 1 | Hi |
| 2 | Hello |
| 3 | Good Morning |
| 4 | Bye |
+----+--------------+
4 rows in set (0.00 sec)
출력 결과에서 볼 수 있듯이 하나의 문자열에 담겨 있던 네 개의 값이 각각 별도의 행으로 분할되어 성공적으로 저장되었습니다. 이처럼 SUBSTRING_INDEX와 LOOP를 조합하면 MySQL 프로시저 내에서도 문자열 파싱 로직을 간단하게 구현할 수 있습니다.