테이블을 만들 때 COMMENT 를 꼼꼼히 달아 두면, 나중에 그 설명만 뽑아서 테이블 정의서를 만들거나 프로그램 화면의 컬럼 제목으로 쓸 수 있습니다. 코멘트는 MySQL이 information_schema 라는 시스템 DB에 같이 보관하기 때문에 일반 SELECT 로 꺼낼 수 있습니다.

테이블 코멘트

테이블 목록과 설명은 information_schema.TABLES 에 있습니다. 처음엔 조건을 어떻게 줘야 할지 몰라서 WHERE 1 로 전부 가져왔습니다.

광고
SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE 1;

이렇게 하면 지금 접속한 DB만이 아니라 서버에 있는 모든 DB의 테이블이 다 나옵니다. mysql, information_schema 같은 시스템 테이블까지 섞여서 수백 줄이 나오기도 하죠. 원하는 DB만 보려면 TABLE_SCHEMA 로 거르면 됩니다. 아래는 이 부분을 추가한 쿼리입니다.

SELECT TABLE_NAME, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

DATABASE() 는 현재 USE 중인 DB 이름을 돌려주니, DB 이름을 직접 적고 싶으면 'mydb' 처럼 문자열로 바꿔 넣으면 됩니다.

컬럼 코멘트

컬럼 설명은 information_schema.COLUMNS 에 있습니다. 특정 테이블(tinventory)의 컬럼명과 코멘트를 가져오는 쿼리입니다.

SELECT COLUMN_NAME, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE `TABLE_NAME` = 'tinventory';

여기도 같은 주의점이 있습니다. 다른 DB에 같은 이름의 테이블이 있으면 그 컬럼까지 같이 나옵니다. 개발 DB와 운영 미러 DB를 한 서버에 두고 쓰는 경우에 흔히 겪는 일입니다. AND TABLE_SCHEMA = DATABASE() 를 붙이고, 컬럼 순서를 테이블 정의 순으로 맞추려면 ORDER BY ORDINAL_POSITION 을 같이 쓰는 게 좋습니다.

SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tinventory'
ORDER BY ORDINAL_POSITION;

이걸 어디에 쓰나

제가 주로 쓰는 곳은 두 가지입니다. 하나는 테이블 정의서 만들기입니다. 위 쿼리 결과를 엑셀로 붙여 넣으면 컬럼명, 타입, 설명이 정리된 표가 바로 나옵니다. 다른 하나는 관리 화면의 그리드 제목입니다. VB.NET이나 PHP에서 조회 결과를 표로 보여 줄 때 컬럼명 대신 코멘트를 헤더로 쓰면, 화면마다 제목을 따로 하드코딩할 필요가 없습니다.

코멘트가 비어 있는 컬럼은 COLUMN_COMMENT 가 빈 문자열로 나오니, 이런 용도로 쓰려면 테이블을 만들 때부터 설명을 다는 습관이 필요합니다. 빠진 코멘트는 ALTER TABLE ... MODIFY COLUMN 으로 컬럼 정의를 다시 적으면서 COMMENT '설명' 을 붙여 추가할 수 있는데, 이때 타입과 NULL 여부, 기본값까지 원래대로 다 적어야 정의가 바뀌지 않는다는 점을 기억해 두세요. 간단히 한 테이블만 확인할 때는 SHOW FULL COLUMNS FROM tinventory; 도 Comment 열을 보여 줍니다.

테이블 코멘트는 컬럼보다 고치기가 쉽습니다. 컬럼 정의를 다시 적을 필요 없이 테이블 옵션만 바꾸면 됩니다.

ALTER TABLE tinventory COMMENT = '재고 현황';

테이블 정의서를 한 번에 뽑는 쿼리

테이블마다 쿼리를 따로 돌리기 귀찮다면 TABLES 와 COLUMNS 를 조인해서 DB 전체를 한 번에 뽑으면 됩니다. 결과를 그대로 엑셀에 붙여 넣으면 테이블별로 묶인 정의서가 나옵니다.

SELECT t.TABLE_NAME, t.TABLE_COMMENT,
       c.COLUMN_NAME, c.COLUMN_TYPE, c.IS_NULLABLE, c.COLUMN_DEFAULT, c.COLUMN_KEY, c.COLUMN_COMMENT
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;

TABLE_TYPE = 'BASE TABLE' 조건은 뷰를 빼기 위한 것입니다. 뷰도 TABLES 에 같이 들어 있고, 뷰의 TABLE_COMMENT 에는 설명 대신 VIEW 라는 글자가 들어 있어서 정의서에 섞이면 보기 싫습니다. COLUMN_KEY 는 기본키면 PRI, 유니크면 UNI, 일반 인덱스의 첫 컬럼이면 MUL 로 나오니 키 표시 칸으로 쓰면 됩니다.

한글 코멘트가 깨져 보일 때

조회 결과에서 한글 코멘트만 ??? 로 보인다면 대부분 접속 문자셋 문제입니다. 테이블을 만들 때 쓴 클라이언트와 지금 조회하는 클라이언트의 연결 문자셋이 다르면 이렇게 됩니다. 접속 직후 SET NAMES utf8mb4; 를 실행하거나, 프로그램의 연결 문자열에 문자셋을 지정한 다음 다시 조회해 보세요. 처음 넣을 때 이미 깨진 채로 저장된 경우라면 조회 쪽을 아무리 고쳐도 안 돌아오니, 그때는 코멘트를 다시 달아야 합니다.

information_schema 는 접속한 계정이 권한을 가진 객체만 보여 준다는 점도 알아 두면 좋습니다. 운영 DB에서 일부 테이블이 결과에 안 나온다면 쿼리보다 계정 권한을 먼저 확인할 일입니다.