평소에도 궁금했지만 자세히 찾아보지 못해 이제야 정리를 해봅니다.
테이블의 컬럼 수가 10개 내외라면 문제가 되지 않지만 많은 수의 컬럼을 지닌 테이블의 경우 유용합니다.
예를 들어 컬럼의 수가 30개인 테이블이 있는데 하나의 컬럼만 제외하고 나머지 29개를 셀렉트하고자 합니다. 이때 보통은 29개의 컬럼을 셀렉트 구문에 모두 입력합니다. 분명 방법이 있을텐데 검색을 해보면 방법이 없다라는 글 밖에 보이지 않더군요. 하지만 방법이 있습니다. (없을리가 없죠…)
일반적인 쿼리문으로는 어렵고 사용자 정의 변수(User-Defined Variable)과 몇가지 내장함수를 사용하는 것으로 해결 할 수 있습니다. 처음엔 어려워 보이더라도 하나하나 뜯어보면 간단합니다.
일단 구문은 이렇습니다.
-- @qry 라는 사용자 정의 변수에 구문 작성
SET @qry = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<제외할 컬럼명>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<대상 테이블명>' AND TABLE_SCHEMA = '<대상 데이터베이스명>'), ' FROM <대상 테이블명>');
-- 쿼리문이 올바르게 작성되었는지 확인
SELECT @qry;
-- Prepared statement 생성 (예제는 result 라는 이름으로 생성)
PREPARE result FROM @qry;
-- Prepared statement 실행
EXECUTE result;
-- 생성한 Prepared stetement 해제 (클라이언트 세션이 종료되어도 자동으로 해제 됩니다.)
DROP PREPARE result;
위 내용을 요약하자면 qry라는 변수에 쿼리 구문을 작성하고 result라는 이름으로 Prepared statement 를 저장해서 실행 시키고 해제하는 것입니다.
여기서 Prepared statement는 쿼리를 메모리에 저장해두었다가 사용하는 MS-SQL의 Stored Procedure (저장 프로시저)와 그 용도가 비슷합니다.
위 구문에 대해 하나씩 정리해봅니다.
1. SET @qry = ”;
=> qry라는 변수를 생성하는 구문입니다.
2. CONCAT(‘구문1’, ‘구문2’, ‘구문3’, …);
=> 여러개의 구문을 하나로 합치는 용도로 사용됩니다. 여기서는 일반 텍스트 구문과 함수/변수를 연결하는 용도로 사용합니다.
3. SELECT REPLACE(‘<대상 문구>’, ‘<찾을 단어>’, ‘<바꿀 단어>’);
=> REPLACE 함수는 특이점은 없습니다. 대상 문구에서 단어를 찾아 치환해주는 용도입니다. 앞에 SELECT는 결과값을 반환하기 위함입니다.
4. GROUP_CONCAT(컬럼명);
=> 여러 레코드로 출력되는 특정 컬럼의 값들을 하나의 스트링으로 합쳐서 반환합니다.
5. INFORMATION_SCHEMA.COLUMNS 테이블
=> INFORMATION_SCHEMA 테이블은 사용자가 생성한 테이블의 속성이 담겨져 있는 시스템 테이블입니다. 이 테이블을 통해 사용자가 생성한 테이블의 컬럼이나 속성 값을 조회할 수 있습니다. 여기서는 테이블의 컬럼명을 얻어오기 위해 사용합니다. WHERE 절에 테이블명과 데이터베이스명이 있는 이유는 스키마 테이블에 모든 사용자 데이터베이스와 테이블 속성 정보가 레코드 단위로 저장되어있기 때문입니다.
6. 전체 구문 정리
=> INFORMATION_SCHEMA 테이블에서 조회하고자 하는 테이블의 컬럼정보를 GROUP_CONCAT 함수로 한줄의 텍스트로 만듭니다. (컬럼1,컬럼2,컬럼3,..)
이것을 REPLACE를 통해 제외하고자 하는 컬럼을 제거합니다. (쉼표와 싱글쿼트 표기에 유의하세요.)
예) REPLACE(GROUP_CONCAT(COLUMN_NAME), ‘컬럼1,’, ”);
그리고 마지막으로 앞 뒤에 셀렉트 구문을 추가하여 하나의 셀렉트 쿼리문을 완성 시킵니다.
완성이 되었다면 SELECT @qry;를 통해 구문을 확인합니다. 구문오류가 없는지 확인을 위함입니다. 조회된 셀렉트 구문을 그대로 복사하여 사용해도 됩니다만.. 복사, 붙여넣고 실행.. 귀찮습니다..
PREPARE result FROM @qry; 구문으로 Prepared statement를 생성하고 EXECUTE result;로 실행시킵니다. 원하는 결과 내용이 조회됩니다. 그리고 생성된 Prepared statement는 DROP Prepare result;로 메모리에서 해제시킵니다. 클라이언트 세션이 종료되면 같이 해제되지만 그 동안은 계속 메모리를 잡고 있기 때문에 그때 그때 해제하는 것을 권장합니다.
인서트문으로 활용
응용해서 INSERT문에도 적용가능합니다. 대표적인 용도로는 구조가 비슷한 A테이블과 B테이블이 있다는 가정에서 A테이블에 B테이블의 데이터를 주입시키는 경우일 겁니다. 자주 있는 경우는 아니겠지만 데이터 마이그레이션 작업 시 유용합니다.
여기서는 변수가 2개가 필요합니다. 1개도 가능하지만 너무 길어지고 반복되는 구문이 생깁니다.
-- INSERT 할 컬럼 생성
SET @col = (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<제외할 컬럼명>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<대상 테이블명>' AND TABLE_SCHEMA = '<대상 데이터베이스명>');
-- INSERT 쿼리 생성
SET @qry = CONCAT('INSERT INTO <대상 테이블명> (', @col ,') SELECT ', @col, ' from <주입할 데이터가 있는 테이블명>');
-- Prepared statement 생성
PREPARE result FROM @qry;
-- Prepared statement 실행
EXECUTE result;
-- Prepared statement 해제
DROP PREPARE result;
적다보니 제법 많은 양을 정리했네요. 이 방법은 테이블에 컬럼의 수가 많으면 많을 수록 유용할 겁니다. 그리고 사용자 정의 변수(User-defined variable)는 동적 쿼리를 작성을 가능하게 하므로 재사용성을 높일 수 있는 쿼리문을 작성할 수 있습니다.