首页
学习
活动
专区
工具
TVP
发布

给你代码:如何压制MySQL主键值非连续增长

主键增长不连续引起的问题根源大都是使用了ON DUPLICATE KEY UPDATE语法。这个语法的好处是可以将“插入重复后触发更新”作为一个独立可靠的事务来处理,但由此引发的副作用是造成主键值的资源浪费,在更新操作频繁的表中,很容易达到主键字段类型允许的最大值,造成整表不可用。本文的目的:将不允许重复的数据录入到数据库,并保持主键id连续增长。我们通过一个基本应用案例来一步步揭示其工作表现,然后给出解决方案。

准备工作

我们创建一个用户表,只包含了一个自增字段id和登录名name,当然真实项目中还有密码,性别,昵称等其他字段,为了简单明了起见,就不让它们掺和进来了。登录名是不允许重复的,所以在字段name上加了唯一索引限制:

现在我们插入一条记录,并立即通过last_insert_id()获取插入记录的id:

为了更清晰发现问题,在每次执行插入更新操作后,使用下面的sql查询当前user表的自增计数器计数,因为它保存的值就是下一个执行插入操作记录的主键值:

自增计数器现在为4,现在我们故意插入一条重复的记录,并看看自增计数器内容如何变化:

虽然上面操作失败了,但是计数器仍然+1,这就触发了主键增长不连续的问题,我们先解决Duplicate entry这个问题:使用 ON DUPLICATE KEY UPDATE 语句:

LAST_INSERT_ID()

现在报错问题解决了,但是通过last_insert_id()获取到的值并不是我们想要的(应该是2),官方对last_insert_id()的描述是这样的:

Returns the value generated for an column by the previous or statement. Use this function after you have performed an statement into a table that contains an field, or have used or to set a column value with .

返回上一步执行的插入或更新语句中自增字段产生的值。last_insert_id()应用场景:1.对含有自增字段的表执行插入或更新操作;2.在插入或更新表的时候使用last_insert_id(expr)来设置某个字段值。

上一步的操作我们是满足第1条应用场景的,但是获取的值与期望不符,我们再试试第2种办法:

在on duplicate key update 后,我们加了这个更新:

id=last_insert_id(id)

这个意思是告诉last_insert_id(id)将当前保存的计数值设置为被更新的记录对应的id字段值,所以之后调用last_insert_id()就显示被设置的id值了。

截至目前,我们只是对用户登录信息重复检查、录入和返回正确的主键id做了一个靠谱的保障,但是在每次做插入或更新操作,将会引起自增计数器的增长,造成很多主键值被浪费掉,那么在这里呼应一下本文主题:如何压制MySQL主键值非连续增长呢?

更改innodb_autoinc_lock_mode设置

innodb_autoinc_lock_mode 有三种工作模式,这篇文章有详细介绍,这里不再复述:https://www.cnblogs.com/widgetbox/p/10178035.html。我们先看看当前的配置:

因为模式1会导致产生的自增值不连续,我们需要将其工作模式改为0,即保证自增值的连续性。找到MySQL配置文件my.ini,在[mysqld]部分添加一行:

重启MySQL后,执行如下语句:

你会神奇的发现计数器居然变回了4,这正是想要的结果,因为目前表中只有三条记录。我们再尝试触发些错误:

说明如果插入失败或插入重复触发更新操作并没有影响表的行数时,计数器是不会对这些操作再+1的。为保证该机制对后续正常插入无影响,我们再试试插入一条不重复的数据,看看计数器是否会+1:

正常工作!需要声明的是:更改服务器设置虽然比较省心,但改为0后,每次执行插入或更新操作时,MySQL将会对操作表进行整表锁定,在高并发的情况下自增锁竞争将会很激烈,拖慢整个系统的响应速度。我们将尝试从编程角度寻找解决办法。

先更新,更新失败则插入

前面的逻辑是不管3721,先插入,插入发现有重复则更新,那反过来是否可行呢?先更新,再插入?有点反人类正常思维对不对?因为大部分情况下,要插入的数据肯定是不存在的,更新个锤子?!先别急,我们核心任务要解决的是主键非连续增长的问题,因为需要避免触发duplicate key错误,而更新一个不存在的记录是不会触发这个错误的。我们通过更新结果就能判断记录存在与否,再决定是否需要执行插入操作。

继续我们的示例,放在一个事务中进行包裹,我们先使用如下语句更新一个不存在的用户:

语句成功运行,没有报错,只是对数据库无任何更改,根据语句影响行数可判断登录名是否存在,如果不为0,我们只需提前commit,如果为0,我们再将登录信息插入进来:

这个问题解决核心是事务。只有两条更新语句在事务环境中才变得有意义。一般在程序编程中,我们会对数据库一些通用操作进行封装,例如:save($table, $data, $duplicate),$table为操作的表名,$data为要插入或更新的数据,$duplicate 是一个主键重复开关,只有在duplicate为true的时候才开启先更新后插入的逻辑,因为考虑到通用性,有些表没有主键,也就不会引起本文提到的问题。这个方法具体实现大家可以在留言区贴上来,互相交流一下。

当然,我们一步一步,似魔鬼的步伐,咳……讲了这么多,绕了这么大个弯子,问题没有变,变的只是思路,只有思路才有更简单的出路。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20190927A0BHB200?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券