关于MySQL里的change和modify,总是看到两种不同的语法,在Oracle中语法有modify,如果修改表名有rename。
alter table change,modify的语法如下:
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
看到一个很善于总结的外国友人总结了一张图,直接搬过来。
大体来说,change可以修改列名,除了这一点和modify不同之外,其它功能都一样。
我们做个简单的小测试来说吧,我们创建一个表test_cm(change和modify合体的意思),然后顺便测试一下auto_increment的对比情况。 create table test_cm
然后插入一行数据,让自增列生效。
(id int(20) unsigned not null auto_increment,name varchar(30),address varchar(50),
primary key(id)
);
insert into test_cm(name,address) values('name1','address1');
这个没什么多说的,自增列从1开始。
select *from test_cm;
+----+-------+----------+
| id | name | address |
+----+-------+----------+
| 1 | name1 | address1 |
+----+-------+----------+
我们使用modify语句来修改列的属性,把自增属性去掉。
> alter table test_cm modify column id int(20) not null;
如果使用change语句来做,就是下面的这样,两者在这方面是一样的功能。
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
> alter table test_cm change column id id int(20) not null;
我们这个时候再尝试插入一条数据,这个时候就充分调用了字段的默认值,数值型为0.
> insert into test_cm(name,address) values('name1','address1');
所以查到的数据是下标为0,和自增列没有关系了。
Query OK, 1 row affected, 1 warning (0.00 sec)
> select * from test_cm;
而这个时候继续插入,就会报错了,因为默认值还是0,就和已有的数据冲突了。
+----+-------+----------+
| id | name | address |
+----+-------+----------+
| 0 | name1 | address1 |
| 1 | name1 | address1 |
+----+-------+----------+
> insert into test_cm(name,address) values('name1','address1');
我们补充列名,分别插入两条数据。
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
> insert into test_cm values(2,'name2','address2');
> insert into test_cm values(3,'name3','address3');
这个时候的数据情况如下:
> select *from test_cm;
我们这个时候再来看看change和modify修改列的属性为自增。
+----+-------+----------+
| id | name | address |
+----+-------+----------+
| 0 | name1 | address1 |
| 1 | name1 | address1 |
| 2 | name2 | address2 |
| 3 | name3 | address3 |
+----+-------+----------+
> alter table test_cm change id id int(20) not null auto_increment;
当然不光是change语句,modify语句也会中招。
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
> alter table test_cm modify id int(20) not null auto_increment;
修改自增列还有一种用法,那就是直接表级设置,当然还是有场景的,在此只是为了对比说明。
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
> alter table test_cm auto_increment=4;
这种情况查看字段属性是没有auto_increment的。> show create table test_cm\G
我们继续插入一条数据,还是会抛出一个错误,说明自增列设置没起作用。
*************************** 1. row ***************************
Table: test_cm
Create Table: CREATE TABLE `test_cm` (
`id` int(20) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> insert into test_cm(name,address) values('name3','address3');
继续改进,那就是使用change,
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
> alter table test_cm modify id int(20) not null auto_increment ;
这个问题该怎么解,那就是数值冲突了,我们根据错误可以看出是主键值为1的,我们清理一下。
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'> delete from test_cm where id=1;
这个时候的数据如下。
Query OK, 1 row affected (0.00 sec)> select * from test_cm;
最后关键的时候到来了。
+----+-------+----------+
| id | name | address |
+----+-------+----------+
| 0 | name1 | address1 |
| 2 | name2 | address2 |
| 3 | name3 | address3 |
+----+-------+----------+
> alter table test_cm modify id int(20) not null auto_increment ;
而数据竟然是这样的。
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
> select *from test_cm;
+----+-------+----------+
| id | name | address |
+----+-------+----------+
| 1 | name1 | address1 |
| 2 | name2 | address2 |
| 3 | name3 | address3 |
+----+-------+----------+