【MySQL】特定のカラムが使用されているテーブルを検索【クエリ文】

例えば、order_idというカラムがデータベースのどのテーブルに使われてるか確認したいことがあると思います。大体の予想は付くはずなので、1つ1つテーブルを確認していく方法もありますが、使用されている関連テーブルが多数あると、探すのが困難で見落とす可能性もあります。そんな時は、この方法でサクッと確実に検索できます。

information_schema

MySQLサーバ内にinformation_schemaというデータベースがデフォルトで存在します。こちらは、データベースやテーブルの名前、列のデータ型、アクセス権限など、データに関するデータが入っています。こちらを利用して特定のカラムのテーブルを検索します。

クエリ文を実行

information_schemaのデータベース内で下記SQL文を実行することで簡単に検索できます。

select *
from
  information_schema.columns 
where
    COLUMN_NAME = "カラム名"
    and table_schema = "データベース名";

"カラム名"と"データベース名"は書き換えてください。

サンプル

例えば、eccubeテーブル内のorder_idが使用されているテーブルを検索したいとします。下記のようにSQLを実行します。

select  *
from
  information_schema.columns 
where
    COLUMN_NAME = "order_id"
    and table_schema = "eccube";

実行結果
phpMyAdmin

カラムTABLE_NAMEのレコードが使用されいるテーブル名です。これで3つのどのテーブルに使用されいているか直ぐ分かりました。

クエリ文の説明

一応説明しておくと、3行目でinformation_schema内にあるcolumsテーブルを指定。 5行目でCOLUMN_NAME内のフィールドと指定のカラム名が一致且つtable_schema内のフィールドと、指定のデータベース名が一致しているが確認しています。

開発現場で起きたトラブル

ECサイトを本番リリースしたときのこと。主キーがある受注テストデータを削除した後、外部キーで使用されいている関連テーブルも全て削除する必要がありますが、一部に削除漏れがあり、受注データに異なるデータが存在する予想外の不具合が発生しました。このようなトラブルを未然に防ぐためにも、外部キーで使用されいる関連テーブルが多いと確認漏れが発生する可能性があるので、今回紹介したクエリ文で事前に確認すべきだと感じました。

以上となります。 Have a nice development day~~!