概要

MySQLにてshow full columns from テーブル名とすると当該テーブルのカラムの情報が取得できる。
あえてshow full columnsを使わずにSQL句で同じ情報を取得してみようと思う。

前提

とある個人サービスにてshow full columns from usersを実行したら下記のように出力された。

Field	Type	Collation	Null	Key	Extra	Privileges	Comment
id	bigint unsigned		NO	PRI	auto_increment	"select,insert,update,references"
name	varchar(255)	utf8mb4_unicode_ci	NO			"select,insert,update,references"
email	varchar(255)	utf8mb4_unicode_ci	NO	UNI		"select,insert,update,references"
password	varchar(255)	utf8mb4_unicode_ci	NO			"select,insert,update,references"
two_factor_secret	text	utf8mb4_unicode_ci	YES			"select,insert,update,references"
two_factor_recovery_codes	text	utf8mb4_unicode_ci	YES			"select,insert,update,references"
two_factor_confirmed_at	timestamp		YES			"select,insert,update,references"
user_type_master_id	bigint unsigned		NO	MUL		"select,insert,update,references"	ユーザータイプマスターID
created_at	timestamp		YES			"select,insert,update,references"
updated_at	timestamp		YES			"select,insert,update,references"
deleted_at	timestamp		YES			"select,insert,update,references"

上記と同じ出力結果をselect句を使って書いてみる。

情報

MySQLはinformation_schemaという特殊なデータベースで、データベースを構成するメタ情報を保持している。
カラム情報はinformation_schemaデータベースのcolumnsテーブルで保持している。
このcolumnsテーブルのなかのカラムにアクセスすることで、指定したカラムのメタデータを知ることができる。
今回使うcolumnsテーブルのなかのカラムを列挙してみる。下記の情報はこちらを参考に記載
- COLUMN_NAME: カラム名
- COLUMN_TYPE: 型（付随情報あり）
- DATA_TYPE: 型（型情報のみ）
- CHARACTER_MAXIMUM_LENGTH: 文字列最大長
- NUMERIC_PRECISION: 数値最大値
- COLLATION_NAME: 文字コード名
- IS_NULLABLE: null許可
- COLUMN_KEY: キー
- COLUMN_DEFAULT: デフォルト値
- EXTRA: 特記情報
- PRIVILEGES: 許可情報
- COLUMN_COMMENT: カラムコメント
- TABLE_NAME: テーブル名
なので下記のようにSQLを組み立てれば情報を出力できそう。
- 対象DB: information_schema
- 対象テーブル: columns
- 対象カラム
  - COLUMN_NAME
  - COLUMN_TYPE
  - COLLATION_NAME
  - IS_NULLABLE
  - COLUMN_KEY
  - COLUMN_DEFAULT
  - EXTRA
  - PRIVILEGES
  - COLUMN_COMMENT
- 絞り込み条件
  - table_schemaがテーブル情報がほしいテーブルが所属するDB名 && table_nameがテーブル情報がほしいテーブル名

`show full column`と同じ情報を得るselect文

下記にSQLを記載する。

SELECT 
    COLUMN_NAME as 'Field',
    COLUMN_TYPE as 'Type',
    COLLATION_NAME as 'Collation',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name = 'users'
ORDER BY ORDINAL_POSITION;

カラムを絞ってみる

show full columnsを実行したときに「Collationの情報は必要ないんだよな〜、、、」みたいな事がある。
show full columnsだと出力内容をかいつまむ事はできない（はず、、！できたらすみません。）
せっかくselect文を使っているので出力内容をちょっとカスタムしてみようと思う。

Collationを外す

単純にselectの対象からCOLLATION_NAMEを除外すれば良いだけ

下記にSQLを記載する。

SELECT 
    COLUMN_NAME as 'Field',
    COLUMN_TYPE as 'Type',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name = 'users'
ORDER BY ORDINAL_POSITION;

Collationを外し、データ型と文字列最大長、数値最大値を別で出力

Collationは外したまま、型と文字列最大長と数値最大値を別で表示してみる

SELECT 
    COLUMN_NAME as 'Field',
    COLUMN_TYPE as 'Type',
    CHARACTER_MAXIMUM_LENGTH as 'Lengtg',
    NUMERIC_PRECISION as 'Digits',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name = 'users'
ORDER BY ORDINAL_POSITION;

Collationを外し、文字列最大長と数値最大値を`Size`として表示する。

文字列最大長と数値最大値が別れているとわかりにくいのでSizeとして一緒に表示してみる。
SQLのCASE文を使う。
型の部分に文字列最大長と数値最大値があるとわかりにくいので型は型の情報だけにする。

DATA_TYPEだとbigintでもbigint unsignedも両方bigintとして表示されてしまうので注意する。

SELECT 
    COLUMN_NAME as 'Field',
    DATA_TYPE as 'Type',
    CASE
        WHEN DATA_TYPE = 'int' THEN NUMERIC_PRECISION
        WHEN DATA_TYPE LIKE 'varchar%' THEN CHARACTER_MAXIMUM_LENGTH
        ELSE NULL
    END as 'Size',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name = 'users'
ORDER BY ORDINAL_POSITION;

Collationを外し、複数テーブルの出力に対応

複数のテーブルを指定してスキーマを出力してみる。

selectにテーブル名を追加して、and条件も無垢数指定のinを使って、order byの最初にTABLE_NAMEを指定している。

SELECT
    TABLE_NAME as 'Table',
    COLUMN_NAME as 'Field',
    COLUMN_TYPE as 'Type',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name in ('users', 'messages')
ORDER BY 
	TABLE_NAME,
	ORDINAL_POSITION;

Collationを外し、複数テーブルの出力に対応、文字列最大長と数値最大値を`Size`として表示する。

ここまでの内容をまとめてみる。

SELECT
    TABLE_NAME as 'Table',
    COLUMN_NAME as 'Field',
    DATA_TYPE as 'Type',
    CASE
        WHEN DATA_TYPE = 'int' THEN NUMERIC_PRECISION
        WHEN DATA_TYPE LIKE 'varchar%' THEN CHARACTER_MAXIMUM_LENGTH
        ELSE NULL
    END as 'Size',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name in ('users', 'messages')
ORDER BY 
	TABLE_NAME,
	ORDINAL_POSITION;

Sizeをもうちょっとだけリッチに、、

Sizeのtext型やbigintの場合の桁数も出してみる。

ただDATA_TYPEを使うとすべてbigintになってしまう。unsignedのものはunsignedとしてtypeに記載したい。（これは別途解決策を考えよう。。。）

SELECT
    TABLE_NAME as 'Table',
    COLUMN_NAME as 'Field',
    DATA_TYPE as 'Type',
    CASE
        WHEN DATA_TYPE = 'int' THEN NUMERIC_PRECISION
        WHEN DATA_TYPE LIKE 'varchar%' THEN CHARACTER_MAXIMUM_LENGTH 
        # 先にbigint unsignedをチェックしないとすべてbigintになってしまう
        WHEN COLUMN_TYPE LIKE 'bigint unsigned' THEN 18446744073709551615
        WHEN DATA_TYPE LIKE 'bigint' THEN 9223372036854775807
        WHEN DATA_TYPE LIKE 'text' THEN 65535
        ELSE NULL
    END as 'Size',
    IS_NULLABLE as 'Null',
    COLUMN_KEY as 'Key',
    COLUMN_DEFAULT as 'Default',
    EXTRA as 'Extra',
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns
WHERE table_schema = '下記のテーブルが所属するDB名'
AND table_name in ('users', 'messages')
ORDER BY 
	TABLE_NAME,
	ORDINAL_POSITION;

参考文献