问题描述

线上错误日志显示一个用来存储年龄段的字典表爆出了id out of range错误，但是在线下数次测试都没问题，该表的定义如下:

create table age_range(
    id tinyint unsigned not null auto_increment primary key,
    value varchar(31) not null unique
)engine=InnoDB default charset=utf8

问题场景

虽然id使用的无符号整数tinyint，最大值可以支持255，但是实际age阶段只有7种，在往这个表中插入数据的时候大部分都是重复的，经查询表中最大的id值也就是8，所以就怀疑是插入语句有问题，插入语句如下

insert into age_range (value)
select distinct(name)
from [origin_table]
on duplicate update key value=values(value);

经过测试发现，每次执行这个语句auto_increment都会增加1，而表里原来的数据不会变，查询语句:

select auto_increment 
from information_schema.tables
where table.schema = 'db' and table.name = 'age_range'

结论 on duplicate update key value=values(value)语句，无论是否新增数据，auto_increment都会增加

为什么线下测试没发现

因为用select distinct(name) from [origin_table]子句插入数据时，auto_increment只增加1，所以在线下测试几次是很难达到无符号tinyint最大值255的。

而使用以下方式添加数据时

insert into age_range (value)
values = ('age-phrase1'),('age-phrase1')...
on duplicate update key value=values(value);

auto_increment增加值就会成为要插入数据的行数。

重置auto_increment

找到解决方案后，可以使用一下语句将auto_increment重置，注意要大于表中auto_increment列的最大值

alter table age_range auto_increment=100;

拓展

经测试replace into和insert ignore into都跟on duplicate update key类似，本质就是insert都会先自增auto_increment值，然后再对数据进行注入重复检查，如果失败了，auto_increment并不会回滚。

总结

看来以后这种即使不成功也会增加auto_increment的语句还是少写，stackoverflow上也有对这个机制的吐槽，但是如果不能自己实现一个RDBS，那就忍着吧...