表描述:
mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
MySQL 中 change 和 modify 区别
- 更改列名 change:
alter table 表名 change 旧列名 新列名 类型
mysql> alter table test change name name_new int(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name_new | int(10) | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- 只更改列属性 change:
alter table 表名 列名 列名 类型相同的列名要写两次.
mysql> alter table test change name_new name_new char(32);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name_new | char(32) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
- 更改列属性 modify:
alter table 表名 列名 类型
mysql> desc test;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name_new | char(32) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table test modify name_new int(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name_new | int(10) | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- change 可以更改列名 和 列类型 (每次都要把新列名和旧列名写上, 即使两个列名没有更改,只是改了类型)
- modify 只能更改列属性 只需要写一次列名, 比change 省事点
