innodb-auto-increment-traditional

This page has moved or been replaced. The new page is located here:

http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

Please update any bookmarks that point to the old page.


User Comments
  Posted by Wagner Bianchi on June 25, 2015
If for some sort of functionality one need to update the PK's value using the LAST_INSERT_ID(), take care to run the SHOW TABLE STATUS to update the dictionary ai_col to avoid a PK violation.

mysql> truncate table tb01;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into tb01 (a,b) values (0,'wbjr');
Query OK, 1 row affected (0.01 sec)

mysql> select a,b from tb01;
+---+------+
| a | b |
+---+------+
| 1 | wbjr |
+---+------+
1 row in set (0.00 sec)

mysql> update tb01 set a=LAST_INSERT_ID(a+1) where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into tb01 (a,b) values (0,'wbjr');
ERROR 1062 (23000): Duplicate entry '2' for key 'a'

#: here it seems that the LAST_INSERT_ID() was updated with the last one
#: but not the internal ai_col

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

mysql> show table status like 'tb01'\G
*************************** 1. row ***************************
Name: tb01
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: 2015-06-25 11:47:29
Update_time: 2015-06-25 11:48:20
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

#: after running the SHOW TABLE STATUS, the ai_col get the table's MAX(ai_col)

mysql> insert into tb01 (a,b) values (0,'wbjr');
Query OK, 1 row affected (0.04 sec)

mysql> select a,b from tb01;
+---+------+
| a | b |
+---+------+
| 2 | wbjr |
| 3 | wbjr |
+---+------+
2 rows in set (0.00 sec)
Sign Up Login You must be logged in to post a comment.