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?
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;