[MySQL] 가로행과 세로열 값을 모두 구분자로 묶기(GROUP_CONCAT,CONCAT

[MySQL] 가로행과 세로열 값을 모두 구분자로 묶기(GROUP_CONCAT,CONCAT_WS)

2007/07/25 17:34

1 Commnet

[공지]이미지나 링크가 깨졌다면 댓글 부탁드립니다.

데이타베이스를 사용하다 보면 데이타를 구분자('|' 또는 '\n')으로 구분하여 출력해야할 경우가 생긴다.
가령 데이타필드가 ID,NAME,EMAIL, GROUPNUM 로 되어있고 GROUPNUM로 GROUPING되어 있을때

ID|NAME|EMAL; ID|NAME|EMAL; ID|NAME|EMAL
ID|NAME|EMAL; ID|NAME|EMAL
ID|NAME|EMAL; ID|NAME|EMAL; ID|NAME|EMAL; ID|NAME|EMAL

이런식으로 출력하고 싶을 경우이다.
이런 데이타를 어디다 쓰냐고 하겠지만, 데이타 전송시 용량이 줄어들기 때문에 순수하게 전송속도를 위해서라면 XML,JSON방식보다 훨씬 좋다. ( 이에 대해서는 http://blog.jidolstar.com/167 를 참고하길 바란다. )

이러한 방식으로 데이타를 사용시에는 각 필드를 구분하기 위한 구분자(|, ;)가 필드내용에 없어야 한다. 만약 있다면 replace를 해주어야 하겠다.

먼저 예시 테이블을 만들어 보겠다.

USER_TEST 테이블 생성 Query (Language : sql)

CREATE TABLE USER_TEST(
IDX INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(255) NOT NULL,
GROUPNUM MEDIUMINT UNSIGNED DEFAULT '0',
KEY IDX_GROUPNUM(GROUPNUM)
);

INSERT INTO USER_TEST
(`NAME`,`EMAIL`,`GROUPNUM`)
VALUES
('지도리', 'jidori@nnn.com', '1'),
('지돌스타', 'jidolstar@nnn.com', '1'),
('방실이', 'bangsiri@nnn.com', '2'),
('설운도', 'seolundo@nnn.com', '2'),
('박주영', 'juyoung@nnn.com', '3'),
('이효리', 'hyri@nnn.com', '2');

초보자들은 이를 GROUPNUM별로 묶어서 출력하기 위해 아래와 비슷한 프로그래밍을 할 것이라 예상된다.

초보자들이 하는 PHP코딩 (Language : php)

<?php
$conn = mysql_connect('','','');
mysql_select_db('',$conn);

$query = "SELECT `GROUPNUM` FROM USER_TEST GROUP BY GROUPNUM";
$result = mysql_query($query);
while( $row = mysql_fetch_array($result) )
{
print_record($row[GROUPNUM]);
}

function print_record($group)
{
$query = "SELECT `IDX`, `NAME`, `EMAIL` FROM USER_TEST WHERE GROUPNUM='$group'";
$result = mysql_query($query);
$return_val = "";
while( $row = mysql_fetch_array($result) )
{
$return_val .= $row[IDX] . "|" . $row[NAME] . "|" . $row[EMAIL] .";";
}
echo $return_val."\n";
}
?>

결과

1|지도리|jidori@nnn.com;2|지돌스타|jidolstar@nnn.com;

3|방실이|bangsiri@nnn.com;4|설운도|seolundo@nnn.com;6|이효리|hyri@nnn.com;

5|박주영|juyoung@nnn.com;

위 처럼 하면 쉽게 짜는 듯하지만 실제로 데이타베이스의 이점을 잘 살리지 못하고 프로그래밍한 격이 된다.

MySQL 함수중 GROUP_CONCAT과 CONCAT, CONCAT_WS 가 있다.
이 함수를 이용하면 우리가 원하는 결과를 얻을 수 있다.

1. CONCAT

CONCAT는 Field를 하나의 문자열로 묶어주는 함수이다.
다음과 같이 사용할 수 있다.

CONCAT 예제 (Language : sql)

SELECT CONCAT(`IDX`,'|',`NAME`,'|',`EMAIL`) FROM USER_TEST;

결과는 다음과 같다.

1|지도리|jidori@nnn.com

2|지돌스타|jidolstar@nnn.com

3|방실이|bangsiri@nnn.com

4|설운도|seolundo@nnn.com

5|박주영|juyoung@nnn.com

6|이효리|hyri@nnn.com

2. CONCAT_WS

CONCAT_WS는 CONCAT과 거의 비슷하다. 하지만 구분자를 한번에 지정할 수 있다는 것이 특징이다.

CONCAT_WS 예제 (Language : sql)

SELECT CONCAT_WS('|', `IDX`, `NAME`, `EMAIL`) FROM USER_TEST;

결과는 CONCAT과 완전 동일하다.

3. GROUP_CONCAT

이 함수를 이용하면 GROUPING된 Record를 원하는 구분자를 이용해 문자열로 통합할 수 있다.

예시는 다음과 같다.

GROUP_CONCAT 예제 (Language : sql)

SELECT GROUP_CONCAT(NAME SEPARATOR ';') FROM USER_TEST GROUP BY GROUPNUM;

결과는 다음과 같다.

지도리;지돌스타

방실이;설운도;이효리

박주영

단, 이것만 기억하자. group_concat_max_len 시스템 변수에 의해 최대허용길이를 설정할 수 있는데, default값이 1024이다. 만약 1024byte가 넘어가면 GROUP_CONCAT에 의해 붙은 문자열은 잘린다.

제대로 사용해보기

Query문 부터 보자.

가로행과 세로열을 모두 구분자로 묶기 (Language : sql)

SELECT GROUP_CONCAT(
CONCAT_WS('|', `IDX`, `NAME`, `EMAIL`)
SEPARATOR '@')
FROM USER_TEST
GROUP BY GROUPNUM;

결과는 다음과 같다.

1|지도리|jidori@nnn.com;2|지돌스타|jidolstar@nnn.com

3|방실이|bangsiri@nnn.com;4|설운도|seolundo@nnn.com;6|이효리|hyri@nnn.com

5|박주영|juyoung@nnn.com

이 방법을 사용하면 한번의 Query로 처음에 보여준 PHP코드 처럼 할 필요가 없다는 것을 알 수 있을 것이다.

위의 Query를 이용해서 PHP코드를 만들어 보겠다.

MySQL함수를 잘 이용해서 최적화된 PHP코딩 (Language : php)

<?php
$conn = mysql_connect('','','');
mysql_select_db('',$conn);

$query = "SELECT GROUP_CONCAT( CONCAT_WS('|', `IDX`, `NAME`, `EMAIL`) SEPARATOR ';') FROM USER_TEST GROUP BY GROUPNUM";
$result = mysql_query($query);
while( $row = mysql_fetch_row($result) )
{
echo $row[0]."\n";
}
?>

결과는 첫번째 PHP코드와 같지만 훨씬 코드가 줄어들었음을 알 수 있다.

참고사이트

오라클에서 하는 방법 : http://blog.naver.com/xsoft/150017833358
GROUP_CONCAT() 메뉴얼 : http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
CONCAT() 및 CONCAT_WS() 메뉴얼 : http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

글쓴이 : 지돌스타 (http://blog.jidolstar.com )

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

'개발 > Database' 카테고리의 다른 글

[Flex] 로컬이미지 서버 전송 후 다시 불러오기 소스 (7)	2008/04/15
MS Visual C++ 6.0 환경에서 MySQL 연동하는 방법 (13)	2007/10/17
[MySQL] 가로행과 세로열 값을 모두 구분자로 묶기(GROUP_CONCAT,CONCAT_WS) (1)	2007/07/25
[대용량 Database의 Table] Clustering Factor 에 대한 이해 (0)	2007/07/07