How to ensure no gaps in auto_increment numbers?

Question

i have a problem case with auto_incrementing, this is my table i have first it was so smooth to incrementing id*

id*      name
1        name1
2        name2
3        name3
4        name4
5        name5
6        name6

but when I delete a record and insert a new record the id starts from 7.

id*      name
1        name1
2        name2
3        name3
5        name5
6        name6
7        name7

this is what i want to make:

id*      name
1        name1
2        name2
3        name3
4        name7
5        name5
6        name6

I would like a solution where every number is filled in, so if I delete a row the next autoinc number will be the number that I deleted not the next number higher.

Answer 1

First off, it's completely fine to have these gaps. There is no problem. It's just your OCD that forces you to think these numbers have to follow a pattern - they DON'T.

• auto_increment is not a PHP feature, it's MySQL feature
• auto_increment ensures every row gets a unique number. It doesn't deal with sequential numbers
• auto_increment works safely in concurrent environment - that means there are a lot of users connecting to MySQL and doing stuff, and all of them have to be able to deal with the database and not get the same id for identifying a row. This is done through a rather complex process and this is one of the reasons why auto_increment yields gaps
• auto_increment is used by InnoDB for physical organization of records on disk - it uses the feature of auto_increment and that one is producing a number that's larger than previous (that's what it does, larger than previous, not sequential). Using this, a b-tree is constructed and records are written in sequence on the hard drive. Tampering with auto_increment makes InnoDB rebalance the tree. It means it goes through records and recreates the index if you mess with it - that's something you don't want. Ever

When you think about it, what do you even get with sequential numbers? Nothing really, except your brain probably hurts less because there's some imaginary order.

For sequential numbers, use triggers to create them. auto_increment has one job and one job only - to produce unique numbers.

Answer 2

If you're trying to get something that looks like a list, I suggest you leave the field "ID" as is and add another field to use for names sorted numerically.

Anyway, you can get the same result with just a query like this:

SELECT name, @Rk := @Rk+1 AS Rank
FROM mynamestable, (Select @Rk := 0) AS Rk 

Edit: This query will return all records in the field name from the table mynamestable and also a column (named Rank) that will be a numeric incremental (starting from 1) so the result will be something like:

name    Rank
Name1    1
Name2    2
Name3    3

Answer 3

for it you can use trigger after delete. Update all ids (decrease 1) which greater than deleted id

CREATE TRIGGER update_ids AFTER DELETE ON test_table
FOR EACH ROW SET UPDATE test_table SET id = id - 1 WHERE id > OLD.id;

also you must reset auto_increment or write another trigger for insert, which update id to max(id)