MySQL 隐式替换导致精度丢失
前言
原来只知道, MySQL类型隐式替换会影响优化器对索引的选择, 由于遇到了一个隐式替换导致的精度丢失的 Bug ,引起了我对隐式替换的实现逻辑的好奇心.
为什么隐式替换会影响精度吗? 不是类似 String.valueOf() 处理吗?
隐式替换导致精度丢失例子
执行SQL 1 :
select * from type_conversion_test where business_order_id = '210517130303013756';
查询结果 1 :
| id | business_order_id |
|---|---|
| 3 | 210517130303013756 |
执行SQL 2 :
select * from type_conversion_test where business_order_id = 210517130303013756;
查询结果 2 :
| id | business_order_id |
|---|---|
| 1 | 210517130303013752 |
| 2 | 210517130303013770 |
| 3 | 210517130303013756 |
| 4 | 210517130303013767 |
| 5 | 210517130303013773 |
由上面的两个SQL看到, 由于where条件的业务订单id筛选项没有添加引号, 导致了精度丢失问题. 210517130303013756在表中是唯一的, 但是查出来多条数据.
如需构建演示环境请执行如下SQL
# 初始化演示表结构
CREATE TABLE `type_conversion_test` (
`id` int NOT NULL AUTO_INCREMENT,
`business_order_id` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_business_order_id` (`business_order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
# 初始化演示数据
INSERT INTO `db_email_util`.`type_conversion_test`
(`business_order_id`)
VALUES
("210517130303013752"),("210517130303013770"),("210517130303013756"),("210517130303013767"),("210517130303013773");
隐式数字到字符串转换的字符集
这里贴出了数字类型相关的替换规则, 想了解更多请查阅官方文档
有关隐式数字到字符串转换的字符集以及适用于CREATE TABLE ... SELECT 语句的修改规则，请参阅本节后面的信息。
以下规则描述了比较操作如何发生转换：
-
如果一个或两个参数均为
NULL，则比较的结果为NULL，除了NULL-safe<=>相等比较运算符。对于NULL <=> NULL，结果为true。无需转换。 -
如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。
-
如果两个参数都是整数，则将它们作为整数进行比较。
-
如果不将十六进制值与数字进行比较，则将其视为二进制字符串。
-
如果参数之一是a
TIMESTAMP或DATETIMEcolumn，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳。这样做是为了使ODBC更友好。对于的参数，此操作未完成IN()。为了安全起见，在进行比较时，请始终使用完整的日期时间，日期或时间字符串。例如，要在BETWEEN与日期或时间值一起使用时获得最佳结果 ，请使用CAST()将值显式转换为所需的数据类型。一个或多个表中的单行子查询不视为常量。例如，如果子查询返回要与
DATETIME值进行比较的整数，则比较将作为两个整数完成。整数不转换为时间值。要将操作数作为DATETIME值进行比较 ，可CAST()用于将子查询值显式转换为DATETIME。 -
如果参数之一是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较；如果另一个参数是浮点值，则将参数作为浮点值进行比较。
-
在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较。
有关将值从一种时间类型转换为另一种时间类型的信息，请参见第11.2.8节“日期和时间类型之间的转换”。
结论
select * from type_conversion_test where business_order_id = 210517130303013756;
由于 210517130303013756 没有加引号, 默认将其作为浮点数处理, 所以在 210517130303013756 转浮点数的时候, 导致了精度丢失问题.
