给你代码：如何压制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的时候才开启先更新后插入的逻辑，因为考虑到通用性，有些表没有主键，也就不会引起本文提到的问题。这个方法具体实现大家可以在留言区贴上来，互相交流一下。

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