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