MySQLで指定したカラム名を持つテーブルを検索する｜information_schemaを使ったDB横断検索

この記事のポイント

・information_schema.columnsをSELECTすればMySQL内のカラム検索が一発でできる
・table_schemaを条件に加えると、特定データベースに絞った検索が可能
・LIKE演算子で部分一致検索（user_%・%_idなど）にも対応
・MariaDB / MySQL 8.0 でも同じ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)

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');

# 「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');

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)

# column_typeまで含めた詳細な定義取得（テーブル定義書の素材として活用）
mysql> select table_name, column_name, column_type, is_nullable, column_default, column_key, extra
    -> from information_schema.columns
    -> where table_schema = 'wordpress'
    -> order by table_name, ordinal_position;

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    |       |
+-------------+--------------+------+-----+---------+-------+

[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

# 大文字小文字を無視して検索
mysql> select table_name, column_name
    -> from information_schema.columns
    -> where lower(column_name) = lower('Site_URL');

# エラー: 権限不足
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;