【MySQL】テーブル定義とカラム一覧を確認する

テーブル定義を確認するにはDESCコマンドを使う。
テーブル作成時のDDLをそのまま確認する場合はSHOW CREATE TABLE、コメントや文字コードも含めて確認する場合はSHOW FULL COLUMNSを使う。
SQLで取得する場合はinformation_schema.columnsを参照する。

DESCコマンドでカラム一覧を確認する

DESC テーブル名を実行すると、カラム名、データ型、NULL許可、キー、デフォルト値、追加情報が表示される。

mysql> DESC users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(255) | NO   |     | NULL              |                   |
| email      | varchar(255) | YES  | MUL | NULL              |                   |
| address    | text         | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

DESCはDESCRIBEの省略形で、SHOW COLUMNS FROM テーブル名と同じ結果になる。

Key列のPRIは主キー、MULはインデックスの先頭カラムであることを表す。
インデックスの詳細を確認する方法は以下を参照。
参考: 【PostgreSQL】指定したテーブルのインデックスを確認する

コメントや文字コードも確認する

SHOW FULL COLUMNSを使うと、Collation・Privileges・Comment列が追加される。

mysql> SHOW FULL COLUMNS FROM users;
+------------+--------------+--------------------+------+-----+-------------------+-------------------+----------------------------------+-----------------------+
| Field      | Type         | Collation          | Null | Key | Default           | Extra             | Privileges                       | Comment                |
+------------+--------------+--------------------+------+-----+-------------------+-------------------+----------------------------------+-----------------------+
| id         | int          | NULL               | NO   | PRI | NULL              | auto_increment    | select,insert,update,references  |                        |
| name       | varchar(255) | utf8mb4_0900_ai_ci | NO   |     | NULL              |                   | select,insert,update,references  |                        |
| email      | varchar(255) | utf8mb4_0900_ai_ci | YES  | MUL | NULL              |                   | select,insert,update,references  | メールアドレス |
| address    | text         | utf8mb4_0900_ai_ci | YES  |     | NULL              |                   | select,insert,update,references  |                        |
| created_at | timestamp    | NULL               | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED | select,insert,update,references  |                        |
+------------+--------------+--------------------+------+-----+-------------------+-------------------+----------------------------------+-----------------------+

Comment列にはカラムコメントが表示される。
数値型や日付型など、文字コードを持たないカラムのCollationはNULLになる。

SHOW CREATE TABLEでDDLを確認する

テーブル作成時のDDLをそのまま確認する場合はSHOW CREATE TABLEを使う。

mysql> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL COMMENT 'メールアドレス',
  `address` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

DESCやSHOW FULL COLUMNSとは異なり、主キーやインデックス、ストレージエンジン、文字コードもまとめて確認できる。
出力はそのまま実行可能なDDLであるため、テーブル定義を別環境にコピーする際にも使える。

行末の\Gは結果を縦形式で表示するオプションである。
Create Table列の値は改行を含む長い文字列であり、通常の横形式では折り返されて読みにくいため\Gを使う。

SQLでカラム一覧を取得する

information_schema.columnsにカラム情報が格納されている。
mysqlコマンドを使えない場合や、アプリケーションから取得する場合に使う。

mysql> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    -> FROM information_schema.columns
    -> WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_NAME = 'users'
    -> ORDER BY ORDINAL_POSITION;
+-------------+-----------+-------------+-------------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT    |
+-------------+-----------+-------------+-------------------+
| id          | int       | NO          | NULL              |
| name        | varchar   | NO          | NULL              |
| email       | varchar   | YES         | NULL              |
| address     | text      | YES         | NULL              |
| created_at  | timestamp | YES         | CURRENT_TIMESTAMP |
+-------------+-----------+-------------+-------------------+

TABLE_SCHEMAはMySQLでは「データベース」と同じ意味である。
テーブル一覧と併せて取得する方法は以下を参照。
参考: 【MySQL】テーブル一覧を取得する

データ型の桁数を取得する

DATA_TYPEには桁数が含まれない。DESCではvarchar(255)と表示されるカラムも、DATA_TYPEではvarcharとなる。

文字列型の最大長はCHARACTER_MAXIMUM_LENGTHで取得する。

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_NAME = 'users'
ORDER BY ORDINAL_POSITION;

+-------------+-----------+--------------------------+-------------+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE |
+-------------+-----------+--------------------------+-------------+
| id          | int       |                     NULL | NO          |
| name        | varchar   |                      255 | NO          |
| email       | varchar   |                      255 | YES         |
| address     | text      |                    65535 | YES         |
| created_at  | timestamp |                     NULL | YES         |
+-------------+-----------+--------------------------+-------------+

text型のCHARACTER_MAXIMUM_LENGTHには、実際に格納できる最大バイト数（65535）が入る。

桁数を含むデータ型をまとめて取得したい場合はCOLUMN_TYPEを使う。

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_NAME = 'users'
ORDER BY ORDINAL_POSITION;

+-------------+--------------+-------------+-----------------------+
| COLUMN_NAME | COLUMN_TYPE  | IS_NULLABLE | COLUMN_COMMENT        |
+-------------+--------------+-------------+-----------------------+
| id          | int          | NO          |                       |
| name        | varchar(255) | NO          |                       |
| email       | varchar(255) | YES         | メールアドレス |
| address     | text         | YES         |                       |
| created_at  | timestamp    | YES         |                       |
+-------------+--------------+-------------+-----------------------+

COLUMN_TYPEはSHOW COLUMNSのType列と同じ値であり、DATA_TYPEと違って桁数やunsignedなどの修飾を含む。
COLUMN_COMMENTにはカラムコメントが入る。

information_schema.columnsの主なカラム

MySQL 8.0のinformation_schema.columnsには22個のカラムが存在する。

カラム	内容
`table_schema`	テーブルが属するデータベース名
`table_name`	テーブル名
`column_name`	カラム名
`ordinal_position`	カラムの定義順
`column_default`	デフォルト値
`is_nullable`	NULLを許可するか(`YES`または`NO`)
`data_type`	データ型。桁数は含まない
`character_maximum_length`	文字列型の最大長
`numeric_precision`	数値型の精度
`numeric_scale`	数値型の位取り
`column_type`	桁数や`unsigned`を含むデータ型
`column_key`	`PRI`・`UNI`・`MUL`などのキー種別
`extra`	`auto_increment`などの追加情報
`column_comment`	カラムコメント

上記以外には次のカラムが存在する。

table_catalog
character_octet_length
datetime_precision
character_set_name、collation_name
privileges
generation_expression
srs_id

PostgreSQLのinformation_schema.columnsは43個のカラムが存在し、MySQLより項目数が多い。
参考: 【PostgreSQL】テーブル定義とカラム一覧を確認する