MySQL INFORMATION_SCHEMA

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

INFORMATION_SCHEMA는 표준 SQL 쿼리를 통해 MySQL 서버 메타데이터를 노출하는 가상 데이터베이스입니다. SHOW TABLES, SHOW COLUMNS, DESCRIBE가 알려줄 수 있는 모든 것을 INFORMATION_SCHEMA도 알려줄 수 있습니다 — 그것도 WHERE, JOIN, 집계의 모든 기능과 함께.

테이블과 열

데이터베이스의 모든 테이블 나열하기:

SELECT table_name, table_type, engine, table_rows, create_time
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY table_name;

table_rows는 InnoDB 테이블의 경우 추정치(인덱스 통계에서 파생)이며 정확한 개수가 아닙니다. 정밀한 값에는 SELECT COUNT(*)를 사용하세요. 테이블의 모든 열 나열하기:

SELECT column_name, column_type, is_nullable, column_default, extra
FROM information_schema.columns
WHERE table_schema = 'myapp'
  AND table_name = 'users'
ORDER BY ordinal_position;

모든 테이블에서 특정 타입의 모든 열 찾기:

SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'myapp'
  AND data_type = 'json';

인덱스

테이블의 모든 인덱스 나열하기:

SELECT index_name, non_unique, seq_in_index, column_name, index_type
FROM information_schema.statistics
WHERE table_schema = 'myapp'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

기본 키가 없는 테이블 찾기:

SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'myapp'
  AND t.table_type = 'BASE TABLE'
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.table_constraints tc
    WHERE tc.table_schema = t.table_schema
      AND tc.table_name = t.table_name
      AND tc.constraint_type = 'PRIMARY KEY'
  );

제약 조건

모든 제약 조건(PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) 나열하기:

SELECT constraint_name, constraint_type, table_name
FROM information_schema.table_constraints
WHERE table_schema = 'myapp'
ORDER BY table_name, constraint_type;

모든 외래 키와 그것이 참조하는 대상 나열하기:

SELECT
    kcu.table_name,
    kcu.column_name,
    kcu.constraint_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.key_column_usage kcu
JOIN information_schema.referential_constraints rc
    ON rc.constraint_schema = kcu.constraint_schema
    AND rc.constraint_name = kcu.constraint_name
WHERE kcu.table_schema = 'myapp'
  AND kcu.referenced_table_name IS NOT NULL
ORDER BY kcu.table_name;

CHECK 제약 조건 나열하기:

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'myapp'
ORDER BY constraint_name;

저장 루틴

모든 저장 프로시저와 함수 나열하기:

SELECT routine_name, routine_type, created, last_altered
FROM information_schema.routines
WHERE routine_schema = 'myapp'
ORDER BY routine_type, routine_name;

루틴의 정의 보기:

SELECT routine_definition
FROM information_schema.routines
WHERE routine_schema = 'myapp'
  AND routine_name = 'get_active_users'
  AND routine_type = 'PROCEDURE'\G

뷰와 트리거

모든 뷰 나열하기:

SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'myapp'\G

모든 트리거 나열하기:

SELECT trigger_name, event_manipulation, event_object_table,
       action_timing, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'myapp'
ORDER BY event_object_table, action_timing;

권한

현재 사용자의 유효 권한 보기:

SELECT * FROM information_schema.user_privileges;
SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;

주요 테이블 참조

테이블	포함 내용
`TABLES`	엔진, 행 수 추정치, 크기를 포함한 모든 테이블과 뷰
`COLUMNS`	타입, 기본값, 널 허용 여부를 포함한 모든 열
`STATISTICS`	인덱스 정의와 카디널리티
`TABLE_CONSTRAINTS`	모든 제약 조건(PK, UK, FK, CHECK)
`KEY_COLUMN_USAGE`	FK 대상 세부 정보를 포함한 제약 조건의 열
`REFERENTIAL_CONSTRAINTS`	FK 캐스케이드/제한 규칙
`CHECK_CONSTRAINTS`	CHECK 제약 조건 표현식
`ROUTINES`	저장 프로시저와 함수
`TRIGGERS`	트리거 정의
`VIEWS`	뷰 정의
`CHARACTER_SETS`	사용 가능한 문자 집합
`COLLATIONS`	사용 가능한 콜레이션
`SCHEMATA`	현재 사용자에게 보이는 모든 데이터베이스

INFORMATION_SCHEMA와 SHOW 명령 비교

SHOW 명령은 축약형이며, 대개 INFORMATION_SCHEMA에 대응물이 있습니다:

SHOW 명령	INFORMATION_SCHEMA 대응물
`SHOW TABLES`	`SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()`
`SHOW COLUMNS FROM t`	`SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 't'`
`SHOW INDEX FROM t`	`SELECT * FROM information_schema.statistics WHERE table_name = 't'`
`SHOW CREATE TABLE t`	직접적인 대응물 없음 — `SHOW CREATE TABLE`이 여전히 가장 쉬운 방법
`SHOW PROCEDURE STATUS`	`SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE'`

필터링, 조인, 집계가 필요할 때는 INFORMATION_SCHEMA 쿼리가 더 낫습니다. 대화형 검사에는 SHOW 명령으로 충분합니다.

자주 묻는 질문

INFORMATION_SCHEMA 쿼리는 느린가요?

InnoDB 테이블의 경우 일부 쿼리(특히 TABLES와 STATISTICS에 대한 쿼리)는 인덱스 통계 갱신을 유발하며, 큰 스키마에서는 느릴 수 있습니다. 메타데이터를 자주 조회한다면, 캐시된 통계에 더 빠르게 접근할 수 있는 mysql.innodb_table_stats와 mysql.innodb_index_stats를 사용하세요.

table_rows 값이 왜 잘못되어 보이나요?

information_schema.tables.table_rows는 InnoDB 인덱스 통계에서 나온 추정치이며 실시간 개수가 아닙니다. 큰 테이블에서는 40-50% 차이가 날 수 있습니다. 통계를 새로 고치려면 ANALYZE TABLE tablename을 실행하거나, 정확한 개수에는 SELECT COUNT(*)를 사용하세요.

문제 해결

문제	해결 방법
알려진 테이블에 대해 쿼리가 행을 반환하지 않음	현재 사용자에게 그 테이블에 대한 `SELECT` 권한이 없을 수 있습니다 — `INFORMATION_SCHEMA`는 사용자가 볼 수 있는 테이블로 결과를 필터링합니다
`table_rows`가 COUNT(*)와 크게 다름	InnoDB 행 수는 추정치입니다 — `ANALYZE TABLE`을 실행하여 인덱스 통계를 새로 고치세요
이름으로 제약 조건을 찾을 수 없음	제약 조건 이름은 데이터베이스 범위입니다 — WHERE 절에서 `constraint_schema`를 확인하세요
`INFORMATION_SCHEMA.TABLES`에 대한 쿼리가 느림	InnoDB는 접근 시 통계를 새로 고칩니다. 결과를 더 오래 캐시하려면 `information_schema_stats_expiry`(기본값 86400초)를 사용하세요

참고

MySQL 사용자 관리 — 권한 부여 감사를 위한 INFORMATION_SCHEMA.USER_PRIVILEGES
MySQL의 스키마 마이그레이션 — 마이그레이션 전에 스키마를 검사하기 위한 INFORMATION_SCHEMA 조회
MySQL 인덱스의 동작 방식 — 찾아낸 인덱스 메타데이터에 따라 조치하기

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성