MySQL 資料一覧
INFORMATION_SCHEMAを利用してメタデータにアクセスする
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| :