MySQL에서 문자열을 개별 행으로 변환하는 방법

1. WITH RECURSIVE를 사용한 문자열 분할

MySQL 8.0 이상에서는 WITH RECURSIVE를 사용하여 문자열을 개별 행으로 변환할 수 있습니다.

- 문자열을 특정 구분자로 나누는 예제

WITH RECURSIVE split_string (str, remaining) AS (
  SELECT '', '김철수,이영희,박민수'
  UNION ALL
  SELECT 
    SUBSTRING_INDEX(remaining, ',', 1), 
    IF(LOCATE(',', remaining) > 0, SUBSTRING(remaining, LOCATE(',', remaining) + 1), '') 
  FROM split_string
  WHERE remaining != ''
)
SELECT str FROM split_string WHERE str != '';

실행 결과:

str

김철수

이영희

박민수

 
 * 설명

• SUBSTRING_INDEX(remaining, ',', 1) → 첫 번째 값을 추출
• LOCATE(',', remaining) → 콤마 위치 찾기
• IF(LOCATE(',', remaining) > 0, SUBSTRING(remaining, LOCATE(',', remaining) + 1), '') → 남은 문자열을 업데이트
• WITH RECURSIVE를 사용하여 계속해서 나누기

반응형

2. 숫자 테이블을 활용한 문자열 분할 (MySQL 5.7 이하에서 가능)

MySQL 5.7에는 WITH RECURSIVE가 없기 때문에 숫자 테이블을 활용하여 문자열을 분할할 수도 있습니다.

- 임시 숫자 테이블을 활용한 문자열 분할

SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX('김철수,이영희,박민수', ',', numbers.n), 
    ',', 
    -1
  ) AS name
FROM 
  (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) numbers
WHERE 
  CHAR_LENGTH('김철수,이영희,박민수') - CHAR_LENGTH(REPLACE('김철수,이영희,박민수', ',', '')) >= numbers.n - 1;

실행 결과:

name

김철수

이영희

박민수

 
* 설명

• SUBSTRING_INDEX()를 사용하여 구분자로 분할
• 숫자 테이블을 활용하여 n 번째 요소 추출

반응형

3. JSON_TABLE()을 활용한 문자열 분할 (MySQL 8.0+)

MySQL 8.0 이상에서는 JSON_TABLE()을 활용하여 문자열을 개별 행으로 변환할 수도 있습니다.

-  JSON_TABLE() 사용 예제

SELECT value AS name
FROM JSON_TABLE(
  CONCAT('["', REPLACE('김철수,이영희,박민수', ',', '","'), '"]'),
  "$[*]" COLUMNS (value VARCHAR(100) PATH "$")
) AS tmp;

실행 결과:

name

김철수

이영희

박민수

 
- 설명

• REPLACE()를 사용하여 JSON 형식(["값1","값2","값3"])으로 변환
• JSON_TABLE()을 사용하여 배열을 행으로 변환

반응형

결론

GROUP_CONCAT()의 반대 개념은 문자열을 개별 행으로 나누는 것입니다.
-  MySQL 8.0 이상:

1. WITH RECURSIVE → SQL 내에서 반복적으로 문자열을 나눌 때 사용
2. JSON_TABLE() → JSON 기반 문자열 분할 가능

-  MySQL 5.7 이하: 3. 숫자 테이블 활용 → 임시 숫자 테이블을 사용하여 문자열을 나눌 수 있음
사용하는 MySQL 버전에 따라 적절한 방법을 선택하면 효율적으로 문자열을 행으로 변환할 수 있습니다!