데브러너 (DevRunner)

28.1 Introduction

INFORMATION_SCHEMA는 데이터베이스 메타데이터, 즉 데이터베이스나 테이블의 이름, 컬럼의 데이터 타입, 액세스 권한과 같은 MySQL 서버에 대한 정보에 접근할 수 있게 해 줍니다. 이 정보에 대해 때때로 사용되는 다른 용어로는 데이터 딕셔너리와 시스템 카탈로그가 있습니다.

INFORMATION_SCHEMA Usage Notes

INFORMATION_SCHEMA는 각 MySQL 인스턴스 내에 존재하는 데이터베이스로, MySQL 서버가 유지 관리하는 다른 모든 데이터베이스에 대한 정보를 저장하는 위치입니다. INFORMATION_SCHEMA 데이터베이스에는 여러 개의 읽기 전용 테이블이 포함되어 있습니다. 이들은 실제 베이스 테이블이 아니라 뷰이므로, 이들과 연관된 파일은 존재하지 않으며, 이들에 트리거를 설정할 수 없습니다. 또한, 이 이름을 가진 데이터베이스 디렉터리도 존재하지 않습니다.

USE 문을 사용하여 INFORMATION_SCHEMA를 기본 데이터베이스로 선택할 수는 있지만, 테이블의 내용을 읽기만 할 수 있고, 이들에 대해 INSERT, UPDATE, 또는 DELETE 작업을 수행할 수는 없습니다.

다음은 INFORMATION_SCHEMA에서 정보를 검색하는 문장의 예입니다:

1mysql> SELECT table_name, table_type, engine
2       FROM information_schema.tables
3       WHERE table_schema = 'db5'
4       ORDER BY table_name;
5+------------+------------+--------+
6| table_name | table_type | engine |
7+------------+------------+--------+
8| fk         | BASE TABLE | InnoDB |
9| fk2        | BASE TABLE | InnoDB |
10| goto       | BASE TABLE | MyISAM |
11| into       | BASE TABLE | MyISAM |
12| k          | BASE TABLE | MyISAM |
13| kurs       | BASE TABLE | MyISAM |
14| loop       | BASE TABLE | MyISAM |
15| pk         | BASE TABLE | InnoDB |
16| t          | BASE TABLE | MyISAM |
17| t2         | BASE TABLE | MyISAM |
18| t3         | BASE TABLE | MyISAM |
19| t7         | BASE TABLE | MyISAM |
20| tables     | BASE TABLE | MyISAM |
21| v          | VIEW       | NULL   |
22| v2         | VIEW       | NULL   |
23| v3         | VIEW       | NULL   |
24| v56        | VIEW       | NULL   |
25+------------+------------+--------+
2617 rows in set (0.01 sec)

설명: 이 문장은 데이터베이스 db5의 모든 테이블 목록을 요청하며, 테이블 이름, 타입, 스토리지 엔진이라는 세 가지 정보만을 보여 줍니다.

generated invisible primary key에 대한 정보는 기본적으로, 테이블 컬럼, 키, 또는 둘 다를 기술하는 모든 INFORMATION_SCHEMA 테이블에서 보입니다. 예를 들어 COLUMNS 테이블과 STATISTICS 테이블이 그러합니다. 이들 테이블에서 select하는 쿼리로부터 이러한 정보를 숨기고 싶다면, show_gipk_in_create_table_and_information_schema 서버 시스템 변수 값을 OFF로 설정하면 됩니다. 보다 자세한 내용은 Section 15.1.24.11, “Generated Invisible Primary Keys”를 참조하십시오.

Character Set Considerations

문자 컬럼의 정의(예를 들어, TABLES.TABLE_NAME)는 일반적으로 VARCHAR(N) CHARACTER SET utf8mb3이며, 여기서 _N_은 최소 64입니다. MySQL은 이 문자 집합에 대한 기본 콜레이션(utf8mb3_general_ci)을 사용하여 이러한 컬럼에 대한 모든 검색, 정렬, 비교 및 기타 문자열 작업을 수행합니다.

일부 MySQL 객체는 파일로 표현되므로, INFORMATION_SCHEMA 문자열 컬럼에서의 검색는 파일 시스템의 대소문자 구분 여부에 영향을 받을 수 있습니다. 보다 자세한 내용은 Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”를 참조하십시오.

INFORMATION_SCHEMA as Alternative to SHOW Statements

SELECT ... FROM INFORMATION_SCHEMA 문장은 MySQL이 지원하는 다양한 SHOW 문이 제공하는 정보에 접근하기 위한 보다 일관된 방법을 제공하기 위해 고안되었습니다( SHOW DATABASES, SHOW TABLES 등). SHOW와 비교했을 때, SELECT를 사용하면 다음과 같은 장점이 있습니다:

모든 액세스가 테이블을 통해 수행되므로 Codd의 규칙을 준수합니다.
익숙한 SELECT 문 구문을 사용할 수 있으며, 일부 테이블 및 컬럼 이름만 익히면 됩니다.
구현자는 키워드를 추가하는 것에 대해 걱정할 필요가 없습니다.
INFORMATION_SCHEMA 쿼리의 결과를 필터링, 정렬, 연결 및 변환하여, 데이터 구조나 파싱할 텍스트 표현과 같이 애플리케이션이 필요로 하는 어떤 형식으로든 변환할 수 있습니다.
이 기법은 다른 데이터베이스 시스템과의 상호 운용성이 더 우수합니다. 예를 들어, Oracle Database 사용자는 Oracle 데이터 딕셔너리의 테이블을 쿼리하는 것에 익숙합니다.

SHOW는 익숙하고 널리 사용되므로, SHOW 문은 대안으로 그대로 남아 있습니다. 실제로 INFORMATION_SCHEMA의 구현과 함께, SHOW에 대한 확장이 추가되었으며, 이는 Section 28.8, “Extensions to SHOW Statements”에 설명되어 있습니다.

