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

# 概要

- 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`テーブルのなかのカラムを列挙してみる。下記の情報は[こちら](https://dev.mysql.com/doc/refman/8.0/ja/information-schema-columns-table.html)を参考に記載
    - `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を記載する。

    ```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を記載する。

    ```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は外したまま、型と文字列最大長と数値最大値を別で表示してみる

    ```sql
    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`として表示されてしまうので注意する。

    ```sql
    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を指定している。

    ```sql
    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`として表示する。

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

    ```sql
    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に記載したい。（これは別途解決策を考えよう。。。）

    ```sql
    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;
    ```

# 参考文献

https://dev.mysql.com/doc/refman/8.0/ja/information-schema-columns-table.html

https://dev.mysql.com/doc/refman/8.0/ja/string-type-syntax.html#:~:text=%E6%9C%80%E5%A4%A7%E9%95%B7%E3%81%8C%2065%2C535%20(2,%E3%81%97%E3%81%A6%E6%A0%BC%E7%B4%8D%E3%81%95%E3%82%8C%E3%81%BE%E3%81%99%E3%80%82

https://dev.mysql.com/doc/refman/8.0/ja/string-type-syntax.html