[MySQL] 가로행과 세로열 값을 모두 구분자로 묶기(GROUP_CONCAT,CONCAT_WS)
페이지 정보
작성자 서방님 댓글 0건 조회 122회 작성일 13-05-02 17:16본문
데이타베이스를 사용하다 보면 데이타를 구분자('|' 또는 '\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를 해주어야 하겠다.
먼저 예시 테이블을 만들어 보겠다.
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');
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
$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";
}
?>
$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 예제 (Language : sql)
CONCAT는 Field를 하나의 문자열로 묶어주는 함수이다.
다음과 같이 사용할 수 있다.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;
결과는 다음과 같다.
이 방법을 사용하면 한번의 Query로 처음에 보여준 PHP코드 처럼 할 필요가 없다는 것을 알 수 있을 것이다.
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코드를 만들어 보겠다.
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 )
댓글목록
등록된 댓글이 없습니다.
