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 は、MySQL サーバーのメタデータを標準の SQL クエリを通じて公開する仮想データベースです。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`	すべての列。型、デフォルト、NULL 許容性を含む
`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 Tutorial

ガイド

データの保存

クエリの作成

データの保護

パフォーマンスの最適化

AI で構築する

MySQL の運用

アプリケーションの接続

拡張機能の作成