GROUP_CONCAT 함수

설명

GROUP_CONCAT 함수는 그룹에서 NULL이 아닌 값들을 연결하여 결과 문자열을 VARCHAR 타입으로 반환한다. 질의 결과 행이 없거나 NULL 값만 있으면 NULL을 반환한다.

리턴 값의 최대 크기는 시스템 파라미터 group_concat_max_len의 설정을 따른다. 기본값은 1024바이트이며, 최소값은 4바이트, 최대값은 33,554,432바이트이다. 최대값을 초과하면 NULL을 반환한다.

중복되는 값을 제거하려면 DISTINCT 절을 사용하면 된다. 그룹 결과의 값 사이에 사용되는 기본 구분자는 쉼표(,)이며, 구분자를 명시적으로 표현하려면 SEPARATOR 절과 그 뒤에 구분자로 사용할 문자열을 추가한다. 구분자를 제거하려면 SEPARATOR 절 뒤에 빈 문자열(empty string)을 입력한다.

결과 문자열에 문자형 데이터 타입이 아닌 다른 타입이 전달되면, 에러를 반환한다.

GROUP_CONCAT 함수를 사용하려면 다음의 조건을 만족해야 한다.

입력 인자로 하나의 표현식(또는 컬럼)만 허용한다.
ORDER BY를 이용한 정렬은 오직 인자로 사용되는 표현식(또는 컬럼)에 의해서만 가능하다.
구분자로 사용되는 문자열은 문자형 타입만 허용하며, 다른 타입은 허용하지 않는다.
구문

GROUP_CONCAT([DISTINCT] {col | expression}
[ORDER BY {col | unsigned_int} [ASC | DESC]]
[SEPARATOR str_val])
expression : 수치 또는 문자열을 반환하는 하나의 연산식
str_val : 구분자로 쓰일 문자열
DISTINCT : 결과에서 중복되는 값을 제거한다.
ORDER BY : 결과 값의 순서를 지정한다.
SEPARATOR : 결과 값 사이에 구분할 구분자를 지정한다. 생략하면 기본값인 쉼표(,)를 구분자로 사용한다.
예제

SELECT GROUP_CONCAT(s_name) FROM code;
group_concat(s_name)
======================
‘X,W,M,B,S,G’

SELECT GROUP_CONCAT(s_name ORDER BY s_name SEPARATOR ‘:’) from code;
group_concat(s_name order by s_name separator ‘:’)
======================
‘B:G:M:S:W:X’

CREATE TABLE t(i int);
INSERT INTO t VALUES (4),(2),(3),(6),(1),(5);

SELECT GROUP_CONCAT(i*2+1 ORDER BY 1 SEPARATOR ”) FROM t;
group_concat(i*2+1 order by 1 separator ”)
======================

MySQL의 경우, 접속(access)은 항상 사용자 이름과 호스트 이름(또는 IP 주소)의 조합, 그리고 패스워드를 기반으로 승인이 이루어 진다-예를 들면, jcole@foo.example.bar 는 유효한 사용자 이름과 호스트 이름의 조합이다.

커넥션되는 클라이언트의 “네트워크 크레덴션(network credentials)”을 검증하기 위해서, MySQL은 인커밍 커넥션(incoming connection)의 IP 상에서 “이중 역방향-DNS 검색(double reverse-DNS lookup)”방식을 사용한다.

간단히 말하자면, MySQL은 우선 gethostbyaddr() 또는 이와 유사한 함수를 사용하여 역방향-DNS 검색을 경유하는 인커밍 커넥션의 IP 주소를 사용해서 호스트 이름을 알아 낸다. 그런 다음에는, gethostbyname() 또는 이와 유사한 함수를 사용하여 순방향-DNS 검색(forward-DNS lookup)을 경유하는 그 호스트 이름을 IP 주소로 해석한다. 마지막으로, MySQL은 원래의 커넥션 IP가 리턴된 IP중의 하나인지를 검증한다.

하지만 역방향-DNS 및 순방향-DNS 검색은 꽤 부하가 걸리며, 또한 DNS 서버에서의 응답 대기를 방해하기 때문에, 위와 같은 방식은 문제가 생기게 된다. 비록 타임 아웃을 포함시킬 수는 있지만, 이렇게 하면 30초 정도 오래 걸리게 된다. 이러한 오랜 오버 헤드를 피하기 위해서, MySQL은 내부적으로 호스트 이름 캐시를 사용한다.

