「テーブル定義を直接見ても、目的のカラムが見つからない」
DBA経験のあるエンジニアなら一度は遭遇する場面です。
この記事では、MySQLで指定したカラム名を持つテーブルを検索する方法を、information_schemaを使った実コマンド付きで解説します。MySQL 5.7 / MySQL 8.0 / MariaDB 10.x で動作確認済みのSQLです。
この記事のポイント
・information_schema.columnsをSELECTすればMySQL内のカラム検索が一発でできる
・table_schemaを条件に加えると、特定データベースに絞った検索が可能
・LIKE演算子で部分一致検索（user_%・%_idなど）にも対応
・MariaDB / MySQL 8.0 でも同じSQLがそのまま動く（標準化済みのスキーマ）
でも安心してください。プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
MySQLで指定したカラム名を持つテーブルを検索する基本SQL
MySQLで特定のカラム名を持つテーブルがわからない場合、下記SQLを発行することで検索が行えます。MySQLにログイン後、下記SQLを実行します。
select table_name, column_name from information_schema.columns where column_name = '検索したいカラム名';
mysql> select table_name, column_name from information_schema.columns where column_name = 'site_url'; +----------------+-------------+ | table_name | column_name | +----------------+-------------+ | gl_pingservice | site_url | +----------------+-------------+ 1 row in set (0.21 sec)
information_schemaとは何か：MySQLのメタデータが集約された仮想DB
information_schemaは、MySQLが内部的に保持する「DB/テーブル/カラム/インデックスの定義情報」を、SQLで参照できるよう仮想テーブル化したデータベースです。標準SQLのANSI/ISO規格に準拠しているため、MySQL 5.5以降、MariaDB 5.x以降、PostgreSQL（こちらはinformation_schemaを完全サポート）でほぼ同じSQLが動きます。
主要なテーブルは以下です。
・SCHEMATA：データベース（スキーマ）一覧
・TABLES：テーブル/ビュー一覧
・COLUMNS：全カラム情報（型・NULL許容・デフォルト値含む）
・STATISTICS：インデックス情報
・VIEWS：ビューの定義SQL
・TRIGGERS：トリガー定義
・PROCESSLIST：現在の接続/クエリ一覧（SHOW PROCESSLIST相当）
特定のデータベースに絞ってカラム検索する：table_schemaの活用
本番環境では複数DBが共存しているため、検索対象のDBを絞り込まないと無関係なテーブルがヒットします。table_schemaを条件に加えます。mysql> select table_schema, table_name, column_name -> from information_schema.columns -> where column_name = 'user_id' -> and table_schema = 'wordpress'; +--------------+----------------+-------------+ | table_schema | table_name | column_name | +--------------+----------------+-------------+ | wordpress | wp_usermeta | user_id | | wordpress | wp_comments | user_id | | wordpress | wp_links | link_owner | +--------------+----------------+-------------+ 3 rows in set (0.03 sec)
mysql> select table_schema, table_name, column_name -> from information_schema.columns -> where column_name = 'created_at' -> and table_schema not in ('mysql','sys','performance_schema','information_schema');
LIKE演算子で部分一致検索する：カラム名がうろ覚えの時の探し方
カラム名の正確な綴りが分からない場合は、LIKE演算子を使って部分一致検索ができます。# 「user_」で始まるカラムをすべて検索 mysql> select table_name, column_name -> from information_schema.columns -> where column_name like 'user_%' -> and table_schema = 'wordpress'; # 「_id」で終わるカラムをすべて検索（外部キー候補の洗い出し） mysql> select table_name, column_name -> from information_schema.columns -> where column_name like '%_id' -> and table_schema = 'wordpress'; # 「mail」を含むカラムをすべて検索（個人情報棚卸し） mysql> select table_schema, table_name, column_name -> from information_schema.columns -> where column_name like '%mail%' -> and table_schema not in ('mysql','sys','performance_schema','information_schema');
カラムの型・NULL許容・デフォルト値まで含めて取得する
column_nameだけでなく、データ型・NULL許容・デフォルト値も同時に取得すると、テーブル定義の概要が一気に把握できます。mysql> select table_name, column_name, data_type, is_nullable, column_default, column_key -> from information_schema.columns -> where column_name = 'site_url'; +----------------+-------------+-----------+-------------+----------------+------------+ | table_name | column_name | data_type | is_nullable | column_default | column_key | +----------------+-------------+-----------+-------------+----------------+------------+ | gl_pingservice | site_url | varchar | NO | NULL | PRI | +----------------+-------------+-----------+-------------+----------------+------------+ 1 row in set (0.04 sec)
SHOW COLUMNSとの違い：いつinformation_schemaを使うべきか
MySQLには昔から SHOW COLUMNS というコマンドがありますが、これは「1つのテーブルのカラムを一覧表示する」用途専用です。mysql> SHOW COLUMNS FROM gl_pingservice; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | site_url | varchar(255) | NO | PRI | NULL | | | service_url | varchar(255) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+
シェルから直接クエリを実行する：mysqlコマンドの-eオプション
information_schemaへのクエリは、シェルスクリプトに組み込んで自動化することが多いです。mysqlコマンドに-eオプションを渡せば、対話モードに入らずにクエリ結果を取得できます。[root@db01 ~]# mysql -uroot -pXXXX -e "select table_schema, table_name from information_schema.columns where column_name = 'email' and table_schema not in ('mysql','sys','performance_schema','information_schema');" # CSV出力する場合は --batch --raw を併用 [root@db01 ~]# mysql -uroot -pXXXX --batch --raw -e "select table_schema, table_name from information_schema.columns where column_name = 'email';" > /tmp/email_columns.tsv
【重要】information_schema検索の性能上の注意点
情報系SQLの落とし穴を3つ挙げておきます。・巨大DBではinformation_schema.columnsが重い：テーブル数が10万を超えるような大規模DBでは、columns全件スキャンに数十秒かかります。MySQL 8.0ではdata dictionaryに改良が入り高速化されていますが、5.7では本番LATENCYに影響します。
・権限がないDBは見えない：information_schemaは「権限のあるDB」しか表示しません。rootならば全部見えますが、アプリ用ユーザーで実行すると結果が空になることがあります。
・テーブル名・カラム名の大文字小文字：Linux環境ではデフォルトで大文字小文字を区別します（lower_case_table_names=0）。site_urlとSite_URLは別物として扱われるので、BINARYやLOWER()を使う場面があります。
# 大文字小文字を無視して検索 mysql> select table_name, column_name -> from information_schema.columns -> where lower(column_name) = lower('Site_URL');
「ERROR 1044: Access denied」が出た時の対処
information_schemaへのアクセスでエラーが出る場合の対処を整理します。# エラー: 権限不足 mysql> select * from information_schema.columns; ERROR 1044 (42000): Access denied for user 'app'@'localhost' to database 'information_schema' # 対処: rootで該当ユーザーに権限付与 mysql> GRANT SELECT ON information_schema.* TO 'app'@'localhost'; mysql> FLUSH PRIVILEGES; # エラー: 結果が空になる mysql> select * from information_schema.columns where column_name = 'user_id'; Empty set (0.00 sec) # 対処: そのユーザーがアクセス可能なDBにそもそもuser_idが存在しないことを確認 mysql> SHOW DATABASES;
本記事のまとめ：MySQLカラム検索SQL早見表
information_schemaを使いこなせると、巨大DBの保守・棚卸しが圧倒的に楽になります。| やりたいこと | コマンド |
|---|---|
| カラム名でテーブル検索 | select table_name, column_name from information_schema.columns where column_name = 'site_url'; |
| 特定DBに絞って検索 | select table_name from information_schema.columns where column_name = 'user_id' and table_schema = 'wordpress'; |
| 部分一致でカラム検索 | select table_name, column_name from information_schema.columns where column_name like '%mail%'; |
| カラム型まで取得 | select table_name, column_name, data_type, is_nullable from information_schema.columns where column_name = 'site_url'; |
| システムDB除外検索 | select table_schema, table_name from information_schema.columns where column_name = 'email' and table_schema not in ('mysql','sys','performance_schema','information_schema'); |
| シェルから一発実行 | mysql -uroot -pXXXX -e "select table_name from information_schema.columns where column_name = 'email';" |
| 単一テーブルのカラム一覧 | SHOW COLUMNS FROM gl_pingservice; |
| 大文字小文字を無視して検索 | select table_name from information_schema.columns where lower(column_name) = lower('Site_URL'); |
3,100名以上が実践した「型」を無料で公開中
プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
その「型」を図解60Pにまとめた入門マニュアルを、完全無料でプレゼントしています。
登録10秒／合わなければ解除3秒 ／ 詳細はこちら
この記事を書いた人
宮崎 智広（みやざき ともひろ）
株式会社イーネットマーキュリー代表。現役のLinuxサーバー管理者として20年以上の実務経験を持ち、これまでに累計3,100名以上のエンジニアを指導してきたLinux教育のプロフェッショナル。「現場で本当に使える技術」を体系的に伝えることをモットーに、実践型のLinuxセミナーの開催や無料マニュアルの配布を通じてLinux人材の育成に取り組んでいる。
趣味は、キャンプにカメラ、トラウト釣り。好きな食べ物は、ラーメンにお酒。休肝日が作れない、酒量を減らせないのが悩み。最近、ドラマ「フライトエンジェル」を観て涙腺が崩壊しました。
- 次のページへ：MySQLでユーザー一覧を表示する方法｜mysql.userの確認とCURRENT_USERの活用
- 前のページへ：MySQLのユーザー一覧と権限を確認するコマンド｜SELECT user・SHOW GRANTSとホスト別の見方
- この記事の属するカテゴリ：Linuxtips・MySQL・サーバー管理・データーベース管理へ戻る
無料メルマガで学習を続ける
Linuxの実践スキルをメールで毎週お届け。
登録は1分、解除もいつでも可。
登録無料・いつでも解除できます