个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
36
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章详解MySQL自增主键的实现由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
不同的引擎对于自增值的保存策略不同 。
1.MyISAM引擎的自增值保存在数据文件中 。
2.InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+步长作为这个表当前的自增值 。
|
1
|
select
max
(ai_col)
from
table_name
for
update
;
|
在MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值 。
如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下:
1.如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段 。
2.如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值 。
假设，某次要插入的值是X，当前的自增值是Y 。
1.如果X<Y，那么这个表的自增值不变 。
2.如果X>=Y，就需要把当前自增值修改为新的自增值 。
新的自增值生成算法是：从auto_increment_offset（初始值）开始，以auto_increment_increment（步长）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值 。
创建一个表t，其中id是自增主键字段、c是唯一索引，建表语句如下:
|
1
2
3
4
5
6
7
|
CREATE
TABLE
`t` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`c`
int
(11)
DEFAULT
NULL
,
`d`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`),
UNIQUE
KEY
`c` (`c`)
) ENGINE=InnoDB;
|
假设，表t里面已经有了(1,1,1)这条记录，这时再执行一条插入数据命令:
|
1
|
insert
into
t
values
(
null
, 1, 1);
|
执行流程如下:
1.执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1) 。
2.InnoDB发现用于没有指定自增id的值，获取表t当前的自增值2 。
3.将传入的行的值改成(2,1,1) 。
4.将表的自增值改成3 。
5.继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error（唯一键冲突），语句返回 。
对应的执行流程图如下:
在这之后，再插入新的数据行时，拿到的自增id就是3。出现了自增主键不连续的情况 。
唯一键冲突和事务回滚都会导致自增主键id不连续的情况 。
自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请 。
但在MySQL5.0版本的时候，自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放 。
MySQL5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1 。
1.这个参数设置为0，表示采用之前MySQL5.0版本的策略，即语句执行结束后才释放锁 。
2.这个参数设置为1 。
3.这个参数设置为2，所有的申请自增主键的动作都是申请后就释放锁 。
为了数据的一致性，默认设置为1 。
如果sessionB申请了自增值以后马上就释放自增锁，那么就可能出现这样的情况:
当binlog_format=statement的时候，两个session是同时执行插入数据命令的，所以binlog里面对表t2的更新日志只有两种情况：要么先记sessionA的，要么先记录sessionB的。无论是哪一种，这个binlog拿到从库执行，或者用来恢复临时实例，备库和临时实例里面，sessionB这个语句执行出来，生成的结果里面，id都是连续的。这时，这个库就发生了数据不一致 。
解决这个问题的思路:
1）让原库的批量插入数据语句，固定生成连续的id值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的 。
2）在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。也就是把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row 。
如果有批量插入数据（insert … select、replace … select和load data）的场景时，从并发插入数据性能的角度考虑，建议把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row，这样做既能并发性，又不会出现数据一致性的问题 。
对于批量插入数据的语句，MySQL有一个批量申请自增id的策略:
1.语句执行过程中，第一次申请自增id，会分配1个 。
2.1个用完以后，这个语句第二次申请自增id，会分配2个 。
3.2个用完以后，还是这个语句，第三次申请自增id，会分配4个 。
4.依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍 。
|
1
2
3
4
5
6
7
|
insert
into
t
values
(
null
, 1,1);
insert
into
t
values
(
null
, 2,2);
insert
into
t
values
(
null
, 3,3);
insert
into
t
values
(
null
, 4,4);
create
table
t2
like
t;
insert
into
t2(c,d)
select
c,d
from
t;
insert
into
t2
values
(
null
, 5,5);
|
insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7 。
由于这条语句实际上只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2 values(null, 5,5)，实际上插入了的数据就是(8,5,5) 。
这是主键id出现自增id不连续的第三种原因 。
自增主键字段在达到定义类型上限后，再插入一行记录，则会报主键冲突的错误 。
以无符号整型（4个字节，上限就是 2 32 − 1 2^{32}-1 232−1）为例，通过下面这个语句序列验证一下:
|
1
2
3
|
CREATE
TABLE
t ( id
INT
UNSIGNED auto_increment
PRIMARY
KEY
) auto_increment = 4294967295;
INSERT
INTO
t
VALUES
(
NULL
);
INSERT
INTO
t
VALUES
(
NULL
);
|
第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误 。
推荐资料:
https://time.geekbang.org/column/article/80531 。
到此这篇关于详解MySQL自增主键的实现的文章就介绍到这了,更多相关MySQL自增主键内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！ 。
原文链接：https://blog.csdn.net/qq_40378034/article/details/90736544 。
最后此篇关于详解MySQL自增主键的实现的文章就讲到这里了,如果你想了解更多关于详解MySQL自增主键的实现的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
36
4
0
我听说过两种数据库架构。 大师级 主从 master-master不是更适合现在的web吗，因为它就像Git一样，每个单元都有整套数据，如果一个宕机也无所谓。 主从让我想起了 SVN(我不喜欢它)，你
我们当前将 MySQL 配置为支持故障转移:Site1 Site2。当它们被设置为主/主时。在给定时间点，应用程序服务器仅主动写入一个站点。我们想要设置一个新的故障转移站点。然后我们将拥有 Site
我听说过两种数据库架构。 大师-大师 主从 master-master 不是更适合当今的网络吗，因为它就像 Git，每个单元都有整套数据，如果其中一个发生故障，也没关系。 主从让我想起 SVN(我不喜
我正在创建一个标记为类别的表，其中主类别(父列)包含 0，子类别包含父类别的 ID。我听说这叫引用。我的问题:这张表的结构正确吗？或者是否有更好的方法，例如实现遍历树或类似方法？ CREATE TAB
我正在阅读一份关于 C++ 与 C 的文档。该文档说与 C 相比，C++ 编写得非常紧凑。一个例子是，C 允许 main() 函数类型为 void。另一方面，C++ 不允许这样做，他给出了标准中的以下
C main函数和Java main函数有什么区别？ int main( int argc, const char* argv[] ) 对比 public static void main(Strin
我一直摸不着头脑，但运气不好。设计器有一个包含 3 栏的站点、两个侧边栏和一个主要内容区域。 专为桌面设计，左栏、主要内容、右栏。但是，在较小的设备上，我们希望首先堆叠主要内容。 所以通常情况下，你可
我一直在阅读有关 Jenkins 主/从配置的信息，但我仍然有一些问题: 是不是真的没有像 Jenkins 主站那样安装和启动从站 Jenkins？我假设我会以相同的方式安装一个主 Jenkins 和
据我了解，Viemodel中MVVM背后的概念包括业务逻辑和/或诸如暴露于 View 的数据的主/明细关系之类的事物 因此，正如我发现的那样，有很多ORM生成器，例如模型的telerik a.o以及另
我们有一个群集，其中包含3个主分区，每个主分区有2个副本。主/副本分片的总文档数相同；但是，对于同一查询/文档，我们得到3个不同的分数。当我们将preference = primary添加为查询参数时
我有一个非常大/旧/长时间运行的项目，它使用相对于启动目录的路径访问文件资源(即应用程序仅在从特定目录启动时才工作)。当我需要调试程序时，我可以从 eclipse 启动它并使用“运行配置”->->“工
谁能向我解释一下为什么我在这段代码上遇到段错误？我一直试图弄清楚这一点，但在各种搜索中却一无所获。当我运行代码而不调用 main(argc, argv) 时，它会运行。 Slave 仅将 argv 中
使用 xcode 中的默认项目作为主从应用程序，如果我在折叠委托(delegate)中放置 print 调试语句，当我旋转设备时它似乎永远不会被触发(事实上我永远无法触发它)。 我编辑的代码位于 Ap
是否有任何产品可以使 mysql 主/从故障转移过程更容易？一些可以自动发生的事情，而不是手动修复它。 最佳答案 [...稍后...;) 你所说的“更容易”是什么？MySQL 有很多解决方案: MyS
我有两个 mysql 数据库。我想做主/主复制。 复制以一种方式进行。然而，反过来说却不然。该错误表明它无法与用户“test@IPADDRESS”连接。 如何将用户名更改为 repl？从未进行过测试，
我正在尝试在 MySQL 中运行以下查询: GRANT REPLICATION SLAVE ON *.* TO 'replication'@’10.141.2.%’ IDENTIFIED BY ‘sl
我正在尝试使用 Android 提供的主/详细流程模板创建一个应用程序，并且我正在尝试将多个操作栏菜单项添加到操作栏的主要部分和详细信息部分。这就是我要实现的目标: (来源:softwarecrew.
我正在寻找一个跨平台的 C++ master/worker 库或工作队列库。一般的想法是我的应用程序将创建某种任务或工作对象，将它们传递给工作主机或工作队列，这将依次在单独的线程或进程中执行工作。为了
我似乎看到很多人在他们的 MySQL 模式中任意分配大尺寸的主/外键字段，例如 INT(11) 甚至 WordPress 使用的 BIGINT(20)。 如果我错了，请纠正我，但即使是 INT(4)
如果我有一个可以与多个键相关联的用户，正确的表设置应该是: 一个表有两列，例如: UserName | Key 没有主键且用户可以有多行，或者: 具有匹配标识符的两个表 Table 1 Us
我是一名优秀的程序员,十分优秀！