The key is added out of order, can it be changed?

Question

MySQL

there is a table with the key (auto increment, not null), there are, suppose 3 records with the key 1,2,3 and if you add another one with the key 7 (1, 2, 3, 7) and delete it later, then when you add records without specifying a key, then the key will be set as 8 (1, 2, 3, 8) and not 4, can I fix it somehow?

Answer 1

Hold: befused.com/mysql/reset-auto-increment
In general, a couple of weeks ago there was the same issue and everything was solved with 1 line in the configuration. If I find it, I'll update the answer.

Answer 2

ALTER TABLE `tbl` AUTO_INCREMENT = 4;

Answer 3

what is a key in a table? This is data that adds uniqueness to the field. And there is absolutely no need to violate this security during operation - you risk losing connections with other tables. There are, of course, all sorts of methods about cascading, but usually those who use cascading do not deal with such decisions.
If you are worried that there will not be enough numbers in the key for your age, then first count the number of possible options in integer form. 32-bit gives 4294967295 options. Even if one record is added per second, then these values ​​will be enough for 4294967295/(60*60*24) = 49710 days = 136 years.
But even in such cases, they simply increase the bit depth. For example, 64 bits gives the number 9.223372e+18. Calculate how long this size will last.
But we are talking about a muscle that has its own cockroaches. And in particular: when declaring a field of type int, a size of 10 decimal places is generated. It contains the maximum value 9999999999, which is approximately 317 years.