mysql自增列注意事项_MySQL使用AUTO_INCREMENT列的表注意事项之update自增列篇

1)对于MyISAM表，如果用UPDATE更新自增列，如果列值与已有的值重复，则会出错；如果大于已有的最大值，则会自动更新表的AUTO_INCREMENT，操作是安全的。

(2)对于innodb表，update auto_increment字段，如果列值与已有的值重复，则会出错；如果大于已有的最大值，可能会引入一个坑，会造成编号重复错误，插入数据失败的情况，可见在update自增列值是要注意。

环境描述：RHEL 6.4 x86_64 + MySQL 5.6.19

blog地址：http://blog.csdn.net/hw_libo/article/details/40097125

在维护有AUTO_INCREMENT列的表时，另外一个注意点，参考：

MySQL使用AUTO_INCREMENT列的表注意事项之delete数据篇

http://blog.csdn.net/hw_libo/article/details/40149173

下面实验证实：

2. MyISAM表

MySQL [bosco]> CREATE TABLE `t5` (

-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

-> PRIMARY KEY (`id`)

-> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.05 sec)

MySQL [bosco]> insert into t5 values(null);

Query OK, 1 row affected (0.07 sec)

MySQL [bosco]> select * from t5;

+----+

| id |

+----+

| 1 |

+----+

1 row in set (0.00 sec)

MySQL [bosco]> insert into t5 values(5),(9);

Query OK, 2 rows affected (0.04 sec)

Records: 2 Duplicates: 0 Warnings: 0

MySQL [bosco]> select * from t5;

+----+

| id |

+----+

| 1 |

| 5 |

| 9 |

+----+

3 rows in set (0.00 sec)

2.1 MyISAM表update自增列，由大改小

MySQL [bosco]> show create table t5\G

*************************** 1. row ***************************

Table: t5

Create Table: CREATE TABLE `t5` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

MySQL [bosco]> update t5 set id=4 where id=9; ## 将自增列由大改小，没有问题

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MySQL [bosco]> show create table t5\G

*************************** 1. row ***************************

Table: t5

Create Table: CREATE TABLE `t5` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

2.2 MyISAM表update自增列，由小改大

MySQL [bosco]> show create table t5\G

*************************** 1. row ***************************

Table: t5

Create Table: CREATE TABLE `t5` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

MySQL [bosco]> update t5 set id=12 where id=5; ## 将自增列由小改大，而且大于当前的AUTO_INCREMENT，同样是没有问题

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MySQL [bosco]> show create table t5\G

*************************** 1. row ***************************

Table: t5

Create Table: CREATE TABLE `t5` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

这里自动修改最新的auto_increment变为13。

可见，MyISAM表的update自增列不会存在风险。

3. InnoDB表

MySQL [bosco]> CREATE TABLE `t6` (

-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

-> PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.06 sec)

MySQL [bosco]> insert into t6 values(null);

Query OK, 1 row affected (0.05 sec)

MySQL [bosco]> insert into t6 values(5),(9);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

MySQL [bosco]> select * from t6;

+----+

| id |

+----+

| 1 |

| 5 |

| 9 |

+----+

3 rows in set (0.00 sec)

3.1 InnoDB表update自增列，由大改小

MySQL [bosco]> show create table t6\G

*************************** 1. row ***************************

Table: t6

Create Table: CREATE TABLE `t6` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

MySQL [bosco]> update t6 set id=4 where id=9;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MySQL [bosco]> show create table t6\G

*************************** 1. row ***************************

Table: t6

Create Table: CREATE TABLE `t6` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

可见，InnoDB表update自增列时，由大值改为小值，除了可能会出现重复数据修改失败外，没有其他风险。

3.2  InnoDB表update自增列，由小改大

MySQL [bosco]> select * from t6;

+----+

| id |

+----+

| 1 |

| 4 |

| 5 |

+----+

3 rows in set (0.00 sec)

MySQL [bosco]> show create table t6\G

*************************** 1. row ***************************

Table: t6

Create Table: CREATE TABLE `t6` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

MySQL [bosco]> update t6 set id=12 where id=5; ## 将自增列由小改大，而且大于当前的AUTO_INCREMENT，这就相当于挖了坑了

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MySQL [bosco]> select * from t6;

+----+

| id |

+----+

| 1 |

| 4 |

| 12 |

+----+

3 rows in set (0.01 sec)

MySQL [bosco]> show create table t6\G

*************************** 1. row ***************************

Table: t6

Create Table: CREATE TABLE `t6` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

表中自增列最大值已经是12，这个update操作不会自动修改最新的auto_increment变为13，那么这就会有问题，以后增加到12后，就会出现冲突，导致数据插入失败：

MySQL [bosco]> insert into t6 values(null),(null);

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

MySQL [bosco]> insert into t6 values(null); ## 错误出现了。

ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

原文：https://blog.csdn.net/HW_LiBo/article/details/40097125