[MySQL/MariaDB] 테이블의 컬럼 정보만을 추출하고 파일로 출력하기

 

데이터베이스에서 필요에 의해 어떤 테이블를 구성하고 있는 컬럼 정보만을 추출해야할 때가 있다.
루트 권한으로 접속하여 다음에 설명할 쿼리를 실행하는 것으로 이를 해결할 수 있다.
또, 실행한 쿼리를 파일로 저장하는 것도 가능하다.

1. 컬럼 정보 확인 쿼리

SELECT  
    -- 필요한 컬럼을 지정
    TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE … … 
FROM  
    information_schema.columns  
WHERE  
    table_schema = '데이터베이스명(스키마명)'
    AND 
    TABLE_NAME = '테이블명' 
;

💡 information_schema.columns
MySQL 혹은 MariaDB에 루트 권한 유저로 접속해보면 "information_schema"라는 데이터베이스가 존재한다.
그 곳의 columns라는 테이블에는, 구축되어 있는 모든 데이터베이스(스키마)들과 그에 속한 컬럼 정보들이 있다.
여기서 필요한 컬럼 정보들을 SELECT 쪽에 지정하여 원하는 데이터를 추출하는 것이 가능하다.

colums 테이블을 통해 우리가 알 수 있는 정보들은 다음과 같다. 

대강의 컬럼명만 보더라도 무엇을 의미하는지 알 수 있다.
이들 중 얻고자 하는 데이터에 대해 SELECT 대상 열로 지정하면 된다.

TABLE_CATALOG	테이블이 속한 카탈로그의 이름 값은 항상 def
TABLE_SCHEMA	데이터베이스명 (스키마명)
TABLE_NAME	테이블명
COLUMN_NAME	컬럼명
ORDINAL_POSITION	테이블에 지정된 컬럼 위치(순서)
COLUMN_DEFAULT	컬럼 기본값
IS_NULLABLE	NULL 허용 여부
DATA_TYPE	데이터 형식
CHARACTER_MAXIMUM_LENGTH	최대 길이
CHARACTER_OCTET_LENGTH	최대 길이 (멀티 바이트 문자열을 제외하고 CHARACTER_MAXIMUM_LENGTH와 같다)
NUMERIC_PRECISION	numeric 데이터에 대한 정밀도(유효 자릿수)  numeric 형식이 아니라면 NULL
NUMERIC_SCALE	numeric 데이터에 대한 정밀도(소수점 아래 유효 자릿수)  numeric 형식이 아니라면 NULL
DATETIME_PRECISION	정밀한 초단위 데이터, time data 형식이 아니라면 NULL
CHARACTER_SET_NAME	charset 인코딩 형식
COLLATION_NAME	collation 인코딩 형식
COLUMN_TYPE	컬럼 형식
COLUMN_KEY	컬럼 키 여부 (PRI: primary key, UNI: unique index, MUL: multiple index)
EXTRA	컬럼 추가 정보 (ex: auto_increment가 지정되었는지 판단)
PRIVILEGES	부여된 권한
COLUMN_COMMENT	컬럼 코멘트

 

 

2. 파일 출력 쿼리 추가 

SELECT  
    -- 필요한 컬럼을 지정
    TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE … … 
FROM  
    information_schema.columns  
WHERE  
    table_schema = '데이터베이스명(스키마명)'  
    AND 
    TABLE_NAME = '테이블명' 

-- 파일로 출력하기
INTO OUTFILE 
    'C:/출력할 경로와 파일명.csv'

-- 파일 출력 시 옵션
FIELDS  
    ENCLOSED BY '"' 
    TERMINATED BY ',' 
    ESCAPED BY '\\' 
LINES  
    TERMINATED BY '\r\n' 
;

💡 INTO OUTFILE '(경로 지정)'
SELECT 쿼리의 결과를 파일로 내보내기
이하 옵션 없이 여기까지만 실행하면 TAB으로 값들이 구분되어 파일로 출력된다.

💡 FIELDS 이후 

ENCLOSED BY '"'  값 하나당 구분할 문자.   예를 들어, ENCLOSED BY '"'(따옴표)라면 "ABC"처럼 따옴표로 값을 구분하여 출력한다.

(optional) TERMINATED BY ','  기본적으로 컬럼 단위를 TAB으로 구분하지만  특정 문자를 지정하여 구분할 수 있다. 필수 지정 구문은 아님.

ESCAPED BY '\\'  빈 데이터(NULL)의 출력방식을 결정한다. 아래 내용들을 고려하여 적절하게 처리해야 한다.  (이 부분은 구글링을 해도 설명된 자료 양이 무척 부족해서 직접 이것저것 대입해보고서야 조금 이해했다.)  - 아무 것도 지정하지 않으면 NULL 데이터는 NULL로 출력된다.  - 지정은 한 글자만 가능해 보인다. (2문자 이상 지정하면 쿼리 실행 에러 발생)  - 지정한 문자열 + N 형식으로 NULL 데이터를 표기한다.     예를 들어 ESCAPED BY '\\'의 경우 출력된 파일을 확인하면 \N으로 출력되고, ESCAPED BY 'A'의 경우 AN으로 출력된다.  - ESCAPED BY 구 자체를 작성하지 않으면 NULL 데이터는 기본적으로 \N으로 출력된다.

💡 LINES 
TERMINATED BY '\r\n'
행 구분자

 

 

<참고한 페이지>

https://mariadb.com/kb/en/information-schema-columns-table/

Information Schema COLUMNS Table