MySQL巧施连环计，小菜鸟误入精度坑

Hello_world_
285 阅读6分钟

前言

MySQL作为一款当前市面最流行的关系型数据库之一。居然有如此巧妙的坑，身为菜鸟的笔者竟然也如此巧妙的遇到了这个坑。整个入坑历程且许我缓缓道来。
在一次普通需求的开发完成后，进行测试时。一个业务接口报了难以预料的错误。经过排查，发现该接口中一个查询操作并没有达到预期效果。具体而言，通过Id（具有唯一性业务含义的字段）进行等值查询操作时，查询结果返回了2条数据，但其中一条数据Id与查询条件并不相当。值此年末时刻，想到我的年终，我的心情

ceeb653ejw1fbfsh9a2cyj208c079dg6.jpg

万幸的是，需求并没有上线还好在测试过程中发现了这个问题。先说结论，经过排查最终发现除了原本系统设计不合理外，还存在MySQL隐式类型转换时精度缺失问题。由于公司内部项目，不便透露具体内容，下面将模拟对应的场景，进行复现。

数据准备

CREATE TABLE `a`  (
  `id` varchar(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `fragment` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `a` VALUES ('10000000000198284', '数据1', '10000000000198284');
INSERT INTO `a` VALUES ('10000000000198283', '数据1', '10000000000198283');

表a无论在生产环境还是测试环境中都存在多库多表，其中fragment字段为分库分表的分片键。

排查过程

设计的不合理

在发现查询结果存在多条的情况后，将该查询操作的入参wrapper（fragment字段）进行了复现，直接在数据库集群中手写SQL进行查询。确实存在2条数据，但怪异的是2条数据的id并不相同。最后翻阅代码发现，在前人设计系统时，并没有很好的考虑系统后期的发展，其fragment字段采用的是id的hash值，同时没有对表中id字段进行唯一键约束。在以设计在数据量不大的情况下本无可厚非，但随着发展上游原本id采用的类型为int后来变成了long类型。这也就使得原本通过取id的hash值作为分片键会产生哈希碰撞的现象。如下所示

public static void main(String[] args) {
    Long a = 10000000000198284L;
    System.out.println(a.hashCode());// hash值：1877442686
    Integer b = 1877442686;
    System.out.println(b.hashCode());// hash值：1877442686
}

以上排查过程，发现系统原本设计不合理的地方。对于这一不合理，由于时间原因并没有立马将其处理掉，而是先满足当前需求，更换了查询方式，使用id进行查询。

MySQL隐式转换精度缺失

在使用id进行查询后，发现该查询结果仍为2条。查看代码后，其对应的wrapper如下：

QueryWrapper<A> wrapper = new QueryWrapper<>();
wrapper.eq("id", 10000000000198284L);

在查看运行SQL后发现，SQL语句并没有特别的地方。然后将SQL语句在MySQL中进行查询后，发现该查询仍为2条，并且id不同。

select * from a where id = 10000000000198284

image.png 此时，我的心情简直崩溃

cc11728b4710b912912acd7b4d2d2f0f904522ac.webp

MySQL隐式类型转换

对该查询结果分析，该结果明显不符合查询预期。在一顿操作后，发现一个神奇的现象

select 10000000000198284 = '10000000000198283'

这个语句返回结果居然为1（即是true）。对于该现象有以下2种推测：

  • 前后端参数传递的精度损失（公司采用的数据库管理平台本质与Navicat相同，都是client端将对应请求发送至MySQL）
  • MySQL类型转换导致精度缺失

对于第一种情况，我直接在本地安装了MySQL并进行了相关测试，发现并不是该推测造成的。 对于第二种情况，我使用CAST()函数将字符串和数值分别进行了强制类型转换为相对类型，并查看对应结果

select CAST(10000000000198284 AS CHAR)
select CAST('1000000000019823' AS SIGNED)

发现并没有发生精度损失。那么就只剩一种推测了，隐式类型转换造成了精度损失。
在进行其它测试用例后发现，存在比较正确的情况，以上sql语句返回0（即是false）

select 10000000000198286 = '10000000000198283'

由此，可以大概率确定是由于MySQL隐式类型转换导致了精度损失。

隐式类型转换规则

在MySQL中，字符串类型与数值类型进行计算时，会将变量隐式转换为double类型进行比较。由此，对之前的查询SQL可以进行如下操作

select CAST(10000000000198284 AS double)      -- 1.0000000000198284e16
select CAST(10000000000198286 AS double)      -- 1.0000000000198286e16
select CAST('10000000000198283' AS double)    -- 1.0000000000198284e16

MySQL类型转换规则

总结

看到这里，没错我就是标题党。虽然整个排查过程涉及MySQL的部分并不是那么高，也并不能全甩锅给MySQL。但确实学到了MySQL的一个设计规则

40c77c952e004b3badbdd0918edc69f2.jpeg 根据以上排查，对整个问题而言对以后的开发有了以下总结：

  • 设计系统一定要考虑扩展、一定要考虑扩展、一定要考虑扩展
  • 分库分表一定选合理的分片方式
  • 使用MySQL进行设计表、或查询时，尽量保证类型匹配、查询时若不能类型匹配需要通过代码进行强制类型转换
avatar
文章
阅读
粉丝