Insert into on duplicate key - auto increment id skipped

I currently have an SQL execution script which updates the row on duplicate key which looks like this.

    $stmt = $dbCon->prepare("INSERT INTO videos_rating (videos_rating_video_fk, "
            . " videos_rating_user_fk, "
            . " videos_rating_rating) "
            . " VALUES (:video_id, "
            . " :user_id, "
            . " :video_rating) "
            . " ON DUPLICATE KEY UPDATE videos_rating_rating = :video_rating");

The script works fine but is there a way to prevent the auto increment column of getting out of sync?

Lets assume we start with an empty table, i then rate a video which then creates a row which will get the id of 1, then the user execute the SQL again by rating the same video a lower or higher rating and the row will be updated because its now a duplicate key, sure no problem.

The problem is this.

Next time another user rates a new new video the row will now begin at id 3 and not 2?

The table will then look like this

id | videos_rating_user_fk | videos_rating_rating
1  | 1                     | 4
3  | 2                     | 5

I were not able to find a similar question even tho i find it higly unlikely that no one else has been bothered with this, if so please refer me over to that post.

I know ids are not supposed to 'look good' but it is very annoying that ids jump from 30 - 51 - 82 - 85 - 89 etc and would there not be a problem at some point when the maximum UNSIGNED big int number is reached? im not saying i will ever go that high but still.

Solution

I assume that you are using the default InnoDB engine. In that case the "problem" is that the engine will "reserve" the id before it knows if it's a duplicate or not. Once the id is "reserved" it cannot be released, because another thread (another user) might perform an insert into the same table at the "same" time. There are also other ways to get gaps in the AUTO_INCREMENT column without deleting any rows. One is when you roll back a transaction.

You can try to "reset" the next AUTO_INCREMENT value after every insert with

alter table videos_rating auto_increment = 1;

But I can't say what problems you might run in executing this statement in a running live environment. And I'm not going to find that out.

Note that this is usually not an issue, because tables on which you run IODKU statemts (usually) don't need an AUTO_INCREMENT column. As Cid wrote in his answer, you can just drop the id column and define your unique key as primary key.