mysql数据库主键设置了自增，但是，我把删除了一些数据，随后加的数据为什么会自增ID会接着已经删除了的

这是数据库主键自增的固然性质所决定的，数据删除后，主键还是会继续增加的，即主键使用过一次将不会再次使用。

例如：这个表中有10条数据，主键为1-10不间断的数字，那删除第十条数据，继续插入的话，id则会变成11，而不是10。通俗的说就是主键使用过一次将不会再次使用。

每次插入则不需要为主键设置参数，数据库会根据设置的递增条件，自动给出主键值。则第一次插入后主键为1，第二次为2，依次递增。

扩展资料：

Mysql、SqlServer主键自动增长的设置方法：

1、在mysql中把主键定义为自动增长标识符类型

如果把表的主键设为auto_increment类型，数据库就会自动为主键赋值。例如：

createtablecustomers(idintauto_incrementprimarykeynotnull,namevarchar(15))insertintocustomers(name)values("name1"),("name2")

2、在MSSQLServer中，如果把表的主键设为identity类型，数据库就会自动为主键赋值。例如：

createtablecustomers(idintidentity(1,1)primarykeynotnull,namevarchar(15))insertintocustomers(name)values("name1"),("name2")identity包含两个参数，第一个参数表示起始值，第二个参数表示增量。

参考资料来源：百度百科-主键约束

* G r e a t S Q L 社 区 原 创 内 容 未 经 授 权 不 得 随 意 使 用 ， 转 载 请 联 系 小 编 并 注 明 来 源 。

本文在测试 insert、insert ignore、replace into三种数据插入方式的时候，发现插入数据的时候在表内存在带有“唯一特性”的值重复的情况下三种语句的处理方式。最终发现了MySQL主键自增值“空洞”了

测试场景为MySQL 8.0：

1、建表，包含主键及唯一约束

2、写入初始测试数据

insert方式插入数据在处理过程中发生主键传统等错误时候，语句会被终止，并告知错误的原因。而使用insert ignore的方式进行数据插入，则会忽略插入错误的行继续插入没有问题的行记录，最终以warning进行提示。

在测试过程中惊奇地发现测试表中的主键自增列发生了改变，经过之前的 *** 作已经变成了7：

最后，replace into的方式导致如果插入数据是原值的情况，然后主键冲突，就对该主键的内容进行替换，如果唯一键冲突，唯一值所在行就会删除，重新插入新的行，如果都不冲突则正常插入数据。

上文测试了三种插入数据的方式，可是测试过程中发现插入失败的时候，自增列的自增值居然变大了。

为了更好地理解，首先让我们具体认识一下 AUTO_INCREMENT属性在不同的存储引擎当中，其自增值的保存策略有所不同：

可是理解了这个并不能马上理解现在的这个问题，我们知道当数据进行数据插入的时候，如果插入的数据中自增列不指定其值的时候，该列就会以当前自增值作为其值，如果指定其值就会插入指定的值，当然也有满足唯一的原则，同时插入指定值大于自增值时，自增值也会随之改变。而自增值使用的算法是以 auto_increment_offset参数决定开始，以auto_increment_increment决定步长来实现的,默认情况都是1：

那么，为什么会出现插入数据未成功，自增值却变大了的情况呢？原因很简单，用插入数据的流程来进行分析：

因为自增值的保存是在插入数据真正执行前完成的，因此就会出现这种问题了。

这个时候有人就会想了，可以把 AUTO_INCREMENT值改回去吗？简单测试一下：

显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值“卡”在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里id=4和id=6之间差了个5的值，将自增值改回5，当插入数据时，自增值就会插入5的值并且把自增值加1，问题就出现了，此时自增值再进行插入就违背了唯一的原则了

在生产环境中还存在很多类似的问题，如：

在插入过程中，开启了一个事务，在插入的时候发生了事务的回滚，当回滚后再次插入数据，发现自增值又出现了“空洞”，那么问题又来了，为什么在插入数据的时候发生了回滚，数据回滚了，自增值却没有回滚呢？为了更直观，继续测试，假设有两个事务。

