MySQL メタデータ
MySQL メタデータは、データベースとそのオブジェクト(テーブル、列、インデックスなど)に関する情報です。
メタデータはシステムテーブルに保存されており、これらのテーブルは MySQL データベースの information_schema データベースにあります。これらのシステムテーブルをクエリすることで、データベース構造、オブジェクト、その他の関連情報の詳細を取得できます。
MySQLについて、次の3つの情報を知りたい場合があります。
- クエリ結果の情報:SELECT、UPDATE、DELETE 文によって影響を受けたレコード数。
- データベースとデータテーブルの情報:データベースとデータテーブルの構造情報が含まれます。
- MySQL サーバー情報:データベースサーバーの現在の状態、バージョン番号などが含まれます。
MySQL のコマンドプロンプトでは、上記のサーバー情報を簡単に取得できますが、Perl や PHP などのスクリプト言語を使用する場合は、特定のインターフェース関数を呼び出して取得する必要があります。次に詳しく説明します。
以下は、よく使用される MySQL メタデータのクエリです:
すべてのデータベースを表示:
SHOW DATABASES;
データベースを選択:
USE database_name;
データベース内のすべてのテーブルを表示:
SHOW TABLES;
テーブルの構造を表示:
DESC table_name;
テーブルのインデックスを表示:
SHOW INDEX FROM table_name;
テーブルの作成文を表示:
SHOW CREATE TABLE table_name;
テーブルの行数を表示:
SELECT COUNT(*) FROM table_name;
列の情報を表示:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
上記の SQL 文の 'your_database_name' と 'your_table_name' は、それぞれあなたのデータベース名とテーブル名です。
外部キー情報を表示:
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;上記の SQL 文の 'your_database_name' と 'your_table_name' を実際のデータベース名とテーブル名に置き換えてください。
information_schema データベース
information_schema は MySQL データベース内のシステムデータベースであり、データベースサーバーに関するメタデータ情報が含まれています。これらの情報はテーブルの形式で information_schema データベースに保存されます。
SCHEMATA テーブル
データベース名、文字セット、照合順序など、データベースに関する情報を格納します。
SELECT * FROM information_schema.SCHEMATA;
TABLES テーブル
テーブル名、データベース名、エンジン、行数など、データベース内のすべてのテーブルに関する情報が含まれます。
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
COLUMNS テーブル
列名、データ型、NULL を許可するかどうかなど、テーブルの列に関する情報が含まれます。
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
STATISTICS テーブル
インデックス名、列名、一意性など、テーブルインデックスに関する統計情報を提供します。
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
KEY_COLUMN_USAGE テーブル
外部キー名、列名、関連テーブルなど、テーブル内の外部キーに関する情報が含まれます。
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
REFERENTIAL_CONSTRAINTS テーブル
制約名、関連テーブルなど、外部キー制約に関する情報を格納します。
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
これらのテーブルは豊富なメタデータ情報を提供し、データベース構造、テーブル情報、列情報、インデックス情報などのクエリに使用できます。
クエリ内の 'your_database_name' と 'your_table_name' を実際のデータベース名とテーブル名に置き換える必要があることに注意してください。
クエリ文の影響を受けたレコード数を取得する
PERL の例
DBI スクリプトでは、文の影響を受けたレコード数は関数 do( ) または execute( ) によって返されます:
# 方法 1 # 使用do( ) 执行 $query my $count = $dbh->do ($query); # 如果发生错误会输出 0 printf "%d 条数据被影响\n", (defined ($count) ? $count : 0); # 方法 2 # 使用prepare( ) 及 execute( ) 执行 $query my $sth = $dbh->prepare ($query); my $count = $sth->execute ( ); printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
PHP の例
PHP では、mysqli_affected_rows( ) 関数を使用して、クエリ文の影響を受けたレコード数を取得できます。
$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");
データベースとデータテーブルのリスト
MySQL サーバーでデータベースとデータテーブルのリストを簡単に取得できます。十分な権限がない場合は、結果は null を返します。
SHOW TABLES または SHOW DATABASES 文を使用して、データベースとデータテーブルのリストを取得することもできます。
PERL の例
# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
print "表名 $table\n";
}
PHP の例
以下の例は、MySQL サーバー上のすべてのデータベースを出力します:
すべてのデータベースを表示
サーバーのメタデータを取得
以下のコマンド文は、MySQL のコマンドプロンプトで使用できるほか、PHP スクリプトなどのスクリプト内でも使用できます。
| コマンド | 説明 |
|---|---|
| SELECT VERSION( ) | サーバーバージョン情報 |
| SELECT DATABASE( ) | 現在のデータベース名(または空を返す) |
| SELECT USER( ) | 現在のユーザー名 |
| SHOW STATUS | サーバーステータス |
| SHOW VARIABLES | サーバー設定変数 |