The auto_increment column is still not continuous after rebuilding in MySQL table

3593 views mysql
0

There is a table named "web_file" with an auto_increment column "num_id" in Mysql (version 8.0) database. Because of some delete operations, the "num_id" is not continuous. So I drop the original "num_id", and add a new auto_increment column still named "num_id" started from 1. But I find the maximum value of the new "num_id" isn't equal with the count of the rows. Here is my sql code:

alter table web_file drop column num_id;
alter table web_file add column num_id int not null auto_increment unique key;
select min(num_id), max(num_id), count(num_id) from web_file;

This is the result:

1   664291  662902

I have tried to execute the sql code several times, but the result are all the same every time. In my opinion, when a column is auto_increment continuously, its max value should be equal with the number of rows. So, what's wrong leading to this exception?

answered question

The short version is you should never expect anything apart from uniqueness from an auto_increment column. Various aspects of conflict resolution, preallocation, transaction rollback and cause gaps. If you can say why you want it continuous perhaps another solution can be provided. btw dba.stackexchange.com is good for these questions/answers.

1 Answer

7

What you are seeing is not really an exception so much as it is just the way auto increment columns in databases behave. The contract for an auto increment column is that when a new id gets added, it sequentially follows the previous greatest value, and that all id values are guaranteed to be unique. There is no guarantee that the sequence will be continuous.

If you require a continuous sequence, then you may use the ROW_NUMBER analytic function (MySQL 8+ and later):

SELECT *, ROW_NUMBER() OVER (ORDER BY num_id) rn
FROM web_file;

On earlier version of MySQL, you could use a correlated subquery:

SELECT *,
    (SELECT COUNT(*) FROM web_file w2 WHERE t2.num_id <= t1.num_id) rn
FROM web_file t1;

posted this

Have an answer?

JD

Please login first before posting an answer.