阅读 21

记mysql on duplicate key update引发的id out of range问题

问题描述

线上错误日志显示一个用来存储年龄段的字典表爆出了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 intoinsert ignore into都跟on duplicate update key类似,本质就是insert都会先自增auto_increment值,然后再对数据进行注入重复检查,如果失败了,auto_increment并不会回滚。

总结

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

关注下面的标签,发现更多相似文章
评论