LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@miriwo(大川 峻)inBOOM TECH CAFE

MySQL show full columnsと同じ内容をselect文で出力してみる

Posted at

概要

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

前提

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

    Field Type Collation Null Key Default 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;

参考文献

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?