MySQL で特定スキーマの全カラム情報を取得｜information_schema

編集

この記事の要点

information_schema.columns にはサーバ上のすべてのカラム情報が格納されている
table_schema 列でデータベース（スキーマ）名を絞り込み、特定の DB のみを対象にできる
取得できる情報はカラム名・データ型・NULL 許可・デフォルト値・コメントなど多岐にわたる
SHOW COLUMNS はテーブル単位だが、information_schema.columns はクロステーブルで一括取得できるのが強み
型変更前の影響調査・コーディング用のドキュメント生成・命名規則チェックなどに使える

information_schema.columns とは

information_schema は MySQL / MariaDB が標準で持つメタデータ用データベースで、サーバ上のすべてのデータベース・テーブル・カラム・インデックスなどの情報を SQL で参照できるようにしたものです。その中の columns ビューにはすべてのカラム情報が 1 行 1 カラムの形で格納されています。

基本クエリ

特定のスキーマ（データベース）に絞って全カラムを取得する最小クエリです。

SELECT *
FROM   information_schema.columns
WHERE  table_schema = 'スキーマ名';

table_schema はデータベース名と同義です（MySQL では「スキーマ」と「データベース」は同じ意味）。

よく使う列の絞り込み

SELECT * だと 20 列以上返ってくるので、実務では必要な列だけ取り出すのが一般的です。

SELECT
    table_name,
    column_name,
    column_type,
    is_nullable,
    column_default,
    column_comment
FROM   information_schema.columns
WHERE  table_schema = 'mydb'
ORDER  BY table_name, ordinal_position;

information_schema.columns の主な列

列名	内容
`table_schema`	データベース名
`table_name`	テーブル名
`column_name`	カラム名
`ordinal_position`	カラムの並び順（1 始まり）
`column_default`	デフォルト値
`is_nullable`	NULL 可かどうか（YES / NO）
`data_type`	型のみ（int / varchar など）
`column_type`	型 + 長さ + UNSIGNED など完全な表記
`character_maximum_length`	文字列型の最大長
`numeric_precision` / `numeric_scale`	数値型の桁数
`column_key`	PRI / UNI / MUL（インデックス種別）
`extra`	auto_increment などの補足
`column_comment`	カラムコメント

実務でよくあるパターン

1. NULL 許可カラムだけを一覧

SELECT table_name, column_name
FROM   information_schema.columns
WHERE  table_schema = 'mydb'
  AND  is_nullable  = 'YES';

2. 特定の型を使っているカラムを洗い出す

-- TEXT / BLOB を使っているカラム
SELECT table_name, column_name, column_type
FROM   information_schema.columns
WHERE  table_schema = 'mydb'
  AND  data_type IN ('text','mediumtext','longtext','blob');

3. コメント未設定のカラムを抽出

SELECT table_name, column_name
FROM   information_schema.columns
WHERE  table_schema = 'mydb'
  AND  (column_comment IS NULL OR column_comment = '');

4. 同じカラム名がどのテーブルに存在するか

SELECT table_name
FROM   information_schema.columns
WHERE  table_schema = 'mydb'
  AND  column_name  = 'user_id';

SHOW COLUMNS との違い

項目	SHOW COLUMNS	information_schema.columns
対象範囲	1 テーブルのみ	サーバ全体・複数テーブル横断
WHERE 句	不可（簡易フィルタのみ）	SQL の WHERE で自由に絞り込み
JOIN	不可	他テーブルと JOIN 可能
用途	対話的な確認	調査・スキャン・ドキュメント生成

注意点

権限: information_schema はユーザが見える範囲（GRANT されたオブジェクト）しか返さない
大文字小文字: table_schema は OS によって大文字小文字を区別する場合がある（lower_case_table_names 設定に依存）
パフォーマンス: 全テーブルスキャンに近い動きをするため、本番稼働中の DB で多用しない

ドキュメント生成への応用

取得結果を Markdown 表に整形すれば、ER 図や設計書のカラム一覧ドキュメントとして再利用できます。MySQL CLI の --batch オプションでタブ区切り出力に切り替えると整形が楽です。

mysql -u root -p --batch -e "
  SELECT table_name, column_name, column_type,
         IF(is_nullable='YES','可','不可') AS null_ok,
         IFNULL(column_comment,'') AS comment
  FROM   information_schema.columns
  WHERE  table_schema = 'mydb'
  ORDER  BY table_name, ordinal_position
" > mydb_columns.tsv

TSV を awk や Python で読んでテーブル単位の Markdown を吐き出せば、CI で「最新スキーマの設計書」を自動更新できます。

命名規則チェックの例

命名規則違反（例: snake_case 統一なのに camelCase が混入）を一発で検出できます。

-- 大文字を含むカラム名（snake_case 違反）
SELECT table_name, column_name
FROM   information_schema.columns
WHERE  table_schema = 'mydb'
  AND  BINARY column_name REGEXP '[A-Z]';

ページ一覧

その他