MySQL中的主键自增机制详情

作者:昨天的风 时间:2024-01-18 23:50:54 

主键自增

MySQL 提供了主键自增机制 AUTO_INCREMENT. 对主键使用, 保证了主键的唯一性.

注意:自增长必须与主键字段配合使用

默认的主键的起始值为 1, 每次增量为 1, 也可以手动指定其自增起始值 auto_increment_offset 和自增步长 auto_increment_increment.

-- 设置主键自增
CREATE TABLE USER(
   id INT UNSIGNED AUTO_INCREMENT,
   ...
)

-- 在创建表时指定自增起始值
CREATE TABLE xx{

}ENGINE=INNODB auto_increment=100 DEFAULT CHARSET=utf8;

-- 修改自增起始值
alter table people  AUTO_INCREMENT = 20;

-- 基于会话级别:
show session variables like 'auto_inc%';-- 查看步长
set session auto_increment_increment=2;   -- 修改自增步长

-- 基于全局级别:(会影响下一次登录的值)
show global variables like 'auto_inc%';-- 查看步长
set global auto_increment_increment=5;-- 修改自增步长

注意:

  • 在 InnoDB 存储引擎中, 自增长值的列必须是索引, 同时必须是索引的第 1 个列. 如果不是第 1 个列, 则 MySQL 数据库会抛出异常. 对于 MyISAM 无此要求。

  • 自增长每次递增 1, 说明是数值型, 可以是整数, 也可以是浮点数.

  • 由于种种原因, 自增值可以保证增长趋势, 但并不能保证连续.

  • 不会影响自增长的命令:DELETE FROM xxx;

  • 可以影响自增长的命令:TRUNCATE TABLE xxx;

使用起来倒是很简单,但是对于主键自增机制的这些问题,你了解吗?

  • 自增主键保存在哪里?

  • 自增主键如何实现自增的?

  • 自增主键是什么时候自增的?

  • 自增主键一定是连续自增的吗?

  • 自增主键可以人为修改吗?

自增主键保存在哪里

首先需要知道的是,自增主键机制是存储引擎实现的,所以不同的存储引擎对于自增值的保存策略不同.

  • MyISAM 的自增值保存在数据文件中.

  • InnoDB 的自增值, 保存在内存里, 一直到了 MySQL 8.0 后, 才有了自增值的持久化的能力, 也就是才保存到文件中,实现了如果发生重启, 表的自增值可以恢复为 MySQL 重启前的值.

具体是:在 MySQL <= 5.7 时, 自增值保存在内存里, 没有持久化. 当 MySQL 重启后, 第一次打开某个数据表的时候, 都会去找该表中主键字段的自增值的最大值 max(id), 然后将 max(id)+1 作为这个表当前的自增值.

但是这样就会存在一个问题,比如 :  如果一个表的 id 最大是 10, 此时的 AUTO_INCREMENT=11. 当删除 id=10 的记录时, 此时 AUTO_INCREMENT 还是 11. 但如果马上重启 MySQL, 重启后这个表的 AUTO_INCREMENT 就变为 10 了. ( maxid = 9, 9+1=10 )  即 MySQL 重启可能会修改表的 AUTO_INCREMENT 的值.

在 MySQL 8.0 版本, 将自增值的变更记录在了 redo log 中, 重启的时候依靠 redo log 恢复重启之前的值. 所以不会出现上述问题.

自增值修改机制

在 MySQL 中, 如果字段 id 被定义为 AUTO_INCREMENT, 在插入一行数据的时候, 自增值的操作如下:

  • 如果插入数据时, id 字段指定为 0, null 或未指定值, 那么就把这个表当前的 AUTO_INCREMENT 值给自增字段, ( 没给值, 则使用自增长值 )

  • 如果插入数据时, id 字段指定了具体的值, 就直接使用 SQL 语句里指定的值. ( 给定了值, 就使用给定值 )

  • 根据要插入的值和当前自增值的大小关系, 自增值的变更结果也会有所不同.

