表描述:
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 區(qū)別
- 更改列名 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 省事點
