ID as auto increment Question

neobie

We normally define a row of data with its unique ID which I set it as (INT, NOT NULL, AUTO INCREMENT, PRIMARY KEY) when we kept on adding new data, ID will be increasing.
ID
1
2
3
4
5

If I delete a data with ID = 3, then add a new data, it will continue with ID=6. But I hope that the sql will search for the 'missing' ID (in this example it is 3) then define the data with ID=3, instead of 6.

Is it possible I can do that? I felt terrible when looking at the non-stop increasing ID as I delete and add data :p . Thanks.

bretticus

ALTER TABLE YourTable AUTO_INCREMENT = 3

neobie

I do the application with php so I don't plan to alter the table in mysql.. so how can I do that

bretticus

The SQL syntax I left for you can be ran in php. In fact, any SQL syntax can be passed to the database.

Such as...

$result = mysql_query("SELECT MIN(id) FROM `YourTable`");
$minid = mysql_result($result, 0);
if (is_int($minid)) {
   $minid++;
   mysql_query("ALTER TABLE `YourTable` AUTO_INCREMENT = $minid");
}

neobie

It's weird.. My data having ID=1,3,4 (ID=2 is deleted)
I tried with mysql command:
ALTER TABLE YourTable AUTO_INCREMENT = 2
But the newly added data still continue with ID=5

laserlight

Why not just ignore this? After all, what the value of the primary key is doesnt matter, so long as it is unique and can be used as a primary key.

bretticus

That command will only work when there's not an auto incremented id that is greater. I used the MIN() function for that reason. In other words, you can't fill in the blanks. I guess if you really want to, you could query every record (row) in the table (well, just the ids), loop through each one, increment a counter variable and break when you find an id that doesn't match the value of the counter variable. Then pass that counter value as an explicit id in your INSERT statement. If you loop all the way through, just leave out the id column in the INSERT and it will take the next one (or use my MIN(id) suggestion at this point.)

However, I'm with laserlight. That's alot of overhead for something as trivial as a a primary key.