测试前数据：

进行测试：

测试后数据：

发现还是“空洞”了，而且此时答案也十分清楚了，在不同事务在进行写入 *** 作的时候申请自增值，为了避免两个事务申请到相同的自增值，所以需要对其加锁，按照一定顺序进行申请自增值。根据前面的例子来看：

此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值“卡”在中间的情况了，以后有机会再继续聊聊自增锁的问题。

En j o y G r e a t S Q L : )

《深 入 浅 出 M G R 》 视 频 课 程

戳 此 小 程 序 即 可 直 达 B 站

https://www.bilibi l i . c om/medialist/play/136385008 2? business=space_collection&business_id=343928&desc=0

文 章 推 荐 ：

G r e a t S Q L 是 由 万 里 数 据 库 维 护 的 M y S Q L 分 支 ， 专 注 于 提 升 M G R 可 靠 性 及 性 能 ， 支 持 I n n o D B 并 行 查 询 特 性 ， 是 适 用 于 金 融 级 应 用 的 M y S Q L 分 支 版 本 。

G i t e e :

h t t p s : / / g i t e e . c o m / G r e a t S Q L / G r e a t S Q L

G i t H u b :

h t t p s : / / g i t h u b . c o m / G r e a t S Q L / G r e a t S Q L

h t t p s : / / s p a c e . b i l i b i l i . c o m / 1 3 6 3 8 5 0 0 8 2 / v i d e o

微 信 &Q Q 群 ：

可 扫 码 添 加 G r e a t S Q L 社 区 助 手 微 信 好 友 ， 发 送 验 证 信 息 “ 加 群 ” 加 入 G r e a t S Q L / M G R 交 流 微 信 群 ， 亦 可 直 接 扫 码 加 入 G r e a t S Q L / M G R 交 流 Q Q 群 。

当我们使用 MySQL 进行数据存储时，一般会为一张表设置一个自增主键，当有数据行插入时，该主键字段则会根据步长与偏移量增长（默认每次+1）。

下文以 Innodb 引擎为主进行介绍，使用自增主键的好处有很多，如：索引空间占比小、范围查询与排序都友好、避免像 UUID 这样随机字符串带来的页分裂问题等...

当我们对该表设置了自增主键之后，则会在该表上产生一个计数器，用于为自增列分配 ID 。

自增的值并不是保存在表结构信息内的，对于不同的版本它们有如下的区别：

计数器的值存储在内存中的，重启后丢弃，下一次将读取最大的一个自增ID往后继续发号。

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

计数器的值将会持久化到磁盘。在每次发号时都将写入 Redolog ，并在每个 Checkpoint 都进行保存，重启时候使用 Redolog 恢复重启之前的值。

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

可以预先确定插入行数的语句（像简单 insert 的语句包含多个 value 这种情况也是属于简单插入，因为在进行插入时就已经可以确定行数了）

预先不知道要插入的行数的语句（包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 语句，但不包括 plain INSERT ）

如果一个事务正在向表中插入值，则会产生表级的共享锁，以便当前事务插入的行接收连续的主键值。

当处于[ 传统模式 ]与[ 连续模式 ]时，每次访问计数器时都会加上一个名为 AUTO-INC 的表级锁

传统模式：锁只持有到该语句执行结束，注意是语句结束，不是事务结束

连续模式：批量插入时锁持有到该语句执行结束，简单插入时锁持有到申请完自增ID后即释放，不直到语句完成

通过调整 innodb_autoinc_lock_mode 配置项，可以定义 AUTO-INC 锁的模式，不同的模式对应的策略与锁的粒度也将不同。

当使用基于 Binlog 的复制场景时，对于 statement（SBR）同步模式下只有[ 传统模式 ]与[ 连续模式 ]能保证语句的正确性。

基于 row（RBR）行复制的情况下任何配置模式都可以。

执行语句时加 AUTO-INC 表级锁，执行完毕后释放

针对 Bulk Inserts 时才会采用 AUTO-INC 锁，而针对 Simple Inserts 时，则采用了一种新的轻量级的互斥锁来分配 auto_increment 列的值。

