[MySQL] 데이터를 구분자로 Split 하기

[MySQL] 데이터를 구분자로 Split 하기

개요

DB 테이블에 컬럼이 다음과 같은 형식으로 되어있는 데이터가 있었는데 구분자를 분리해서 

개수가 몇개인지 카운트를 하고싶었다.

test_columns

apple

apple, banana

banana, pineapple

pineapple

 

MySQL에서 특정 문자를 기준으로 split 하는 함수에는 SUBSTRING_INDEX() 함수가 있는데,

이 함수는 인덱스에 해당하는 라인만 뽑기 때문에 데이터가 많을 경우 원하는 결과를 얻기 위해선 프로시저를 사용해야 했다..😥

 

SELECT 문으로 데이터 하나 뽑자고 프로시저를 사용하는 건 너무 과한 것 같아 다른 방법을 알아보기로 했다.

 

JSON_TABLE

열심히 서칭한 결과 JSON_TABLE()이라는 아주 좋은 방법이 있었다.

json_table은 MySQL에서 json 데이터를 효율적으로 처리하고 분석하는 데 매우 유용한 도구다.

 

❗ JSON_TABLE은 MySQL 8.0.4 이상 버전에서만 지원한다.

 

구분자를 기준으로 데이터를 분리하기

내가 사용한 방법은 다음과 같은데. json_array를 사용하여 json 배열로 변환 후 split 하는 쿼리를 사용했다.

먼저 다음과 같은 테스트 테이블을 생성해준다.

-- test 테이블 생성
CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    test_column VARCHAR(255)
);

-- 샘플 데이터 삽입
INSERT INTO test (test_column) VALUES
('1,2,3,4'),
('5,6,7'),
('8,9,10'),
('11,12'),
('13');

 

그 후 JSON 배열로 변환하여 결과값을 도출했다.

SELECT temp.split_column
FROM test t
JOIN JSON_TABLE(
    REPLACE(JSON_ARRAY(t.test_column), ',', '","'),
    '$[*]' COLUMNS (split_column VARCHAR(50) PATH '$')
) temp;

아름다운 결과값

 

단계별 설명

해당 쿼리를 단계별로 설명하면 다음과 같다.

REPLACE(JSON_ARRAY(t.test_column), ',', '","')

test_column 값을 JSON 배열 형식으로 변환한다. ex) 1,2,3,4 --> [1,2,3,4]

그 후 배열 문자열에서 ','를 json 배열의 요소를 구분하는 쌍따옴표(")로 변경한다.

ex) ["1,2,3,4"]  --> ["1","2","3","4"]

JSON_TABLE(
    REPLACE(JSON_ARRAY(t.test_column), ',', '","'),
    '$[*]' COLUMNS (
        split_column VARCHAR(50) PATH '$'
    )
) temp

json 배열 형식으로 변환된 문자열을 JSON_TABLE에 전달 후 json 배열의 모든 요소를 선택한다. ( $[*] )

그 후 json 배열의 각 요소를 split_column이라는 열로 변환한다. ( COLUMNS (name VARCHAR(50) PATH '$') )

 

마지막으로 json_table과 조인하여 test_column값과 json 배열의 각 요소를 조인한다.

 

조건절 넣기

만약 데이터가 많아 필터링한 데이터에서 분리를 하고 싶다면, 다음과 같이 할 수 있다.

SELECT temp.split_column
FROM (
    SELECT test_column
    FROM test
    WHERE test_column >= 5
) t
JOIN JSON_TABLE(
    REPLACE(JSON_ARRAY(t.test_column), ',', '","'),
    '$[*]' COLUMNS (split_column VARCHAR(50) PATH '$')
) temp;