innodb 缺少自动增量值 [重复]-解网

问：

4个月前关闭。

我在这方面没有经验。你能告诉我为什么自动增量会跳过值吗？

这是我的表格：

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

下面是 SQL 查询

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

在插入请求期间和之间没有服务器重新启动，没有触发器或存储过程。

我通过mysql工作台手动运行代码（我展示的）（我只是手动更改值 1,2,3 ...等在语句中），没有崩溃，记录了从SELECT接收的所有字段。重新插入之前出现 15 个缺失值。在此处输入图像描述branch_offices_idSELECT

mysql select sql-insert innodb 自动增量

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. 我只是在展示存在差距，而不是试图解释为什么差距是 28 ....😉

innodb 缺少自动增量值 [重复]

innodb missing auto-increment values [duplicate]

评论

评论