MySQL에서 같은 ID를 가진 여러 개의 레코드를 하나의 행으로 합치고 싶을 때가 있습니다. 이럴 때 CONCAT_WS() 함수와 GROUP_CONCAT() 함수를 함께 사용하면 손쉽게 해결할 수 있습니다.
CONCAT_WS()는 지정한 구분자를 기준으로 여러 문자열을 연결하고, GROUP_CONCAT()은 그룹별로 결과 값을 하나의 문자열로 묶어 줍니다. 두 함수를 조합하면 ID별로 레코드를 그룹화하면서 원하는 특수 문자로 구분된 형태로 출력할 수 있습니다.
1. 예제 테이블 생성하기
먼저 실습에 사용할 테이블을 생성합니다.
mysql> create table DemoTable2016
-> (
-> UserId int,
-> UserName varchar(20)
-> );
Query OK, 0 rows affected (0.51 sec)
2. 샘플 데이터 삽입하기
INSERT 명령을 사용해 몇 개의 레코드를 입력합니다. 의도적으로 동일한 UserId를 가진 데이터를 여러 개 넣었습니다.
mysql> insert into DemoTable2016 values(1,'Chris');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable2016 values(2,'Bob');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable2016 values(1,'David');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2016 values(2,'Carol');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2016 values(1,'Sam');
Query OK, 1 row affected (0.14 sec)
3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 전체 데이터를 조회합니다.
mysql> select *from DemoTable2016;
실행 결과는 다음과 같습니다.
+--------+----------+
| UserId | UserName |
+--------+----------+
| 1 | Chris |
| 2 | Bob |
| 1 | David |
| 2 | Carol |
| 1 | Sam |
+--------+----------+
5 rows in set (0.00 sec)
4. GROUP_CONCAT과 CONCAT_WS로 레코드 연결하기
이제 동일한 UserId를 가진 레코드들을 하이픈(-)으로 구분하여 하나의 행으로 연결하는 쿼리입니다.
mysql> select group_concat(concat_ws('-',UserName,UserId)) from DemoTable2016 group by UserId;실행 결과는 다음과 같습니다.
+----------------------------------------------+
| group_concat(concat_ws('-',UserName,UserId)) |
+----------------------------------------------+
| Chris-1,David-1,Sam-1 |
| Bob-2,Carol-2 |
+----------------------------------------------+
2 rows in set (0.00 sec)
쿼리 동작 원리
위 쿼리는 다음과 같은 순서로 동작합니다.
① CONCAT_WS('-', UserName, UserId) : 각 행마다 이름과 ID를 하이픈(-)으로 연결합니다. 예를 들어 'Chris'와 1은 'Chris-1'이 됩니다.
② GROUP BY UserId : UserId가 같은 행들을 하나의 그룹으로 묶습니다.
③ GROUP_CONCAT() : 각 그룹 내에서 생성된 문자열들을 기본 구분자인 콤마(,)로 이어 붙여 최종 결과를 만듭니다.
그 결과 UserId가 1인 레코드들은 'Chris-1,David-1,Sam-1'로, UserId가 2인 레코드들은 'Bob-2,Carol-2'로 각각 하나의 행에 출력되는 것을 확인할 수 있습니다.
참고 사항
GROUP_CONCAT()의 기본 구분자는 콤마(,)이지만, SEPARATOR 옵션을 사용하면 원하는 구분자로 변경할 수 있습니다. 또한 group_concat_max_len 시스템 변수의 기본값은 1024바이트이므로, 대량의 데이터를 연결할 경우에는 이 값을 적절히 늘려주는 것이 좋습니다.