Make automatic addition filled with previously deleted number

Question

Possible duplicate:
Using unused primary keys

I created a table with an id column with auto-increment Now that I have deleted the rows with id 8,12,30 in this table.

Now, when I insert a new record, I want to insert the next record in 8, then 12, etc.

Is there a way to get MySQL auto grow for this?

I tried to set auto-increment for a certain number, but I do not want to do this.

Answer 1

Instead, you do not want to use an auto-increment column. If you want to fill in the blanks, set it to an int column and process the logic in the stored proc or insert.

EDIT:

Since this is an int column, you can order them numerically. Just do SELECT Ids FROM Table Order By Ids to get all identifiers and check for spaces in the returned dataset.

There is probably a smoother way to do this, but you can loop the results with the cursor and compare with the INT variable, which increases throughout the loop. When you find a space (no match), break the loop and use this INT value as your INSERT id.

I will not write your code for you, but these are some steps for you to go in the right direction. It must be a truly basic programming bit that you must deal with.

Hope this helps.

EDIT # 2:

As others have pointed out, your best move is simply to leave spaces. If there is no cap on it in length, and identifiers SHOULD be 1-30 (strange), leave it alone. There is no benefit to filling in the gaps.

EDIT # 3:

Another thing to keep in mind: if you really need to leave 1-30 for some reason, don't delete your lines. Add a column to mark each row as active or not, and then just update the rows that are inactive when you need to, and then mark them as active. This is VERY hacked, but your requirement is a bit hacky, so ...

Answer 2

http://sqlfiddle.com/#!8/3f071/1

Set @x smallest unused value:

 SET @x = ( SELECT MIN(t1.id+1) FROM t t1 LEFT JOIN t t2 ON t2.id = t1.id + 1 WHERE t2.id IS NULL ); INSERT INTO t(id) VALUES (@x); 

Set @x smallest unused value that is in the space, otherwise return NULL to use the MySQL auto-increment mechanism:

 SET @x = ( SELECT IF( MIN(t1.id-1) = 0, NULL, MIN(t1.id-1) ) FROM t t1 LEFT JOIN t t2 ON t2.id = t1.id - 1 WHERE t2.id IS NULL ); INSERT INTO t(id) VALUES (@x); 

Of course, you must wrap them in a transaction if you do not want to shoot yourself in the foot or wherever you want.

Answer 3

well AUTO_INCREMENT behaves like this. it does not fill in the blanks in your deleted data. The best way to do this is to create logic that will satisfy your needs (filling in the blanks).