지금까지의 문제점들을 정리하자면 아래와 같다:
– 호스트 이름 캐시는 정적으로(statically) 128 개의 엔트리를 가질 수 있다. 만일 캐시
가 가득 차게 되면, 오래된 엔트리들을 우선 없애 버린다. 만일 한 개의 MySQL 서버
에 128개 이상의 클라이언트를 커넥션하고자 한다면, 이 캐시는 거의 효율성을 잃게 된
다.
– 만일 어떤 IP주소 또는 호스트 이름을 해석하기 위해서 DNS 서버에 접속을 하는 것이
실패하게 된다면, 커넥션은 이루어지지 않으며, 또한 실제로 어떤 환경에서는, MySQL
은 이러한 커넥션 실패를 캐시하고 동일 IP에서 오는 향후의 커넥션에 대해서는 계속
거부를 하게 된다.
– 만일 순방향-DNS 매핑과 역방향-DNS 매핑이 일치하지 않게 되면, MySQL은 해당 커
넥션을 거부한다.
– 호스트 이름 캐시의 내용을 알아 볼 수 있는 방법은 없다. 사용 가능한 유일한 SQL 명
령어는 FLUSH HOSTS 인데, 이것은 전체 캐시를 단순히 깨끗이 비우는 기능만을 수행
한다.
– 만일 특정 클라이언트가 max_connect_errors 동안 커넥션이 실패하게 된다면, 그 클
라이언트는, FLUSH HOSTS를 실행하지 않는 한, 향후의 커넥션도 실패하게 된다.
– 만일 MySQL 서버에 libwrap (tcp wrappers)를 지원 하도록 활성화 시킨다면, 이러
한 설정은 종종 MySQL이 single-threaded accept()인 동안에 역방향-DNS 검색을 실
행 하도록 만드는데, 이렇게 되면 다른 모든 클라이언트의 커넥션이 블러킹 된다는 것
을 의미하는 것이다.

위의 사항을 정리하자면:
– MySQL의 안정성은 DNS의 안정성에 달려 있다. 즉, DNS의 설정 상태가 좋지 않을
경우에는, MySQL 서버도 그 만큼 안정하지 못하게 된다.
– 만일 네트워크가 안정하지 못할 경우, max_connect_errors가 빠른 시간 안에 10 (디
폴트 값) 에 도달하게 되고 동일 클라이언트의 향후 커넥션을 거부하게 된다.

따라서, 이러한 문제를 해결하기 위한 방법을 아래와 같이 제안 한다:

1. 절대로 외부에서 MySQL 서버에 커넥션을 하도록 만들지 말 것.
2. MySQL에서 libwrap를 지원하도록 활성화 시키지 말 것. 이것을 활성화 시키는 것
은 단지 문제를 더 어렵게 하는 것이다.
3. 여러분의 my.cnf 파일에서 skip_name_resolve를 활성화 시킬 것. 이렇게 하면, 모
든 호스트 이름에서 점(period)를 비활성화(disable) 시킨다. 모든 All GRANT는 반드
시 IP주소를 기반으로 되어 있어야 한다.
4. max_connect_errors를 매우 높게 설정한다: 예를 들면, 99999999. 이렇게 하면 네트
워크 또는 클라이언트의 문제로 인한 우발적인 커넥션 단절 문제를 피할 수가 있다.

위의 사용 팁을 활용해서 좋은 결과를 얻기를…….

1. 개요
Web Server와 Database가 분리되어 있는 환경에서 발생하는 문제점으로 이 문제를 해결하지 않고 그대로 놔 두게 되면 max_connections 값만큼 프로세스를 차지하게 되어 다음 연결부터는 too many connections 라는 문구를 보게 될 것이다.

2. 원인
mysql 서버의 경우 ip주소로 클라이언트 접속이 들어오게 되면 해당 ip에 대한 resolving을 수행하게 된다. 즉, 접속한 네가 누구냐? 라고 묻게되는 건데 문제는 이 질의에 대해 정상적으로 대답을 하게 되면 show processlist 상에 ip 부분이 이름으로 나오게 되지만 네트워크 환경이 느려 dns server의 대답이 늦어지게 된다면 그만큼 connection을 유지하고 있는 문제가 발생하게 된다.

3. 해결
mysql 구동시 –skip-name-resolve

혹은 my.cfg 파일에서
[mysqld]
…
skip-name-resolve
skip-host-cache

두 줄을 추가해 주고 데몬을 재시작 하면 된다.