[MySQL] GROUP_CONCAT

Mysql의 GROUP_CONCAT() 함수가 있다. 이 함수의 예시는 다음과 같다.

CREATE TABLE `friend` (

`profile_id` int(11) DEFAULT NULL,

`friend_id` int(11) DEFAULT NULL

) ENGINE=InnoDB;

INSERT INTO friend(profile_id, friend_id) VALUES (1,1),(1,2),(1,3),(1,4),(2,1),(3,1),(3,2),(3,4),(4,1),(4,1)

SELECT DISTINCT profile_id, friend_id FROM friend ORDER BY profile_id

결과는 다음과 같다.

list 형태로 보려면 GROUP_CONCAT과 DISTINCT를 이용한다.

SELECT DISTINCT profile_id, GROUP_CONCAT(DISTINCT friend_id ORDER BY friend_id) friend_id_list 

FROM friend 

GROUP BY profile_id 

ORDER BY profile_id

, 가 아닌 ; 을 쓰려면 SEPARATOR를 이용한다.

SELECT DISTINCT profile_id, GROUP_CONCAT(DISTINCT friend_id ORDER BY friend_id SEPARATOR ';') friend_id_list 

FROM friend 

GROUP BY profile_id 

ORDER BY profile_id

GROUP_CONCAT 사용시 유의할 점은 디폴트 값이 좀 작다. 

다음 예시를 살펴보낟.

CREATE TABLE `friend2` (

`profile_id` int(11) DEFAULT NULL,

`friend_id` varchar(1000) 

) ENGINE=InnoDB;

INSERT INTO friend2(profile_id, friend_id) VALUES (1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111113')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111114')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111115')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111116')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111117')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111118')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111119')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111120')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111121')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111122')

,(1,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111123')

SELECT DISTINCT profile_id, GROUP_CONCAT(DISTINCT friend_id ORDER BY friend_id) friend_id_list 

FROM friend2

GROUP BY profile_id 

ORDER BY profile_id

결과를 보면, 맨 끝에 결과가 짤려 있다.

'1', '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111113,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111114,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111115,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111116,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111117,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111118,111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111119,111111111111111111111111'

그 이유는 GROUP_CONCAT 의 디폴트 최대값은 1024이다. 따라서 1024가 넘어가면서 짤리는 현상이 발생한다.

> show variables like "group_concat_max_len";

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| group_concat_max_len | 1024  |

+----------------------+-------+

1 row in set (0.00 sec)

이 문제를 해결하려면, group_concat_max_len 값을 길게 설정하거나 쓰지 않는다.

MariaDB [(none)]>  set @@group_concat_max_len = 50000;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like "group_concat_max_len";

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| group_concat_max_len | 50000 |

+----------------------+-------+

1 row in set (0.00 sec)

참고

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

http://ra2kstar.tistory.com/m/post/81

https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/