首页 > 代码库 > modify与change的区别

modify与change的区别

 对mysql的表的表结构进行修改时,有用到change,modify两个,它们都有“改变”的意思,那它们在功能上有什么区别了?做个试验比较下

 

1、字段重命名:

1)change

mysql> alter table t1 change number id char(2);

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

2)modify

mysql> alter table t1 modify id num int(2);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘num int(2)‘ at line 1

mysql>

结论:能用change重命名,而modify不能。

 

2、修改字段类型和约束

1)modify

mysql> alter table t1 modify id int(2);

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table t1 modify id int(2) not null;

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

2)change

 

mysql> alter table t1 change id char(2);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘char(2)‘ at line 1

 

 

mysql> alter table t1 change id char(2) not null;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘char(2) not null‘ at line 1

 

结论:modify能修改字段类型和约束,而change不能。

 

最终结论:change用来字段重命名,不能修改字段类型和约束;

         modify不用来字段重命名,只能修改字段类型和约束;

modify与change的区别