3
4

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 5 years have passed since last update.

指定したテーブルのカラム名を全て取得する

Posted at

概要

MySQL DB から、テーブル名を指定してそのテーブルに含まれる全てのカラム名を取得する方法について記載する

information_schemeを使う

コマンド
mysql -u [username] -p -D [database_name] -e "select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA where TABLE_SCHEMA=[database_name] AND TABLE_NAME=[table_name];"
結果
+---------------+------------+-------------+
| TABLE_SCHEMA  | TABLE_NAME | COLUMN_NAME |
+---------------+------------+-------------+
| database_name | table_name | column1     | 
| database_name | table_name | column2     | 
| database_name | table_name | column3     | 
+---------------+------------+-------------+

show full columns を使用する

mysql -u [username] -p -D [database_name] -e "show full columns from [table_name];" 
+----------------+-----------------+------------------+------+-----+---------------------+----------------+---------------------------------+-----------------------------------+
| Field          | Type            | Collation        | Null | Key | Default             | Extra          | Privileges                      | Comment                           |
+----------------+-----------------+------------------+------+-----+---------------------+----------------+---------------------------------+-----------------------------------+
| id             | int(11)         | NULL             | NO   | PRI | NULL                | auto_increment | select,insert,update,references |                                   | 
| member_id      | varchar(100)    | sjis_japanese_ci | NO   |     | NULL                |                | select,insert,update,references |  this column is uuid              |
| created        | datetime        | NULL             | NO   |     | NULL                |                | select,insert,update,references |                                   | 
| updated        | timestamp       | NULL             | NO   |     | 0000-00-00 00:00:00 |                | select,insert,update,references |                                   | 
+----------------+-----------------+------------------+------+-----+---------------------+----------------+---------------------------------+-----------------------------------+

比較

information_scheme テーブルにはMySQLの中に含まれるDB及びテーブル、各テーブルのカラムに関する情報が全て手に入る。
また、show full columns からも同様の情報を取得できるが、こちらはテーブルに付与されたコメントも含めて取得できる。

参考
https://dev.mysql.com/doc/refman/5.6/ja/information-schema.html

3
4
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
3
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?