如何确保 auto_increment 数字没有间隙? [英] How to ensure no gaps in auto_increment numbers?

问题描述

我有一个 auto_incrementing 的问题案例，这是我的第一个表，它很顺利地增加 id*

i have a problem case with auto_incrementing, this is my table i have first it was so smooth to incrementing id*

id*      name
1        name1
2        name2
3        name3
4        name4
5        name5
6        name6

但是当我删除一条记录并插入一条新记录时，id 从 7 开始.

but when I delete a record and insert a new record the id starts from 7.

id*      name
1        name1
2        name2
3        name3
5        name5
6        name6
7        name7

这是我想做的:

id*      name
1        name1
2        name2
3        name3
4        name7
5        name5
6        name6

我想要一个解决方案，其中每个数字都被填写，所以如果我删除一行，下一个自动编号将是我删除的数字，而不是下一个更高的数字.

I would like a solution where every number is filled in, so if I delete a row the next autoinc number will be the number that I deleted not the next number higher.

推荐答案

首先，有这些差距完全没问题.没问题.只是你的强迫症迫使你认为这些数字必须遵循某种模式 - 他们没有.

First off, it's completely fine to have these gaps. There is no problem. It's just your OCD that forces you to think these numbers have to follow a pattern - they DON'T.

• auto_increment 不是 PHP 特性，而是 MySQL 特性
• auto_increment 确保每一行都有一个唯一编号.它不处理序列号
• auto_increment 在并发环境中安全工作 - 这意味着有很多用户连接到 MySQL 并做一些事情，而且他们都必须能够处理数据库而不是相同用于标识行的 id.这是通过一个相当复杂的过程完成的，这也是 auto_increment 产生间隙的原因之一
• auto_increment 被 InnoDB 用于磁盘上记录的物理组织 - 它使用了 auto_increment 的特性，并且产生一个数字这比以前大(这就是它所做的，比以前大，不是连续的).使用它，构建了一个 b 树，并将记录按顺序写入硬盘驱动器.篡改 auto_increment 会使 InnoDB 重新平衡树.这意味着它会遍历记录并在您弄乱它时重新创建索引 - 这是您不想要的.曾经

• auto_increment is not a PHP feature, it's MySQL feature
• auto_increment ensures every row gets a unique number. It doesn't deal with sequential numbers
• auto_increment works safely in concurrent environment - that means there are a lot of users connecting to MySQL and doing stuff, and all of them have to be able to deal with the database and not get the same id for identifying a row. This is done through a rather complex process and this is one of the reasons why auto_increment yields gaps
• auto_increment is used by InnoDB for physical organization of records on disk - it uses the feature of auto_increment and that one is producing a number that's larger than previous (that's what it does, larger than previous, not sequential). Using this, a b-tree is constructed and records are written in sequence on the hard drive. Tampering with auto_increment makes InnoDB rebalance the tree. It means it goes through records and recreates the index if you mess with it - that's something you don't want. Ever

当您考虑一下时，您甚至可以从序列号中得到什么?真的没什么，除了你的大脑可能会受到较少的伤害，因为有一些想象的顺序.

When you think about it, what do you even get with sequential numbers? Nothing really, except your brain probably hurts less because there's some imaginary order.

对于序列号，使用触发器来创建它们.auto_increment 有一份工作，只有一份工作 - 生成唯一数字.

For sequential numbers, use triggers to create them. auto_increment has one job and one job only - to produce unique numbers.

这篇关于如何确保 auto_increment 数字没有间隙?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！