2007/12/19更新

対応バージョン: 5.0.51

INFORMATION_SCHEMA(情報スキーマ)を利用するとデータベースメタデータへのアクセスが可能になる。

INFORMATION_SCHEMAはSQL:2003(ISO/IEC 9075:2003)で定められているシステムカタログへの標準インタフェースで、他のDBMSでも実装されつつある。

MySQLではこの標準に加えて独自の拡張項目があるが、ここでは触れない。

例えばすべてのデータベースのすべてのユーザテーブルを表示するには以下のクエリを実行する。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_TYPE = "BASE TABLE"
    -> ORDER BY TABLE_SCHEMA, TABLE_NAME;
+--------------+---------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME                | TABLE_ROWS |
+--------------+---------------------------+------------+
| mysql        | columns_priv              |          0 | 
| mysql        | db                        |          2 | 
| mysql        | func                      |          0 | 
| mysql        | help_category             |          0 | 
| mysql        | help_keyword              |          0 | 
| mysql        | help_relation             |          0 | 
| mysql        | help_topic                |          0 | 
| mysql        | host                      |          0 | 
| mysql        | proc                      |          0 | 
| mysql        | procs_priv                |          0 | 
| mysql        | tables_priv               |          0 | 
| mysql        | time_zone                 |          0 | 
| mysql        | time_zone_leap_second     |          0 | 
| mysql        | time_zone_name            |          0 | 
| mysql        | time_zone_transition      |          0 | 
| mysql        | time_zone_transition_type |          0 | 
| mysql        | user                      |          5 | 
| test         | pet                       |          2 | 
+--------------+---------------------------+------------+

TABLE_TYPEには以下の種類がある。

BASE TABLE

ユーザテーブル

VIEW

ビュー

SYSTEM VIEW

システムビュー

PROCEDURE

ストアドプロシージャ

FUNCTION

ファンクション

TRIGGER

トリガ

その他、主な情報スキーマを以下に示す。

INFORMATION_SCHEMA.SCHEMATA

データベース情報

例)

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
    -> FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+----------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| information_schema | utf8                       | 
| mysql              | latin1                     | 
| test               | latin1                     | 
+--------------------+----------------------------+

データベースの一覧は以下のようにしても参照できる。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+

INFORMATION_SCHEMA.COLUMNS

カラム情報

例)

mysql> SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
    -> COLUMN_KEY, COLUMN_DEFAULT, EXTRA
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = 'pet';
+-----------+-----------+-----------+----------+--------------+-----+
|COLUMN_NAME|COLUMN_TYPE|IS_NULLABLE|COLUMN_KEY|COLUMN_DEFAULT|EXTRA|
+-----------+-----------+-----------+----------+--------------+-----+
|name       |varchar(20)|YES        |          |NULL          |     | 
|owner      |varchar(20)|YES        |          |NULL          |     | 
|type       |varchar(20)|YES        |          |NULL          |     | 
|sex        |char(1)    |YES        |          |NULL          |     | 
|birth      |date       |YES        |          |NULL          |     | 
+-----------+-----------+-----------+----------+--------------+-----+

これは以下と同義である。

mysql> SHOW COLUMNS FROM pet;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       | 
| owner | varchar(20) | YES  |     | NULL    |       | 
| type  | varchar(20) | YES  |     | NULL    |       | 
| sex   | char(1)     | YES  |     | NULL    |       | 
| birth | date        | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+

INFORMATION_SCHEMA.USER_PRIVILEGES

ユーザ権限

例)

mysql> SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE
    -> FROM INFORMATION_SCHEMA.USER_PRIVILEGES;
+--------------------+----------------+--------------+
| GRANTEE            | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+----------------+--------------+
| 'root'@'localhost' | SELECT         | YES          | 
| 'root'@'localhost' | INSERT         | YES          | 
| 'root'@'localhost' | UPDATE         | YES          | 
| 'root'@'localhost' | DELETE         | YES          | 
| 'root'@'localhost' | CREATE         | YES          | 
| 'root'@'localhost' | DROP           | YES          | 
   :

INFORMATION_SCHEMA.VIEWS

ビュー定義

例)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.VIEWS;
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| test         | test_view  | 
+--------------+------------+

INFORMATION_SCHEMA.ROUTINES

ストアドプロシージャ/ファンクション定義

例)

mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME
    -> FROM INFORMATION_SCHEMA.ROUTINES;
+----------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME |
+----------------+--------------+
| test           | test_proc    | 
+----------------+--------------+

INFORMATION_SCHEMA.TRIGGERS

トリガ定義

例)

mysql> SELECT TRIGGER_SCHEMA, TRIGGER_NAME
    -> FROM INFORMATION_SCHEMA.TRIGGERS;
+----------------+--------------+
| TRIGGER_SCHEMA | TRIGGER_NAME |
+----------------+--------------+
| test           | test_trigger | 
+----------------+--------------+

INFORMATION_SCHEMA.CHARACTER_SETS

サポートされている文字コードセット

例)

mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;
+------------------+--------------------+---------------------+------+
|CHARACTER_SET_NAME|DEFAULT_COLLATE_NAME|DESCRIPTION          |MAXLEN|
+------------------+--------------------+---------------------+------+
 :
|latin1            |latin1_swedish_ci   |cp1252 West European |     1| 
 :
|ascii             |ascii_general_ci    |US ASCII             |     1| 
 :
|utf8              |utf8_general_ci     |UTF-8 Unicode        |     3| 
|binary            |binary              |Binary pseudo charset|     1| 
 :