MySQL修改表，alter介绍

1 增加列

• alter table 表名 add 列名称 列参数 列声明
  • 追加：alter table 表名 add 列名称 列参数
  • 在某列后面追加：alter table 表名 add 列名称 列参数 [after 某列列明]
  • 加在最前面：alter table 表名 add 列名称 列参数 first

mysql> create table mt(
    -> id int unsigned auto_increment primary key
    -> ) engine myisam charset utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> desc mt;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> alter table mt add username varchar(20) not null default '';        Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mt;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   |     |         |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table mt add gender varchar(1) not null default '' after id;  Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mt;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| gender   | varchar(1)       | NO   |     |         |                |
| username | varchar(20)      | NO   |     |         |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

2 删除列

• alter table 表名 drop 列明

mysql> alter table mt drop gender;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mt;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   |     |         |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

3 修改列类型

• alter table 表名 modify 列名称 新列参数 新列声明

注意：修改类型时注意数据，一般类型往大了修改，当往小修改时注意数据范围。

mysql> alter table mt modify username varchar(10) not null default '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

4 修改列名和列类型

• alter table 表名 change 旧列名 新列名称 新列参数 新列声明

mysql> alter table mt change username uname varchar(15) not null default '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mt;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| uname | varchar(15)      | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)