INFORMATION_SCHEMA では、データベースメタデータへのアクセスを実現し、データベースまたはテーブルの名前、カラムのデータ型、アクセス権限などの MySQL Server に関する情報を提供します。この情報に使用されることがある別の用語が、データディクショナリとシステムカタログです。

INFORMATION_SCHEMA データベースの使用上の注意

INFORMATION_SCHEMA は、各 MySQL インスタンス内のデータベースであり、MySQL Server が保持するほかのすべてのデータベースに関する情報を格納する場所です。INFORMATION_SCHEMA データベースには複数の読み取り専用テーブルが含まれます。これらには実際にはビューがあるので、関連付けられたファイルはなく、トリガーは設定できません。また、その名前を持つデータベースディレクトリもありません。

USE ステートメントを使用してデフォルトデータベースとして INFORMATION_SCHEMA を選択できますが、実行できる操作はテーブル内容の読み取りだけで、テーブルに対する INSERT、UPDATE、DELETE 操作は実行できません。

例

次に INFORMATION_SCHEMA から情報を取り出すステートメントの例を示します。

mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'db5'
    -> ORDER BY table_name;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| fk         | BASE TABLE | InnoDB |
| fk2        | BASE TABLE | InnoDB |
| goto       | BASE TABLE | MyISAM |
| into       | BASE TABLE | MyISAM |
| k          | BASE TABLE | MyISAM |
| kurs       | BASE TABLE | MyISAM |
| loop       | BASE TABLE | MyISAM |
| pk         | BASE TABLE | InnoDB |
| t          | BASE TABLE | MyISAM |
| t2         | BASE TABLE | MyISAM |
| t3         | BASE TABLE | MyISAM |
| t7         | BASE TABLE | MyISAM |
| tables     | BASE TABLE | MyISAM |
| v          | VIEW       | NULL   |
| v2         | VIEW       | NULL   |
| v3         | VIEW       | NULL   |
| v56        | VIEW       | NULL   |
+------------+------------+--------+
17 rows in set (0.01 sec)

説明: このステートメントは、データベース db5 内のすべてのテーブルのリストを要求し、テーブルの名前、種類、ストレージエンジンという 3 つの情報だけを示します。

文字セットの考慮事項

文字カラム (TABLES.TABLE_NAME など) の定義は、通常、VARCHAR(N) CHARACTER SET utf8 であり、ここで N は少なくとも 64 です。MySQL は、このようなカラムでのすべての検索、ソート、比較、およびほかの文字列操作に、この文字セット (utf8_general_ci) のデフォルトの照合順序を使用します。

一部の MySQL オブジェクトはファイルとして表されるので、INFORMATION_SCHEMA 文字列カラムでの検索は、ファイルシステムでの大文字と小文字の区別によって影響を受けることがあります。詳細は、セクション10.1.7.9「照合順序と INFORMATION_SCHEMA 検索」を参照してください。

SHOW ステートメントの代替方法としての INFORMATION_SCHEMA

SELECT ... FROM INFORMATION_SCHEMA ステートメントは、MySQL がサポートするさまざまな SHOW ステートメント (SHOW DATABASES、SHOW TABLES など) により提供された情報にアクセスするための一貫性の高い方法として用意されています。SELECT は、SHOW に比べて次の利点があります。

すべてのアクセスがテーブル上で行われるので、Codd のルールに準拠しています。
SELECT ステートメントの使い慣れた構文を使用でき、学習が必要なものは一部のテーブルおよびカラムの名前だけです。
実装者はキーワードの追加を心配する必要がありません。
INFORMATION_SCHEMA クエリーからの結果をフィルタリング、ソート、連結でき、構文解析するデータ構造やテキスト表現など、アプリケーションで必要なあらゆる形式に変換できます。
この手法は、ほかのデータベースシステムとの相互運用可能性に優れています。たとえば、Oracle Database ユーザーは、Oracle データディクショナリのテーブルのクエリーに慣れています。

SHOW はよく知られ、広く使用されているので、SHOW ステートメントは引き続き代替方法として有効です。実際、セクション21.32「SHOW ステートメントの拡張」で説明しているように、INFORMATION_SCHEMA の実装に伴い、SHOW に拡張が行われています。

権限

各 MySQL ユーザーには、これらのテーブルへのアクセス権がありますが、ユーザーが適切なアクセス権限を持つオブジェクトに対応したテーブル内の行だけを表示できます。一部の場合 (たとえば INFORMATION_SCHEMA.ROUTINES テーブル内の ROUTINE_DEFINITION カラム) では、権限が不足しているユーザーには NULL と表示されます。これらの制限は InnoDB テーブルには適用されません。PROCESS 権限だけがあればこれらを表示できます。

