mysql隐式转换导致的越界bug

发表于 更新于 分类于 ， 阅读次数： 本文字数： 3k 阅读时长 ≈ 3 分钟

之前用触发器做数据统计

然后还是一不小心踩坑了

触发器新建了一张fileinfo表，file表中增删查改的信息都会以触发器的形式去修改fileinfo表

后来发现由于mysql是行级锁，由于file表的操作QPS会很高，这个行级锁会造成很大的性能损失

所以需要把增删查改写入随机行。

首先需要新加一个NoUseId字段，这个字段就是随机值匹配的字段

然后需要把原先的UploadSize从unsigned变成signed。

因为如果删除的时候，update的那一行可能会从正数变成负数

1
2
3
4
5
6
7
8
9
create table if not exists `fileinfo` (
    `Bucket` varchar(32) NOT NULL,
    `NoUseId` smallint unsigned NOT NULL DEFAULT 0,
    `UploadSize` bigint NOT NULL DEFAULT 0,
    `DeleteSize` bigint unsigned NOT NULL DEFAULT 0,
    `UploadCount` bigint unsigned NOT NULL DEFAULT 0,
    `DeleteCount` bigint unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY (`Bucket`,`NoUseId`)
)ENGINE=InnoDB CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
delimiter //
create trigger file_insert_after
after insert on file
for each row
begin
  insert into fileinfo(Bucket,NoUseId,UploadSize,UploadCount) values(new.Bucket,RAND() * 10000, new.Size,1) on duplicate key
  update UploadSize = UploadSize + new.Size,UploadCount = UploadCount + 1;
end
//
delimiter ;

delimiter //
create trigger file_update_after
after update on file
for each row
begin
  insert into fileinfo(Bucket,NoUseId,UploadSize,UploadCount) values(new.Bucket,RAND() * 10000, new.Size - old.Size, 0) on duplicate key
  update UploadSize = UploadSize + new.Size - old.Size;
end
//
delimiter ;

delimiter //
create trigger file_delete_after
after delete on file
for each row
begin
  insert into fileinfo(Bucket,NoUseId,DeleteSize,DeleteCount) values(old.Bucket,RAND() * 10000, old.Size,1) on duplicate key
  update DeleteSize = DeleteSize + old.Size,DeleteCount = DeleteCount + 1;
end
//
delimiter ;

很快就报错了

1
meta insert failed Error 1690: BIGINT UNSIGNED value is out of range in '(`POLKA_META_3393`.`fileinfo`.`UploadSize` + NEW.Size)'

这个报错和C的有符号和无符号相加有点类似，没想到mysql也会有这样的问题:

无符号数和有符号数相加的结果，会默认转换成无符号数

做一个小测试

1
2
3
4
5
6
7
8
9
10
11
12
mysql> CREATE TABLE t ( a INT UNSIGNED, b INT SIGNED );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t(a,b) values(0,-1);
Query OK, 1 row affected (0.03 sec)

mysql> update t set b = a+b where a = 0;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` + `test`.`t`.`b`)'

mysql> update t set b = b+convert(a,signed) where a = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

因此如下修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
delimiter //
create trigger file_insert_after
after insert on file
for each row
begin
  insert into fileinfo(Bucket,NoUseId,UploadSize,UploadCount) values(new.Bucket,RAND() * 10000, CONVERT(new.Size, signed), 1) on duplicate key
  update UploadSize = UploadSize + CONVERT(new.Size, signed), UploadCount = UploadCount + 1;
end
//
delimiter ;

delimiter //
create trigger file_update_after
after update on file
for each row
begin
  insert into fileinfo(Bucket,NoUseId,UploadSize,UploadCount) values(new.Bucket,RAND() * 10000, CONVERT(new.Size, signed) - CONVERT(old.Size, signed), 0) on du
  update UploadSize = UploadSize + CONVERT(new.Size, signed) - CONVERT(old.Size, signed);
end
//
delimiter ;

delimiter //
create trigger file_delete_after
after delete on file
for each row
begin
  insert into fileinfo(Bucket,NoUseId,DeleteSize,DeleteCount) values(old.Bucket,RAND() * 10000, old.Size,1) on duplicate key
  update DeleteSize = DeleteSize + old.Size,DeleteCount = DeleteCount + 1;
end
//
delimiter ;

总结

mysql涉及到符号数加减，一定要自己通过convert转换，排除mysql自己的隐式转换规则