MySQL - change 和 modify 的区别

https://www.jianshu.com/p/e9338da60a61

表描述:

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 省事点

作者：iOS_愛OS
链接：https://www.jianshu.com/p/e9338da60a61
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。