MySQL Use auto_increment column table notes update self-add column

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