Precautions for using the AUTO_INCREMENT column in MySQL: update auto-incrementing Column

Precautions for using the AUTO_INCREMENT column in MySQL: update auto-incrementing Column
1. Description

(1) For the MyISAM table, if you use UPDATE to UPDATE the auto-incrementing column, if the column value is the same as the existing value, an error occurs. If the column value is greater than the existing maximum value, the AUTO_INCREMENT of the table is automatically updated, and the operation is safe.

(2) For the innodb table, in the update auto_increment field, if the column value is the same as the existing value, an error occurs. If the column value is greater than the existing maximum value, a pitfall may be introduced, duplicate numbers and data insertion failures may occur. Note that the auto-increment column value in update is correct.

Environment Description: RHEL 6.4 x86_64 + MySQL 5.6.19

Blog: http://blog.csdn.net/hw_libo/article/details/40097125

The following experiment confirms:

2. MyISAM table

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: 0MySQL [bosco]> select * from t5;+----+| id |+----+|  1 ||  5 ||  9 |+----+3 rows in set (0.00 sec)

2.1 update auto-increment column in MyISAM table, changed from large to small

MySQL [bosco]> show create table t5 \ G **************************** 1. row *************************** Table: t5Create Table: create table 't5 '('id' int (10) unsigned not null AUTO_INCREMENT, primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 10 default charset = utf81 row in set (0.00 sec) MySQL [bosco]> update t5 set id = 4 where id = 9; # change the auto-increment column from large to small, no problem 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: t5Create Table: create table 't5 '('id' int (10) unsigned not null AUTO_INCREMENT, primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 10 default charset = utf81 row in set (0.00 sec)

2.2 update auto-increment column in MyISAM table, changed from small to large

MySQL [bosco]> show create table t5 \ G **************************** 1. row *************************** Table: t5Create Table: create table 't5 '('id' int (10) unsigned not null AUTO_INCREMENT, primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 10 default charset = utf81 row in set (0.00 sec) MySQL [bosco]> update t5 set id = 12 where id = 5; # change the auto-increment column from small to large and greater than the current AUTO_INCREMENT. Similarly, 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: t5Create Table: create table 't5 '('id' int (10) unsigned not null AUTO_INCREMENT, primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 13 default charset = utf81 row in set (0.00 sec) here the latest auto_increment is changed to 13 automatically.

It can be seen that the update auto-increment column in The MyISAM table is not risky.

3. InnoDB table

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: 0MySQL [bosco]> select * from t6;+----+| id |+----+|  1 ||  5 ||  9 |+----+3 rows in set (0.00 sec)

3.1 update auto-incrementing column in the InnoDB table, from large to small

MySQL [bosco]> show create table t6\G*************************** 1. row ***************************       Table: t6Create Table: CREATE TABLE `t6` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 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: 0MySQL [bosco]> show create table t6\G*************************** 1. row ***************************       Table: t6Create Table: CREATE TABLE `t6` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf81 row in set (0.00 sec)

It can be seen that when the auto-increment column is updated in the InnoDB table, there is no risk except that duplicate data modification may fail.

3.2 InnoDB table update auto-increment column, changed from small to large

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: t6Create Table: create table 't6 '('id' int (10) unsigned not null AUTO_INCREMENT, primary key ('id ')) ENGINE = InnoDB AUTO_INCREMENT = 10 default charset = utf81 row in set (0.00 sec) MySQL [bosco]> update T6 set id = 12 where id = 5; # increase the auto-increment column from small to large and greater than the current AUTO_INCREMENT. This is equivalent to digging the 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: t6Create Table: create table 't6 '('id' int (10) unsign Ed not null AUTO_INCREMENT, primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 10 default charset = utf81 row in set (0.01 sec, this update operation will not automatically change the latest auto_increment to 13, so there will be a problem. After it is increased to 12, a conflict will occur, leading to data insertion failure: 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 occurs. ERROR 1062 (23000): Duplicate entry '12' for key 'primary'

Blog: http://blog.csdn.net/hw_libo/article/details/40097125

-- Bosco QQ: 375612082

---- END ----
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!