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!