메뉴 건너뛰기

조회 수 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
번호 제목 날짜 조회 수
106 LIMIT 속도 저하 2021.03.26 246
105 fulltext 관련 글 2021.03.26 125
104 order by field 와 union, 원하는 대로 정렬 하기 2021.03.26 768
103 테이블, 컬럼 charset 변경 2021.03.26 186
102 no exists, not in 을 이용한 조건에 만족하지 않는 것들 구하기, 둘의 차이점 2021.03.26 125
101 group by로 뽑아온 값중에 가장큰 값(max)의 상태값을 가져오기 2021.03.26 593
100 group by, distinct, count 를 이용한 겹치지 않는것의 개수 2021.03.26 262
99 mysqlbinlog (mysql 백업, 로그남기기) 2021.03.26 196
98 mysql 포트 바꾸기, 외부에서 접속하기 2021.03.26 5070
97 테이블, 레코드 복사 2021.03.26 162
96 BEGIN, COMMIT, ROLLBACK 2021.03.26 315
95 column의 정보 중에서 column 설명(column_comment) 2021.03.26 129
94 [mysql,mariaDB] 컬럼 암호화, 복호화 2020.11.25 766
93 MySQL 암호화 방법 2020.11.25 360
92 일반적인 오류를 해결하는 유용한 정보들 file 2020.11.23 3840
91 서로 다른 결과를 한줄로 합쳐서 보여줘야 할 때(GROUP_CONCAT) file 2020.07.28 742
90 엑셀로 되어있는 부분 db로 import하는 방법 2020.07.28 144
89 하나의 쿼리로 여러 테이블의 데이터 삭제 file 2020.07.28 887
88 UPDATE ~ CASE 문을 사용한 조건별 업데이트 file 2020.07.28 1387
87 이벤트 스케줄러 등록하기 file 2020.06.29 303
Board Pagination Prev 1 2 3 4 5 6 7 Next
/ 7

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

© k2s0o1d4e0s2i1g5n. All Rights Reserved