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

Source: Internet
Author: User

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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.