1.INFORMATION_SCHEMA 란?
-
MySQL 서버 내에 존재하는 DB의 메타 정보(테이블, 칼럼, 인덱스 등의 스키마 정보)를 모아둔 DB
-
시스템 카탈로그라고도 함
2. INFORMATION_SCHEMA DB 테이블 종류
- COLUMNS : 모든 스키마의 컬럼 확인
- ENGINES : 사용되는 엔진 확인
- EVENTS : 생성된 Event(스케줄) 확인
- KEY_COLUMN_USAGE : 사용된 키 컬럼 확인
- PROCESSLIST : 수행 중인 프로세스 리스트
- SCHEMATA : 생성된 스키마 확인
- SCHEMA_PRIVILEGES : 스키마 권한 확인
- TABLES : 생성된 모든 테이블 정보
- TABLE_PRIVILEGES : 테이블 권한 확인
- TRIGGERS : 생성된 트리거 확인 가능
- USER_PRIVILEGES : 사용자 권한 정보
- VIEWS : 생성된 뷰 정보
3.INFORMATION_SCHEMA.TABLES
- TABLE_CATALOG : 테이블이 속한 카탈로그의 이름
- TABLE_SCHEMA : 테이블이 속한 스키마(데이터베이스)의 이름
- TABLE_NAME : 테이블의 이름
- TABLE_TYPE : BASE TABLE 의 테이블, VIEW의 뷰 또는 INFORMATION_SCHEMA 테이블에 대한 SYSTEM VIEW
- ENGINE : 테이블의 스토리지 엔진
- VERSION: MySQL 5.7에서 사용된 마지막 파일 버전
- ROW_FORMAT : 행 저장 형식( Fixed, Dynamic, Compressed, Redundant, Compact)
- TABLE_ROWS : 행 수 (INFORMATION_SCHEMA 테이블의 경우 TABLE_ROWS는 NULL)
- AVG_ROW_LENGTH : 평균 행 길이
- DATA_LENGTH : 데이터 파일의 길이(바이트) -MyISAM
- MAX_DATA_LENGTH : 데이터 파일의 최대 길이 -MyISAM
- INDEX_LENGTH : 인덱스 파일의 길이(바이트) - MyISAM
- DATA_FREE : 할당되었지만 사용되지 않은 바이트 수
- AUTO_INCREMENT : 다음 AUTO_INCREMENT값
- CREATE_TIME : 테이블이 생성된 시간
- UPDATE_TIME : 테이블이 마지막으로 업데이트된 날짜
- CHECK_TIME : 테이블을 마지막으로 확인한 시간
- TABLE_COLLATION : 테이블 기본 데이터 정렬
- CHECKSUM : 실시간 체크합 값(있는 경우)
- CREATE_OPTIONS : CREATE TABLE과 함께 사용되는 추가 옵션
- TABLE_COMMENT : 테이블을 만들 때 사용되는 주석
4. INFORMATION_SCHEMA.COLUMNS
- TABLE_CATALOG : 열이 포함된 테이블이 속한 카탈로그의 이름
- TABLE_SCHEMA : 해당 컬럼이 속한 테이블이 속한 스키마(데이터베이스)의 이름
- TABLE_NAME : 열이 포함된 테이블의 이름
- COLUMN_NAME : 열의 이름
- ORDINAL_POSITION : 테이블 내 열의 위치
- COLUMN_DEFAULT : 열의 기본값
- IS_NULLABLE : 열 Null 허용 여부
- DATA_TYPE : 열 데이터 유형
- CHARACTER_MAXIMUM_LENGTH : 문자열 열의 경우 최대 문자 길이
- CHARACTER_OCTET_LENGTH : 문자열 열의 경우 최대 길이(바이트)
- NUMERIC_PRECISION : 숫자 열의 경우 숫자 정밀도
- NUMERIC_SCALE : 숫자 열의 경우 숫자 척도
- DATETIME_PRECISION : 시간 열의 경우 분수 초 정밀도입니다.
- CHARACTER_SET_NAME : 문자열 컬럼의 경우 문자 집합 이름
- COLLATION_NAME: 문자열 열의 경우 데이터 정렬 이름
- COLUMN_TYPE : 열 데이터 유형
- COLUMN_KEY : 열의 색인이 생성되었는지 여부
- EXTRA : 특정 열에 대해 사용할 수 있는 추가 정보
- PRIVILEGES : 해당 열에 대해 가지고 있는 권한
- COLUMN_COMMENT : 열 정의에 포함된 모든 설명
- GENERATION_EXPRESSION : 생성된 열의 경우 열 값을 계산하는 데 사용되는 표현식을 표시
- SRS_ID : 이 값은 공간 열에 적용됨
5. 개인적으로 자주 쓰는 쿼리
1) 동작중인 쿼리 확인
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'
AND USER <>'event_scheduler'
order byTIMEdesc;
- 동작중인 쿼리 사용중지 ( KILL *** )
2) 테이블 정보 확인
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='DB'
AND TABLE_NAME like 'tabel%'
3) 컬럼코멘트 확인
SELECT COLUMN_NAME,COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='DB
AND TABLE_NAME = 'TABLE'
ORDER BY ORDINAL_POSITION;
4)프로시져 내 사용여부 확인
SELECT ROUTINE_SCHEMA , ROUTINE_NAME , ROUTINE_DEFINITION, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA ='DB
AND ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%검색문구%'
- 참고자료
