Why mysql autoincrement increments the last used id rather then the last existing id

Question

I am using mysql, and am looking at a strange behavior. Scenario : I have a table having table_id as primary key, which is set to auto-increment.

table_id  more_columns
1         some value
2          others

Now if i delete row 2, and insert one more row, the table_id becomes 3 (Expected is 2)

table_id  more_columns
    1         some value
    3          recent

Why is it so? Here I am loosing some ids (I know they are not important). Please put some lights on this behavior

mysql internally stores the last autoincremented id per column. if you delete a row (might be the first or a row inbetween) it will not update that internal value — knittl, Jan 10 '14 at 07:08
Yes i agree, but i wanted to know what is the logic behind this — pratim_b, Jan 10 '14 at 07:10
Logic behind this: http://stackoverflow.com/questions/13643938/mysql-delete-autoincrement — Aaron Blenkush, Jan 10 '14 at 07:12
thanks i got the answer. Possible duplicate. did not came up when i googled. — pratim_b, Jan 10 '14 at 07:13

score 1 · Answer 1 · edited May 23 '17 at 12:17

1

In auto-increment field If a row is deleted, the auto_increment column of that row will not be re-assigned.

Please see here for more information.

For reasons why auto-increment doesn't use deleted values you can refer here(mentioned in comments by @AaronBlenkush).

edited May 23 '17 at 12:17

Community

1
1

answered Jan 10 '14 at 07:08

Bhushan

6,151
13
58
91

yes, but i am expecting some information on the logic behind this – pratim_b Jan 10 '14 at 07:12

score 0 · Answer 2 · answered Jan 10 '14 at 07:14

A MySQL auto_increment column maintains a number internally, and will always increment it, even after deletions. If you need to fill in an empty space, you have to handle it yourself in PHP, rather than use the auto_increment keyword in the table definition.

Rolling back to fill in empty row ids can cause all sorts of difficulty if you have foreign key relationships to maintain, and it really isn't advised.

The auto_increment can be reset using a SQL statement, but this is not advised because it will cause duplicate key errors.

-- Doing this will cause problems!
ALTER table AUTO_INCREMENT=12345;

EDIT To enforce your foreign key relationships as described in the comments, you should add to your table definition:

FOREIGN KEY (friendid) REFERENCES registration_table (id) ON DELETE SET NULL;

Fill in the correct table and column names. Now, when a user is deleted from the registration, their friend association is nulled. If you need to reassociate with a different user, that has to be handled with PHP. mysql_insert_id() is no longer helpful.

If you need to find the highest numbered id still in the database after deletion to associate with friends, use the following.

SELECT MAX(id) FROM registration_table;

After delete write this query

ALTER TABLE tablename AUTO_INCREMENT = 1

if i have foreign key relation , i will not be able to delete — pratim_b, Jan 10 '14 at 07:34

score 0 · Accepted Answer · answered Jan 10 '14 at 07:32

0

The auto_increment value is a counter stored internally for each table. The counter is only increased, never decreased. Every syntactically correct INSERT statement fired against the table increments this counter, even when it is rolled back and also when you define an insert value for the primary key.

answered Jan 10 '14 at 07:32

Michel Feldheim

17,625
5
60
77

is there a way to get this internally stored counter? – pratim_b Jan 10 '14 at 07:43
@techG: You can get next assignable counter value from `AUTO_INCREMENT` column of `INFORMATION_SCHEMA.TABLES`. – Ravinder Reddy Jan 10 '14 at 08:23

Why mysql autoincrement increments the last used id rather then the last existing id

3 Answers3