假设, 要插入的值是 X, 当前的自增值是 Y.

如果 X < Y, 那么这个表的自增值不变.

如果 X &ge; Y, 就需要把当前表的自增值修改为新的自增值. 新的自增值生成方式是:从 auto_increment_offset 开始, 以 auto_increment_increment 为步长, 持续叠加, 直到找到第一个大于 X 的值, 作为新的自增值.   也就是,这种情况下步长也参与了影响。

自增值的修改时机

假设, 有一个表 t , 有 A, B, C 三个字段, 字段 A 是主键, 且自增, 字段 C 有唯一约束.

当前表 t 中有一条记录为  (1,1,1) , 此时执行一条插入语句 insert into t values(null, 1, 1);

那么这个语句的执行流程就是:

  • 执行器调用 InnoDB 引擎的接口,写入一行, 传入的这一行的值是 (null,1,1);

  • InnoDB 发现用户没有指定自增 id 的值, 所以会获取表 t 当前的自增值 2,

  • 然后 InnoDB 会将传入的行的值改成 (2,1,1);

  • 然后将表的自增值改成 3,

  • 然后执行插入数据操作, 由于 C 字段已经存在 = 1 的记录, 所以报 Duplicate key error, 并返回.

最后的结果可以看到, 这个表的自增值改成 3, 是在真正执行插入数据的操作之前进行的.

这个语句真正执行的时候, 因为碰到唯一键 C 冲突, 所以 id=2 这一行并没有插入成功, 但也没有将自增值再改回去. 所以, 在这之后, 再插入新的数据行时, 拿到的自增 id 就是 3. 也就是说, 出现了自增主键不连续的情况.

总结:自增主键不连续有如下两种情况 :

  • 唯一约束冲突导致自增主键不连续.

  • 事务回滚也会导致自增主键不连续.

如何修改自增主键值

有如下几种修改方式:

  • 使用alter table 表名 AUTO_INCREMENT = ?来修改自增值的起始值。

  • 在创建表时设置 AUTO_INCREMENT=? 自增值的起始值。

来源:https://juejin.cn/post/7135460563919831053

标签:MySQL,主键,自增,机制
0
投稿

猜你喜欢

  • 解决golang 反射interface{}做零值判断的一个重大坑

    2024-05-21 10:24:27
  • Linux下mysql5.6.24(二进制)自动安装脚本

    2024-01-21 12:36:42
  • 关于Python解包知识点总结

    2021-01-03 22:34:15
  • asp下去除超链接的函数

    2011-03-29 11:11:00
  • python 剪切移动文件的实现代码

    2023-06-10 05:21:14
  • 一文带你了解Golang中的WaitGroups

    2024-04-25 13:18:17
  • Anaconda的安装及其环境变量的配置详解

    2022-08-31 19:21:11
  • 利用Tkinter和matplotlib两种方式画饼状图的实例

    2021-09-22 18:10:35
  • python服务器与android客户端socket通信实例

    2022-04-28 02:30:31
  • Python中aiohttp的简单使用

    2022-05-29 05:43:33
  • Python中__init__和__new__的区别详解

    2023-09-24 13:14:17
  • 禁止拷贝网页内容的js代码

    2024-04-25 13:07:50
  • JS实现颜色动态淡化效果

    2024-02-24 13:19:51
  • windows 10下安装搭建django1.10.3和Apache2.4的方法

    2022-01-01 01:07:51
  • Python安装Bs4及使用方法

    2023-01-09 19:36:45
  • python next()和iter()函数原理解析

    2023-04-05 21:58:50
  • MySQL定期分析检查与优化表的方法小结

    2024-01-24 15:04:14
  • python实现感知器算法详解

    2022-04-29 15:08:43
  • 浅析PEP572: 海象运算符

    2023-06-28 10:56:10
  • SWFObject2.0: 基于Javascript的Flash媒体版本检测与嵌入模块

    2009-08-19 13:24:00
  • asp之家 网络编程 m.aspxhome.com