how to make my own auto increment in php?

Question

The in-built Auto-Increment in MySQL doesn't meet my requirement so I'm thinking about making a new one. Here is my requirement:

1. Create an increment serial number
2. Able to insert row (record) to missing number. For instance, I've 5 rows and then delete the 3rd row. After that, I insert another 2 new row. I expect one of it will be insert to the 3rd row position.

My idea is to use the loop to check all rows in the table. If it ever finds a missing position, it will add (a) new row(s) to the missing position. Otherwise, it will continue add new rows at the end of table.

And as you can see, the idea only works with a very small table. If the table's extended to, let say, 10MB. Then the server will got a huge trouble.

I wonder if anyone got a better algorithm, please enlighten me.

Answer 1

Filling gaps in the auto-increment column is generally not necessary, and more trouble than it's worth. The auto-increment is not a row number. It doesn't need to be consecutive, it only needs to be unique.

One of the problems you will find if you want to fill gaps is the race condition. That is, in the millisecond between your PHP script finding a gap to use, and inserting into that gap, another PHP request might be doing the same thing, find the same gap, and go to fill it.

To solve this, your PHP script must lock the entire table before searching for a gap to use. Since you're searching for a gap, where no row exists with a given value, you can't lock any row. You have to lock the whole table, because you don't know before you search where the gap lies (if any gap even exists).

Resorting to table-locking is a costly sacrifice, because it means only one PHP request can insert at a time. This becomes a bottleneck for your application scalability.

Now about your implementation. How to find a missing number? The numbers are in the database, so you can query to find any id where the preceding id is not in the table.

SELECT t1.id FROM mytable AS t1 
LEFT OUTER JOIN mytable AS t1 ON t2.id = t1.id - 1
WHERE t2.id IS NULL
ORDER BY t1.id
LIMIT 1

The alternative is to keep some kind of cache of all id's in your application. But that also means every concurrent PHP request needs access to the cache, and some ability to lock the cache so only one PHP request at a time can search it and update it.

Either way, you have created a bottleneck for your application.

I wrote more about this as chapter 22 of my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Answer 2

In practice, you would have to be very careful and really know what you are doing because filling up the 'gaps' in the id column that way could break the referential integrity of the entire database or system where the id's in that table are referenced by other tables.

Otherwise, a quick way is to first fill up those 'empty' ids by existing rows using mysql, e.g. using phpmyadmin, with something like the following, after ensuring that the table is sorted on the id column in ascending order:

 SET @count = 0;
 UPDATE the_table SET id = @count:= @count + 1;
 #Then after this you do your insert operations.

The above will update all existing ids to a sequential order, and the item you insert will be just be auto_incremented to an id equal to the total number rows in the table.

But if you want to leave the already non-empty ids intact, then you could do something like this in PHP:

/* Assume that you want to insert into a table called the_table with columns id, col1, col2, col3 a new row with values for the three cols 
$value1, $value2, $value3 respectively, using an existing 'gap' in the id numbering: */

/* Get an array of all present ids: */
$arr = []; 
$q1 = mysqli_query($con,"SELECT id from the_table");
while(list($id) = mysqli_fetch_array($q1)){
$arr[] = $id;
} 

/* Get the currently largest id in the table as $largest_id */
$q2 = mysqli_query($con,"SELECT MAX(id) from the_table");
list($largest_id) = mysqli_fetch_array($q2);

/* Loop through all integers up to $largest_id + 1 */
/* And do the insert operation just one time, once you find a number not in $arr */
/* Use the $not_yet_inserted variable to break out of the loop */
$not_yet_inserted = true;
for($j = 1; $j <= $largest_id + 1; $j += 1){
    if(!in_array($j,$arr) && $not_yet_inserted ){ 
    mysqli_query($con,"INSERT INTO the_table (id, col1, col2,col3) values ('$j','$value1','$value3,'$value3'')");
    $not_yet_inserted = false;
    }
}

Answer 3

You can use binary search kind of algorithm.

First get all the IDs inserted in database. Then compare the greatest id with the length of list. If both are same then insert the row with next id. If not then compare the id at half of list and length/2. Now if both are same it means the missing id is after the first half of list else it is in the first half of the list. Hope you can understand what I am trying to say.

Answer 4

dont delete rows. include a boolean field 'reuseflag' = false on insert.

to 'delete' row, set reuse-flag to true and change your row-selects to only include a row if reuseflag = false

then when you want to reuse a row, find first where reuseflag=true, insert the new values and change reuseflag to false before you update the row.

this will be faster than any other way you can do it.