메뉴 건너뛰기

조회 수 8854 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

테이블의 컬럼 수가 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;



List of Articles
번호 제목 날짜 조회 수
126 한글정렬 order by 2019.02.19 2424
125 하나의 쿼리로 여러 테이블의 데이터 삭제 file 2020.07.28 886
124 필드 변경, 추가, 삭제 2016.12.23 6085
123 특정 테이블만 replication 하거나 제외하는 방법 2018.07.18 2789
» 특정 COLUMN을 제외하고 SELECT/INSERT하는 방법 2017.11.16 8854
121 테이블, 컬럼 charset 변경 2021.03.26 186
120 테이블, 레코드 복사 2021.03.26 162
119 테이블 파티셔닝 2017.12.22 4940
118 테이블 타입(Heap, MyIsam, InnoDB...) 변경하기 2017.04.12 5272
117 테이블 체크, 백업 2016.12.23 7478
116 테이블 복사, 테이블 비우기 2016.12.23 5781
115 컬럼에 포함된 특정문자열을 검색해서 그 문자로 또 다른 테이블검색하기 2016.08.30 6606
114 컬럼내 특정 문자를 다른문자로 변경하고자 할때 2014.02.27 6012
113 중복데이터 하나만 빼고 삭제 2019.01.11 3161
112 전체 텍스트 검색과 파티션 file 2017.12.22 3868
111 전체 텍스트 검색과 파티션 file 2017.12.22 3941
110 일반적인 오류를 해결하는 유용한 정보들 file 2020.11.23 3840
109 이벤트 스케줄러 등록하기 file 2020.06.29 303
108 월의 마지막 일수를 반환하는 함수입니다 2015.12.19 5996
107 외부접근 권한 주기 file 2016.12.23 5949
Board Pagination Prev 1 2 3 4 5 6 7 Next
/ 7

하단 정보를 입력할 수 있습니다

© k2s0o1d4e0s2i1g5n. All Rights Reserved