MySQL中的change,modify和自增列的关系(r12笔记第70天)

关于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'); select *from test_cm; +----+-------+----------+ | id | name | address | +----+-------+----------+ | 1 | name1 | address1 | +----+-------+----------+

这个没什么多说的，自增列从1开始。

我们使用modify语句来修改列的属性，把自增属性去掉。

> alter table test_cm modify column id int(20) not null; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0

如果使用change语句来做，就是下面的这样，两者在这方面是一样的功能。

> alter table test_cm change column id id int(20) not null;

我们这个时候再尝试插入一条数据，这个时候就充分调用了字段的默认值，数值型为0.

> insert into test_cm(name,address) values('name1','address1'); Query OK, 1 row affected, 1 warning (0.00 sec)

所以查到的数据是下标为0，和自增列没有关系了。

> select * from test_cm; +----+-------+----------+ | id | name | address | +----+-------+----------+ | 0 | name1 | address1 | | 1 | name1 | address1 | +----+-------+----------+

而这个时候继续插入，就会报错了，因为默认值还是0，就和已有的数据冲突了。

> 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; +----+-------+----------+ | id | name | address | +----+-------+----------+ | 0 | name1 | address1 | | 1 | name1 | address1 | | 2 | name2 | address2 | | 3 | name3 | address3 | +----+-------+----------+

我们这个时候再来看看change和modify修改列的属性为自增。

> alter table test_cm change id id int(20) not null auto_increment; ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

当然不光是change语句，modify语句也会中招。

> 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'); ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

继续改进，那就是使用change,

> 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'

这个问题该怎么解，那就是数值冲突了，我们根据错误可以看出是主键值为1的，我们清理一下。 > 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 | +----+-------+----------+