主键增长不连续引起的问题根源大都是使用了ON DUPLICATE KEY UPDATE 语法。这个语法的好处是可以将“插入重复后触发更新”作为一个独立可靠的事务来处理，但由此引发的副作用是造成主键值的资源浪费，在更新操作频繁的表中，很容易达到主键字段类型允许的最大值，造成整表不可用。本文的目的：将不允许重复的数据录入到数据库，并保持主键id连续增长。我们通过一个基本应用案例来一步步揭示其工作表现，然后给出解决方案。
准备工作
我们创建一个用户表，只包含了一个自增字段id和登录名name，当然真实项目中还有密码，性别，昵称等其他字段，为了简单明了起见，就不让它们掺和进来了。登录名是不允许重复的，所以在字段name上加了唯一索引限制：
MariaDB > use kunquerDatabase changedMariaDB [kunquer]> CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(200) NOT NULL DEFAULT '' COMMENT '登录名', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息'现在我们插入一条记录，并立即通过last_insert_id()获取插入记录的id：
MariaDB [kunquer]> insert into user(name) values ('******');Query OK, 1 row affected (0.03 sec)MariaDB [kunquer]> insert into user(name) values ('kunquer');Query OK, 1 row affected (0.04 sec)MariaDB [kunquer]> insert into user(name) values ('-------');Query OK, 1 row affected (0.04 sec)MariaDB [kunquer]> select * from user;+----+---------+| id | name |+----+---------+| 1 | ****** || 2 | kunquer || 3 | ------- |+----+---------+3 rows in set (0.00 sec)MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 4 |+----------------+1 row in set (0.00 sec)为了更清晰发现问题，在每次执行插入更新操作后，使用下面的sql查询当前user表的自增计数器计数，因为它保存的值就是下一个执行插入操作记录的主键值：
select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user'自增计数器现在为4，现在我们故意插入一条重复的记录，并看看自增计数器内容如何变化：
MariaDB [kunquer]> insert into user(name) values ('kunquer');ERROR 1062 (23000): Duplicate entry 'kunquer' for key 'name'MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 5 |+----------------+1 row in set (0.00 sec)虽然上面操作失败了，但是计数器仍然+1，这就触发了主键增长不连续的问题，我们先解决Duplicate entry这个问题：使用 ON DUPLICATE KEY UPDATE 语句：
MariaDB [kunquer]> insert into user(name) values ('kunquer') on duplicate key update name = 'kunquer';Query OK, 0 rows affected (0.04 sec)MariaDB [kunquer]> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 3 |+------------------+1 rows in set (0.00 sec)MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 6 |+----------------+1 row in set (0.00 sec)LAST_INSERT_ID()
现在报错问题解决了，但是通过last_insert_id()获取到的值并不是我们想要的(应该是2)，官方对last_insert_id()的描述是这样的：
Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field, or have used INSERT or UPDATE to set a column value with LAST_INSERT_ID(expr).
返回上一步执行的插入或更新语句中自增字段产生的值。last_insert_id()应用场景：1.对含有自增字段的表执行插入或更新操作；2.在插入或更新表的时候使用last_insert_id(expr)来设置某个字段值。
上一步的操作我们是满足第1条应用场景的，但是获取的值与期望不符，我们再试试第2种办法：
MariaDB [kunquer]> insert into user(name) values ('kunquer') on duplicate key update name = 'kunquer', id = last_insert_id(id);Query OK, 0 rows affected (0.03 sec)MariaDB [kunquer]> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 2 |+------------------+2 rows in set (0.00 sec)MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 7 |+----------------+1 row in set (0.00 sec)在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 有三种工作模式，这篇文章有详细介绍，这里不再复述：。我们先看看当前的配置：
MariaDB [kunquer]> SELECT @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 1 |+----------------------------+1 row in set (0.00 sec)因为模式1会导致产生的自增值不连续，我们需要将其工作模式改为0，即保证自增值的连续性。找到MySQL配置文件my.ini，在[mysqld]部分添加一行：
innodb_autoinc_lock_mode = 0重启MySQL后，执行如下语句：
MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 4 |+----------------+你会神奇的发现计数器居然变回了4，这正是想要的结果，因为目前表中只有三条记录。我们再尝试触发些错误：
MariaDB [kunquer]> insert into user(name) values ('kunquer');ERROR 1062 (23000): Duplicate entry 'kunquer' for key 'name'MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 4 |+----------------+MariaDB [kunquer]> insert into user(name) values ('kunquer') on duplicate key update name = 'kunquer', id = last_insert_id(id);Query OK, 0 rows affected (0.05 sec)MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 4 |+----------------+说明如果插入失败或插入重复触发更新操作并没有影响表的行数时，计数器是不会对这些操作再+1的。为保证该机制对后续正常插入无影响，我们再试试插入一条不重复的数据，看看计数器是否会+1：
MariaDB [kunquer]> insert into user(name) values ('@@@@@@');Query OK, 1 row affected (0.05 sec)MariaDB [kunquer]> select auto_increment from information_schema.tables where table_schema = 'kunquer' and table_name = 'user';+----------------+| auto_increment |+----------------+| 5 |+----------------+1 row in set (0.00 sec)正常工作！需要声明的是：更改服务器设置虽然比较省心，但改为0后，每次执行插入或更新操作时，MySQL将会对操作表进行整表锁定，在高并发的情况下自增锁竞争将会很激烈，拖慢整个系统的响应速度。我们将尝试从编程角度寻找解决办法。
先更新，更新失败则插入
前面的逻辑是不管3721，先插入，插入发现有重复则更新，那反过来是否可行呢？先更新，再插入？有点反人类正常思维对不对？因为大部分情况下，要插入的数据肯定是不存在的，更新个锤子？！先别急，我们核心任务要解决的是主键非连续增长的问题，因为需要避免触发duplicate key错误，而更新一个不存在的记录是不会触发这个错误的。我们通过更新结果就能判断记录存在与否，再决定是否需要执行插入操作。
继续我们的示例，放在一个事务中进行包裹，我们先使用如下语句更新一个不存在的用户：
MariaDB [kunquer]> start transaction;Query OK, 0 rows affected (0.00 sec)MariaDB [kunquer]> update user set name = '@@@@@@' where name = '@@@@@@';Query OK, 0 rows affected (0.00 sec)Rows matched: 0 Changed: 0 Warnings: 0语句成功运行，没有报错，只是对数据库无任何更改，根据语句影响行数可判断登录名是否存在，如果不为0，我们只需提前commit，如果为0，我们再将登录信息插入进来：
MariaDB [kunquer]> insert into user(name) values('@@@@@@');Query OK, 1 row affected (0.06 sec)MariaDB [kunquer]> commit;Query OK, 0 rows affected (0.12 sec)这个问题解决核心是事务。只有两条更新语句在事务环境中才变得有意义。一般在程序编程中，我们会对数据库一些通用操作进行封装，例如：save($table, $data, $duplicate)，$table为操作的表名，$data为要插入或更新的数据，$duplicate 是一个主键重复开关，只有在duplicate为true的时候才开启先更新后插入的逻辑，因为考虑到通用性，有些表没有主键，也就不会引起本文提到的问题。这个方法具体实现大家可以在留言区贴上来，互相交流一下。
