テーブルのカラム情報取得
クエリ
show columns from [テーブル名];
show full columns from [テーブル名]; -- ←コメントまで取得したい場合
実行結果
mysql> show columns from users;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name_sei | varchar(255) | NO | | NULL | |
| name_mei | varchar(255) | NO | | NULL | |
| tel | varchar(255) | YES | | NULL | |
| email | varchar(255) | NO | MUL | NULL | |
| postal_code | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| created | datetime | NO | | NULL | |
| modified | datetime | NO | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> show full columns from users;
+---------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| name_sei | varchar(255) | utf8_general_ci | NO | | NULL | | select,insert,update,references | ユーザー名の姓 |
| name_mei | varchar(255) | utf8_general_ci | NO | | NULL | | select,insert,update,references | ユーザー名の名 |
| tel | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 電話番号 |
| email | varchar(255) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | メールアドレス |
| postal_code | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 郵便番号 |
| address | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 住所 |
| created | datetime | NULL | NO | | NULL | | select,insert,update,references | |
| modified | datetime | NULL | NO | | NULL | | select,insert,update,references | |
+---------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------+
9 rows in set (0.00 sec)
その他
既存カラムにコメント追加
ALTER TABLE [テーブル名] CHANGE COLUMN [カラム名] [カラム名] [型] [制約系(例: NOT NULL, DEFAULT 'aaa', etc...)] COMMENT [コメント];
auto_incrementの追加
alter table [テーブル名] auto_increment = [id];