MySQL :: MySQL 8.0 リファレンスマニュアル

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.8.7「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 ステートメントは引き続き代替方法として有効です。実際、セクション26.55「SHOW ステートメントの拡張」で説明しているように、INFORMATION_SCHEMA の実装に伴い、SHOW に拡張が行われています。

INFORMATION_SCHEMA および権限

ほとんどの INFORMATION_SCHEMA テーブルでは、各 MySQL ユーザーはそれらにアクセスする権限を持ちますが、ユーザーが適切なアクセス権限を持つオブジェクトに対応するテーブルの行のみを表示できます。場合によっては (たとえば、INFORMATION_SCHEMA ROUTINES テーブルの ROUTINE_DEFINITION カラム)、権限が不十分なユーザーに NULL が表示されます。一部のテーブルには異なる権限要件があります。これらの要件については、該当するテーブルの説明に記載されています。たとえば、InnoDB テーブル (INNODB_で始まる名前を持つテーブル) には、PROCESS 権限が必要です。

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

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

複数のデータベースの情報を検索する INFORMATION_SCHEMA クエリーは、長時間かかり、パフォーマンスに影響を及ぼす可能性があります。クエリーの効率を確認するには、EXPLAIN を使用できます。 EXPLAIN 出力を使用した INFORMATION_SCHEMA クエリーの調整に関する詳細は、セクション8.2.3「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() 条件を追加すると、デフォルトデータベースの情報を選択できます。

26.1 はじめに