MySQL Use auto_increment column table notes update self-add column

Source: Internet
Author: User

1. Description

(1) For the MyISAM table, if you update the self-increment column with update, an error occurs if the column value repeats with the existing value, and if it is greater than the existing maximum, the auto_increment of the table is automatically updated and the operation is safe.

(2) for the InnoDB table, the Update Auto_increment field, if the column values and the existing values are duplicated, then an error occurs, if the value is greater than the existing maximum, may introduce a pit, will cause the number of duplicate errors, the insertion of data failures, visible in the update self-increment column value is to be noted.

Environment Description: RHEL 6.4 x86_64 + MySQL 5.6.19

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

The following experiments confirm:


2. MyISAM table


MySQL [bosco]> CREATE TABLE ' T5 ' (    -   id ' int ') 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 MyISAM Table Update self-increment column, from big to small
MySQL [bosco]> Show CREATE TABLE t5\g*************************** 1. Row ***************************       table:t5create table:create Table ' T5 ' (  ' id ' int (ten) unsigned not NULL auto_inc Rement,  PRIMARY KEY (' id ')) engine=myisam auto_increment=10 DEFAULT charset=utf81 row in Set (0.00 sec) MySQL [BOSCO]&G T Update T5 set id=4 where id=9;   # # Will Self-increment column by big change small, no problem query OK, 1 row affected (0.02 sec) Rows matched:1  changed:1  warnings:0mysql [bosco]> show CR Eate table t5\g*************************** 1. Row ***************************       table:t5create table:create Table ' T5 ' (  ' id ' int (ten) unsigned not NULL auto_inc Rement,  PRIMARY KEY (' id ')) engine=myisam auto_increment=10 DEFAULT charset=utf81 row in Set (0.00 sec)


2.2 MyISAM table Update self-increment column, from small to large
MySQL [bosco]> Show CREATE TABLE t5\g*************************** 1. Row ***************************       table:t5create table:create Table ' T5 ' (  ' id ' int (ten) unsigned not NULL auto_inc Rement,  PRIMARY KEY (' id ')) engine=myisam auto_increment=10 DEFAULT charset=utf81 row in Set (0.00 sec) MySQL [BOSCO]&G T Update T5 set id=12 where id=5;   # # will increment the column from small to large, and greater than the current auto_increment, the same is no problem query OK, 1 row affected (0.03 sec) Rows matched:1  changed:1  Warnings: 0MySQL [bosco]> Show CREATE TABLE t5\g*************************** 1. Row ***************************       table:t5create table:create Table ' T5 ' (  ' id ' int (ten) unsigned not NULL auto_inc Rement,  PRIMARY KEY (' id ')) engine=myisam auto_increment=13 DEFAULT charset=utf81 row in Set (0.00 sec) Here automatically modifies the latest auto_ Increment becomes 13.


As can be seen, the update self-increment of the MyISAM table is not at risk.

3. InnoDB table
MySQL [bosco]> CREATE TABLE ' T6 ' (    -   id ' int ') 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 InnoDB table update self-increment column, from big to small
MySQL [bosco]> Show CREATE TABLE t6\g*************************** 1. Row ***************************       table:t6create table:create Table ' T6 ' (  ' id ' int (ten) unsigned not NULL auto_inc Rement,  PRIMARY KEY (' id ')) engine=innodb auto_increment=10 DEFAULT charset=utf81 row in Set (0.00 sec) MySQL [BOSCO]&G T 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 (ten) unsigned not NULL auto_inc Rement,  PRIMARY KEY (' id ')) engine=innodb auto_increment=10 DEFAULT charset=utf81 row in Set (0.00 sec)


Visible, when the InnoDB table update self-increment column is changed from a large value to a small value, there is no risk other than the possibility of a duplicate data modification failure.

3.2 InnoDB table update self-increment column, 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 (ten) unsigned not NULL auto_incr Ement, PRIMARY KEY (' id ')) engine=innodb auto_increment=10 DEFAULT charset=utf81 row in Set (0.00 sec) MySQL [bosco]> u   Pdate T6 set id=12 where id=5; # # Will Self-increment column from small to large, and larger than the current auto_increment, which is equivalent to digging a pit of query OK, 1 row affected (0.03 sec) Rows matched:1 changed:1 warnings:0mysq L [bosco]> SELECT * from t6;+----+|  ID |+----+|  1 | | 4 | | |+----+3 rows in Set (0.01 sec) MySQL [bosco]> Show create TABLE t6\g*************************** 1. Row *************************** table:t6create table:create Table ' T6 ' (' id ' int (ten) unsigned not NULL auto_incr Ement, PRIMARY KEY (' id ')) engine=innodb auto_increment=10 default charset=utf81 row in Set (0.01 sec) table in the self-increment maximum is already 12, this upd Ate operation does not automatically modify the latest AUTO_increment becomes 13, then there will be a problem, after the increase to 12, there will be a conflict, resulting in data insertion failure: MySQL [bosco]> INSERT INTO T6 values (NULL), (NULL);   Query OK, 2 rows affected (0.03 sec) records:2 duplicates:0 warnings:0mysql [bosco]> insert into T6 values (NULL); # # error has occurred. ERROR 1062 (23000): Duplicate entry ' for key ' PRIMARY '

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

--Bosco qq:375612082

----END----
-------------------------------------------------------------------------------------------------------
Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal responsibility!


MySQL Use auto_increment column table notes update self-add column

Related Article

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.