同じ権限が、INFORMATION_SCHEMA からの情報の選択と、SHOW ステートメントを通じた同じ情報の表示に適用されます。どちらの場合でも、オブジェクトに関する情報を表示するには、そのオブジェクトに対する何らかの権限が必要です。

パフォーマンスに関する考慮事項

複数のデータベースの情報を検索する INFORMATION_SCHEMA クエリーは、長時間かかり、パフォーマンスに影響を及ぼす可能性があります。クエリーの効率を確認するには、EXPLAIN を使用できます。EXPLAIN 出力を使用した INFORMATION_SCHEMA クエリーの調整に関する詳細は、セクション8.2.4「INFORMATION_SCHEMA クエリーの最適化」を参照してください。

標準に関する考慮事項

MySQL での INFORMATION_SCHEMA テーブル構造の実装は ANSI/ISO SQL:2003 標準パート 11 の Schemata に準拠しています。SQL:2003 の中核機能の F021 基本情報スキーマにほぼ準拠することを意図しています。

SQL Server 2000 (これも標準に準拠しています) のユーザーであれば、非常に類似していることがわかるでしょう。ただし、MySQL では実装に関連しない多くのカラムを省略し、MySQL 固有のカラムを追加しています。このようなカラムの 1 つが、INFORMATION_SCHEMA.TABLES テーブル内の ENGINE カラムです。

ほかの DBMS では syscat や system などのさまざまな名前を使用していますが、標準の名前は INFORMATION_SCHEMA です。

標準または DB2、SQL Server、Oracle で予約されている名前を使用しないように、「MySQL 拡張」のマークを付けて一部のカラムの名前を変更しています。(たとえば、TABLES テーブルでの COLLATION を TABLE_COLLATION に変更しました。)https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5 の記事の末尾近くにある予約語のリストを参照してください。

INFORMATION_SCHEMA 参照セクションでの規則

次のセクションでは、INFORMATION_SCHEMA でのテーブルおよびカラムのそれぞれについて説明します。カラムごとに次の 3 つの情報があります。

「INFORMATION_SCHEMA 名」には、INFORMATION_SCHEMA テーブルのカラムの名前が示されます。これは、「備考」フィールドで「MySQL 拡張」と記されていないかぎり、標準の SQL 名に一致します。
「SHOW 名」には、もっとも近い SHOW ステートメントに同等のフィールド名がある場合は、この名前が示されます。
「備考」には、必要に応じて追加情報が記されます。このフィールドが NULL の場合、カラムの値が常に NULL であることを意味します。このフィールドに「MySQL 拡張」とある場合、そのカラムは標準 SQL に対する MySQL 拡張です。

多くのセクションは、どの SHOW ステートメントが、INFORMATION_SCHEMA から情報を取得する SELECT と同等であるかを示します。デフォルトデータベースの情報を表示する SHOW ステートメントでは、FROM db_name 句を省略した場合、INFORMATION_SCHEMA テーブルから情報を取得するクエリーの WHERE 句に AND TABLE_SCHEMA = SCHEMA() 条件を追加すると、デフォルトデータベースの情報を選択できます。

InnoDB ストレージエンジンに固有の INFORMATION_SCHEMA テーブルの詳細は、セクション21.29「InnoDB の INFORMATION_SCHEMA テーブル」を参照してください。スレッドプールプラグインに固有の INFORMATION_SCHEMA テーブルの詳細は、セクション21.31「スレッドプールの INFORMATION_SCHEMA テーブル」を参照してください。

INFORMATION_SCHEMA データベースに関するよくある質問とその回答については、セクションA.7「MySQL 5.6 FAQ: INFORMATION_SCHEMA」を参照してください。

PREV HOME UP NEXT

User Comments

Posted by Hans-Henrik Stærfeldt on February 20, 2009

The implementation of INFORMATION_SCHEMA can have serious impact on performance of the server. If you have many tables, and query into INFORMATION_SCHEMA without limitations on the schema and if possible the table itself, performance is severely impacted while the query runs.

This, i theorize, is because it uses the existing table cache to open and get information on tables, so in reality, all tables are closed when you execute the query in INFORMATION_SCHEMA (if you have many tables!).

Posted by Ivan Cachicatari on February 29, 2012

You can found a custom SHOW TABLE STATUS command based on INFORMATION_SCHEMA database at:

http://en.latindevelopers.com/ivancp/2012/a-better-show-table-status/