该模式下可以保证同一条 insert 语句中新插入的自增 ID 都是连续的，但如果前一个事务 rollback 丢弃了一部分 ID 的话也会存在后续 ID 出现间隔的情况。

来一个分配一个，不会产生 AUTO-INC 表级锁 ，仅仅会锁住分配 ID 的过程。

由于锁的粒度减少，多条语句在插入时进行锁竞争，自增长的值可能不是连续的。

且当 Binlog 模式为 statement（SBR）时自增 ID 不能保证数据的正确性

不一定，业务也不应该过分依赖 MySQL 自增 ID 的连续性，在以下三种情况下，并不能保证自增 ID 的连续性：

假设已存在数据{1,张三}，且张三所属的字段设置了唯一主键

此时再次插入{null,张三}时候，主键冲突插入失败，但表的计数器已由2变成了3

当下次插入{null,李四}的时候最终入库的会变成{3,李四}

在一个事务里进行数据的插入，但最后并没提交，而是执行了 Rollback 。那么计数器已递增的 ID 是不会返还的，而是被直接丢弃。

发生大量插入时可能会出现自增 ID 并不是连续的情况

当我们为表设置了自增主键后，自增 ID 的范围则与主键的数据类型长度相关。

如果没有一张表里没有设置任何主键，则会自动生成一个隐性的6字节的 row_id 作为主键，它的取值范围为 0 到 2^48-1。

row_id 是由一个全局的 dict_sys.row_id 参数进行维护的，所有没有主键的表都会用上它（并不是每一个表单独占一份 row_id list ）

那么针对这两种主键，则会有以下两种情况发生：

当自增 ID 到达上限后，受到主键数据类型的影响，计数器发放的下一个 ID 也是当前这个 Max ID ，当执行语句时则会提示主键冲突。

建议根据业务合理规划，在进行表设计时就选择适合的数据类型。

当然也可以直接选择 Bigint 类型，它的取值范围是无符号情况下：0到 2^64–1（18446744073709551615）

这里并不是指 bigint 类型一定不会用完，毕竟一个有范围的持续增长的值一定会有溢出的时候，只是说一般场景下它都是足够使用的。

当 row_id 使用完后则又会从 0 开始发放，此时新插入的数据将覆盖回 row_id=0 的数据行。

由于它并不产生错误，还会造成数据的覆盖写。所以我们平时还是尽量给表都设置一个合理的主键才是。

在实际业务场景中，ID 常常需要返回给客户端用来进行相关业务 *** 作。

假如我们有个 userinfo?uid=? 的 API 接口，而用户 ID 是自增的，这时会发生什么？

该接口通过简单的尝试就可以暴露出真实的业务用户总数，可以很方便的使用爬虫从1开始递增获取数据信息。

那么有的同学说，我既想使用自增 ID 带来的好处，也不想承受这种比较常见的问题，那该怎么办呢？

在输出或者获取前对指定字段进行可逆的转义 *** 作

优点：实现起来比较简单，无论单体业务或者分布式应用都无需考虑对数据源的解析，只需在客户端实现自己的转义与解析方法即可；

缺点：业务入侵较大，且需要前后端各个合作方确认统一的标准；如果转义方法有调整，变更影响面也会很大；字符串长度会随ID长度而变化，使用空位填充也会特别明显；

优点：由于采用了时间戳进行 ID 生成，该 ID 是有序的，对范围查询与排序都比较友好；

缺点：需要保证发号节点的高可用性；另外由于生成时依赖时间戳，需要考虑时钟回拨与时钟同步的问题；

维护一份 ID 与 hash 的映射字典，它可以存在于客户端本身，也可以依赖其他如 Redis 、ETCD 之类的组件

优点：hash 长度不会随着 ID 长度或值的变化而变化；可以根据已有的 hash code 来造布隆过滤器；

缺点：业务入侵较大，查询时同样需要先根据 hash key 找到对应的 ID 值；需要考虑选择合适的 hash 算法以及解决 hash 冲突或扩容的问题。

---