-1

I am not experienced in this area. Can you tell me why autoincrement skips values?

This is my table:

CREATE TABLE `orders` 
(
   `id` int NOT NULL AUTO_INCREMENT,
   `product_id` int NOT NULL,
   `branch_offices_id` int NOT NULL,
   `product_queue` int NOT NULL,
   `quantity` int DEFAULT NULL,
   `curdate` date NOT NULL,
   `active` int NOT NULL DEFAULT '1',
   `del` int NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Here is the SQL query

INSERT INTO orders (branch_offices_id, product_id, product_queue,quantity,curdate)
    SELECT 
        1 AS branch_offices_id,
        id, queue, 0 AS quantity, CURDATE() 
    FROM 
        Product

There are no server restarts during and between insert requests, there are no triggers or stored procedures.

I run the code (that I showed) manually through mysql workbench (I just change manually the branch_offices_id values 1,2,3 ... etc in SELECT statement), There were no crashes, All fields received from SELECT are recorded. 15 missing values appear before reinserting. enter image description here

beslana
  • 1
  • 3
  • 2
    Does this answer your question? [Auto Increment skipping numbers?](https://stackoverflow.com/questions/17798835/auto-increment-skipping-numbers) or (from: dba.stackexchange: [What could cause an auto increment primary key to skip numbers?](https://dba.stackexchange.com/questions/62151/what-could-cause-an-auto-increment-primary-key-to-skip-numbers)) – Luuk Jul 25 '23 at 16:50
  • try this ALTER TABLE orders AUTO_INCREMENT = 1; – Smordy Jul 25 '23 at 16:52
  • 2
    `(1,2,...etc)` is not valid SQL (sigh). Please provide the actual statement that you are using. ( or make it a [mre]) – Luuk Jul 25 '23 at 16:54
  • I don't think the dup ( https://stackoverflow.com/questions/17798835/auto-increment-skipping-numbers ) is the answer. In @Luuk's case, 128 is a power of 2; I would guess that it pre-allocated that many ids so that it would not have to keep locking the table to get the next id. – Rick James Jul 25 '23 at 22:36
  • Anyway, `AUTO_INCREMENT` values are only guaranteed to be distinct. "Gap-free" is _not_ promised. Insert ignore, Replace, Delete, and many other things also create [unnecessary] gaps. Live with it! – Rick James Jul 25 '23 at 22:39
  • @RickJames: no it does not seem to happen at `a power of 2`, but at an interval of `n*128-(n-1)`, which is `128, 255, 354, 481`, see: https://dbfiddle.uk/M5ul7mYk – Luuk Jul 26 '23 at 18:03
  • @Luuk - OK. But I will still _guess_ that the Optimizer is somehow deciding to grab a batch of ids. (For the hidden 6-byte number that you get when you do not provide a PK, it grabs in chunks of 256.) – Rick James Jul 26 '23 at 18:06
  • @RickJames: I do not mind any gaps in the autoincrement sequence, as long as it gives unique numbers. (and it does do that, so .....) – Luuk Jul 26 '23 at 18:11
  • @Luuk - The OP seems to care about the gaps. You and I are on the same wavelength. – Rick James Jul 26 '23 at 22:36

1 Answers1

0

Please study this reproduction of an interval created by AUTO_INCREMENT.

see: DBFIDDLE

create table orders (id int not null auto_increment primary key, product int not null);

insert into orders(product)
with recursive cte as (
  select 1 as x
  union all
  select x+1 from cte where x<100
  )
select * from cte;

-- AUTO_INCREMENT= (is not 100)
show create table orders;

-- But max value is 100
select max(id) from orders;

-- insert more values
insert into orders(product)
with recursive cte as (
  select 1 as x
  union all
  select x+1 from cte where x<100
  )
select * from cte;

-- find a gap
select *
from (
select 
  id,
  lead(id) over (order by id) nextValue
from orders) x
where abs(nextValue - id)<>1;

P.S. I am just showing there is a gap, not trying to explain why the gap is 28 ....

Luuk
  • 12,245
  • 5
  • 22
  • 33