やりたいこと
テーブル構造を確認するクエリはいくつがあるが、できるだけ手軽に確認したい。
手順
以下クエリを実行する。
DESCRIBE
mysql> DESCRIBE {テーブル名};
より短くするなら以下。
mysql> DESC {テーブル名};
以下、WordPressのテーブルでの実行例。
mysql> DESC wp123456options; +--------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+----------------+ | option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | option_name | varchar(191) | NO | UNI | | | | option_value | longtext | NO | | NULL | | | autoload | varchar(20) | NO | MUL | yes | | +--------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
SHOW FIELDS FROM
同じ結果が得られるが以下クエリは少し長い。
mysql> SHOW FIELDS FROM wp123456options; +--------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+----------------+ | option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | option_name | varchar(191) | NO | UNI | | | | option_value | longtext | NO | | NULL | | | autoload | varchar(20) | NO | MUL | yes | | +--------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
SHOW COLUMNS FROM
こちらもやや長い。
mysql> SHOW COLUMNS FROM wp123456options; +--------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+----------------+ | option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | option_name | varchar(191) | NO | UNI | | | | option_value | longtext | NO | | NULL | | | autoload | varchar(20) | NO | MUL | yes | | +--------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
SHOW FULL COLUMNS FROM
SHOW COLUMNSはFULLを追加すると、照合順序のCollationとユーザ権限のPrivileges、Commentも表示できる。
mysql> SHOW FULL COLUMNS FROM wp123456options; +--------------+---------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------------+---------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+ | option_id | bigint(20) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | option_name | varchar(191) | utf8mb4_unicode_520_ci | NO | UNI | | | select,insert,update,references | | | option_value | longtext | utf8mb4_unicode_520_ci | NO | | NULL | | select,insert,update,references | | | autoload | varchar(20) | utf8mb4_unicode_520_ci | NO | MUL | yes | | select,insert,update,references | | +--------------+---------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+ 4 rows in set (0.00 sec)
SHOW CREATE TABLE
CREATE TABLEを見ることでもテーブル構造を確認できる。
mysql> SHOW CREATE TABLE wp123456options; +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | wpf4c150options | CREATE TABLE `wpf4c150options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '', `option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL, `autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`), KEY `autoload` (`autoload`) ) ENGINE=InnoDB AUTO_INCREMENT=2888 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci | +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
まとめ
用途によるが最も短いのはDESC。