INFORMATION_SCHEMA and Privileges

대부분의 INFORMATION_SCHEMA 테이블에 대해서는, 각 MySQL 사용자가 이들에 접근할 수 있는 권한을 가지지만, 사용자는 자신이 적절한 액세스 권한을 가진 객체에 해당하는 행만 볼 수 있습니다. 일부 경우(예를 들어, INFORMATION_SCHEMA ROUTINES 테이블의 ROUTINE_DEFINITION 컬럼)의 경우, 권한이 부족한 사용자는 NULL을 보게 됩니다. 일부 테이블은 서로 다른 권한 요구 사항을 가지며, 이러한 경우 요구 사항은 해당 테이블 설명에 언급되어 있습니다. 예를 들어, InnoDB 테이블(이름이 INNODB_로 시작하는 테이블)의 경우 PROCESS 권한이 필요합니다.

INFORMATION_SCHEMA에서 정보를 select하는 것과 같은 정보를 SHOW 문을 통해 보는 것에는 동일한 권한이 적용됩니다. 어느 쪽이든, 어떤 객체에 대한 정보를 보려면 해당 객체에 대해 어떤 권한이라도 가지고 있어야 합니다.

Performance Considerations

둘 이상의 데이터베이스에서 정보를 검색하는 INFORMATION_SCHEMA 쿼리는 시간이 오래 걸릴 수 있으며 성능에 영향을 줄 수 있습니다. 쿼리의 효율성을 확인하려면 EXPLAIN을 사용할 수 있습니다. EXPLAIN 출력을 사용하여 INFORMATION_SCHEMA 쿼리를 튜닝하는 방법에 대한 정보는 Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”를 참조하십시오.

Standards Considerations

MySQL에서 INFORMATION_SCHEMA 테이블 구조에 대한 구현은 ANSI/ISO SQL:2003 표준 Part 11 _Schemata_를 따릅니다. 우리의 목표는 SQL:2003 코어 기능 F021 _Basic information schema_에 대한 대략적인 준수입니다.

(이 표준을 따르는) SQL Server 2000 사용자들은 많은 유사점을 발견할 수 있을 것입니다. 그러나 MySQL은 우리의 구현과 관련이 없는 많은 컬럼을 생략하고, MySQL 고유의 컬럼을 추가했습니다. 이러한 추가된 컬럼 중 하나는 INFORMATION_SCHEMA TABLES 테이블의 ENGINE 컬럼입니다.

다른 DBMS는 syscat 또는 system과 같은 다양한 이름을 사용하지만, 표준 이름은 INFORMATION_SCHEMA입니다.

표준이나 DB2, SQL Server, Oracle에서 예약어로 되어 있는 이름을 사용하는 것을 피하기 위해, 우리는 “MySQL extension”으로 표시된 일부 컬럼의 이름을 변경했습니다. (예를 들어 TABLES 테이블에서 COLLATION을 TABLE_COLLATION으로 변경했습니다.) 이 article의 끝부분 근처에 있는 예약어 목록을 참조하십시오: https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5.

Conventions in the INFORMATION_SCHEMA Reference Sections

다음 section에서는 INFORMATION_SCHEMA의 각 테이블 및 컬럼을 설명합니다. 각 컬럼에 대해 세 가지 정보가 제공됩니다:

“INFORMATION_SCHEMA Name”은 INFORMATION_SCHEMA 테이블에서의 컬럼 이름을 나타냅니다. “Remarks” 필드에 “MySQL extension”이라고 되어 있지 않다면, 이는 표준 SQL 이름에 해당합니다.
“SHOW Name”은, 존재한다면, 가장 가까운 SHOW 문에서 동일한 필드 이름을 나타냅니다.
“Remarks”는 해당되는 경우 추가 정보를 제공합니다. 이 필드가 NULL이면, 컬럼의 값이 항상 NULL이라는 의미입니다. 이 필드에 “MySQL extension”이라고 되어 있으면, 해당 컬럼은 표준 SQL에 대한 MySQL extension입니다.

많은 section에서, INFORMATION_SCHEMA에서 정보를 가져오는 SELECT와 동일한 SHOW 문이 무엇인지 표시합니다. FROM db_name 절을 생략할 경우 기본 데이터베이스에 대한 정보를 표시하는 SHOW 문에 대해서는, INFORMATION_SCHEMA 테이블에서 정보를 가져오는 쿼리의 WHERE 절에 AND TABLE_SCHEMA = SCHEMA() 조건을 추가하여 기본 데이터베이스에 대한 정보를 선택할 수 있는 경우가 많습니다.

Related Information

다음 section에서는 추가적인 INFORMATION_SCHEMA 관련 토픽을 다룹니다:

InnoDB 스토리지 엔진에 특화된 INFORMATION_SCHEMA 테이블에 대한 정보: Section 28.4, “INFORMATION_SCHEMA InnoDB Tables”
thread pool 플러그인에 특화된 INFORMATION_SCHEMA 테이블에 대한 정보: Section 28.5, “INFORMATION_SCHEMA Thread Pool Tables”
CONNECTION_CONTROL 플러그인에 특화된 INFORMATION_SCHEMA 테이블에 대한 정보: Section 28.6, “INFORMATION_SCHEMA Connection Control Tables”
INFORMATION_SCHEMA 데이터베이스와 관련하여 자주 묻는 질문에 대한 답변: Section A.7, “MySQL 9.5 FAQ: INFORMATION_SCHEMA”
INFORMATION_SCHEMA 쿼리와 옵티마이저: Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”
INFORMATION_SCHEMA 비교에서 콜레이션의 효과: Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”