【MySQL】知っておくと便利なinformation_schemaのクエリ集

はじめに

INFORMATION_SCHEMAはデータベースのメタデータが格納されているデータベースです。以下、公式ドキュメントの解説になります。

INFORMATION_SCHEMA では、データベースメタデータへのアクセスを実現し、データベースまたはテーブルの名前、カラムのデータ型、アクセス権限などの MySQL Server に関する情報を提供します。
https://dev.mysql.com/doc/refman/8.0/ja/information-schema-introduction.html

使ってみると意外と便利なので、いくつかピックアップして紹介します。

クエリ集

テーブル、カラムの一覧取得

用例：名前はなんとなくわかるが、どこのテーブルにあるのかがわからないカラムを探す

SELECT `TABLE_NAME`,
       `COLUMN_NAME`,
       `COLUMN_KEY`,
       `DATA_TYPE`,
       `IS_NULLABLE`,
       `COLUMN_DEFAULT`,
       `COLUMN_COMMENT`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = DATABASE()
  AND `COLUMN_NAME` LIKE '%column_name%';

インデックスの確認

用例：JOINを追加するときにキーとして使用するカラムにインデックスが張られているかどうかを確認する

SELECT `TABLE_NAME`,
       `COLUMN_NAME`,
       `INDEX_NAME`,
       `NON_UNIQUE`,
       `CARDINALITY`, -- カーディナリティは推定値
       `IS_VISIBLE`,
       `INDEX_COMMENT`
FROM `INFORMATION_SCHEMA`.`STATISTICS`
WHERE `TABLE_SCHEMA` = DATABASE()
  AND `TABLE_NAME` = 'table_name'
  AND `INDEX_NAME` <> 'PRIMARY'
ORDER BY `CARDINALITY` ASC;

外部キーとして参照されているカラムと参照しているカラム一覧を取得

用例：データを更新・削除しても問題ないかを確認する

SELECT `TABLE_NAME`,
       `COLUMN_NAME`,
       `REFERENCED_TABLE_NAME`,
       `REFERENCED_COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE `REFERENCED_TABLE_SCHEMA` = DATABASE()
  AND `REFERENCED_TABLE_NAME` IS NOT NULL;

CASCADEが設定されているカラム一覧を取得

用例：データを更新・削除する際に他のテーブルのデータに影響を与えてしまわないかを確認する

SELECT `TABLE_SCHEMA`,
       `KEY_COLUMN_USAGE`.`TABLE_NAME`,
       `COLUMN_NAME`,
       `KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME`,
       `REFERENCED_COLUMN_NAME`,
       `UPDATE_RULE`,
       `DELETE_RULE`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
         JOIN `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
              ON `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`TABLE_SCHEMA` =
                 `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA`
                  AND
                 `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`TABLE_NAME` =
                 `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`.`TABLE_NAME`
                  AND
                 `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` =
                 `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_NAME`
WHERE `DELETE_RULE` = 'CASCADE'
   OR `UPDATE_RULE` = 'CASCADE';

テーブルの行数とサイズ一覧の取得

用例：サイズが大きすぎるテーブルを検出する

SELECT `TABLE_SCHEMA`                                 AS `DATABASE_NAME`,
       `TABLE_NAME`,
       `TABLE_ROWS`                                   AS `ROW_COUNT`,
       `DATA_LENGTH` / 1024 / 1024                    AS `DATA_SIZE_MB`,
       `INDEX_LENGTH` / 1024 / 1024                   AS `INDEX_SIZE_MB`,
       (`DATA_LENGTH` + `INDEX_LENGTH`) / 1024 / 1024 AS `TOTAL_SIZE_MB`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = DATABASE()
ORDER BY `TOTAL_SIZE_MB` DESC;

おわりに

以下の通り他にも多くのテーブルがあるので、色々と応用できそうです。

INFORMATION_SCHEMA テーブル
https://dev.mysql.com/doc/refman/8.0/ja/information-schema.html

また便利なものがあれば追記していきたいと思います。今回は以上です。