MySQL ALTER命令修改表的属性

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

创建一个测试表

mysql> create table exmaple_alter_test(id int(11),name varchar(100));
Query OK, 0 rows affected (0.76 sec)

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+--

• 删除，添加或修改表字段

如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i d字段：

mysql> alter table exmaple_alter_test drop id;
Query OK, 0 rows affected (1.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

• MySQL 中使用 ADD 子句来向数据表中添加列

mysql> alter table exmaple_alter_test add sex varchar(100);
Query OK, 0 rows affected (1.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| sex   | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 指定添加位置

mysql> alter table exmaple_alter_test add age Int after name;
Query OK, 0 rows affected (1.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| sex   | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+--

• change 和modify 修改字段类型及名称

mysql> alter table exmaple_alter_test change sex sexs varchar(200);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| sexs  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)



mysql> alter table exmaple_alter_test modify age  int(100);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(100)     | YES  |     | NULL    |       |
| sexs  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• ALTER TABLE 对 Null 值和默认值的影响

mysql> alter table exmaple_alter_test modify age int not null default 0;
Query OK, 0 rows affected (1.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | NO   |     | 0       |       |
| sexs  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+-----


你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：
mysql> alter table exmaple_alter_test alter age drop default;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | NO   |     | NULL    |       |
| sexs  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

设置默认值
mysql> alter table exmaple_alter_test alter age set default 100;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from exmaple_alter_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | NO   |     | 100     |       |
| sexs  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+-----

• 修改表名

mysql> alter table exmaple_alter_test rename to exmaple_alter;
Query OK, 0 rows affected (0.08 sec)

修改某一列在表中的位置

alter table exmaple_userinfo modify id int  first ;


alter table exmaple_userinfo modify id int  after  列名;