After insert into table the index of the table seems to jump randomly

I frequently have to insert the contents of one (temporary for revision) table into another "permanent" one as part of a regular import process. An example of the code I use, which seems to work fine, is

INSERT INTO fish.pca_full (fund, isin, exch_epic, risk_profile_sector, ia_sector, Instances, MaxInstances, Accumulated_Value, Units, End_Price, Valuation_Date, Start_Price, Start_Date, Value_added, feestructure, a_m_charges, acc_inc, inst_retail, ut_oeic, fundtype, currency, TIDM)

SELECT fund, isin, exch_epic, risk_profile_sector, ia_sector, Instances, MaxInstances, Accumulated_Value, Units, End_Price, Valuation_Date, Start_Price, Start_Date, Value_added, feestructure, a_m_charges, acc_inc, inst_retail, ut_oeic, fundtype, currency, TIDM FROM fish.pca_temp ORDER BY Valuation_Date, MaxInstances, risk_profile_sector, currency, exch_epic, Value_added ;

The index of fish.pca_full is ID and is an auto-incrementing integer so, as desired, the column will automatically be filled by the addition of the new records.

Suppose the ID value of fish.pca_full was 10,000 and we inserted 100 records from fish.pca_temp I expect the next default ID value will rise to 10,101 which it might. However if may also be e.g. 10,397 and it will be found there is a gap from 10,100 (the last ID assigned) to 10,297 (I just picked that number at random, it does not appear to be related to the number of records imported).

If there are no records in the gap I can reset the NEXT ID to be 10,101 and adding another set of records works fine (or may introduce another random gap at the end).

Perhaps there is a good reason why the next ID after the INSERT INTO operation seems to be randomly set but if so I can't work out why. More importantly from my perspective, how to stop the unwanted gap or automatically resetting the default next ID value.

One way might be to count the number of records after the insert and then try to set the next value to that plus 1 but is that the right approach?

Is it, as I suspect, a bug? Hence this bug report?

Any helpful suggestions / explanations gratefully received.

MySQL has internal counters for auto-incremented columns. If I recall right it reserves one id when doing an update via INSERT INTO ... ON DUPLICATE UPDATE ... although not required.

One reasonable way to trim the next auto-increment id of a table is such a query:

ALTER TABLE mytable AUTO_INCREMENT=0;

The same can be done in HeidiSQL's bulk table editor:

will try that, thank you.