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

1 description (1) for the MyISAM table, if the auto-increment column is updated using UPDATE, if the column 20540 is repeated with the existing 20540, an error will occur. if the column is greater than the existing maximum 20540 ;, the AUTO_INCREMENT of the table is automatically updated, and the operation is safe. (2) for innodb tables, upda 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!