[MySQL]information_schema란 ? (정의 및 테이블 종류)

information_schema란 ?

INFORMATION_SCHEMA란 MySQL 서버 내에 존재하는 DB의 메타 정보(테이블, 칼럼, 인덱스 등의 스키마 정보)를 모아둔 DB다. INFORMATION_SCHEMA 데이터베이스 내의 모든 테이블은 읽기 전용이며, 단순히 조회만 가능하다. 즉, 읽기전용(Read-only)으로 사용자가 직접 수정하거나 관여할 수는 없다.

 

실제로 이 데이터베이스 내의 테이블은 레코드가 있는 것이 아니라, SQL을 이용해 조회할 때마다 메타 정보를 MySQL 서버의 메모리에서 가져와서 보여준다. 이런 이유로 한꺼번에 많은 테이블을 조회할 경우 시간이 많이 걸릴 수도 있다.

 

* 메타 데이터(Metadata) 란 데이터의 데이터로서, 데이터베이스 또는 테이블의 이름, 컬럼의 데이터 타입, 또는 접근 권한과 같은 것을 말한다. 경우에 따라서 데이터 사전(data dictionary) 과 시스템 카탈로그(system catalog)로 불린다.

information_schema DB 테이블 종류

show tables; 를 실행시키면 아~주 많은 information_schmea의 테이블이 나온다.

그 중에서 개인적으로 생각할 때 많이 사용될 만한 것들만 추렸다.

Tables_in_information_schema
COLUMNS	모든 스키마의 컬럼 확인
ENGINES	사용되는 엔진 확인
EVENTS	생성된 Event(스케줄) 확인
KEY_COLUMN_USAGE	사용된 키 컬럼 확인
PROCESSLIST	수행 중인 프로세스 리스트
SCHEMATA	생성된 스키마 확인
SCHEMA_PRIVILEGES	스키마 권한 확인
TABLES	생성된 모든 테이블 정보
TABLE_PRIVILEGES	테이블 권한 확인
TRIGGERS	생성된 트리거 확인 가능
USER_PRIVILEGES	사용자 권한 정보
VIEWS	생성된 뷰 정보

 

활용 방법

1) test 스키마에서 id가 들어있는 컬럼을 찾아주세요.

SELECT * FROM information_schema.`COLUMNS`
WHERE information_schema.`COLUMNS`.TABLE_SCHEMA = 'test'
AND information_schema.`COLUMNS`.COLUMN_NAME LIKE '%id%';

 

2) test 스키마의 테이블 목록 뽑아주세요.

SELECT * FROM information_schema.`TABLES`
WHERE information_schema.`TABLES`.TABLE_SCHEMA = 'test';

 

information_schema 테이블이 어떤 특성을 가진지 알면 자유롭게 사용할 수 있을 것 같네요

왕초보자들을 위해 팁이라고 한다면 heidiSQL에서 select * from information_schema 까지 입력하고 ctrl + space 를 누르면 테이블 목록이 쭉 떠요. 거기서 원하는 것 선택해서 사용하면 쉽습니다.