MySQL 5.7使用+运算符后Decimal精度丢失，与8.0结果差异原因

问题 #

问题背景 #

DDL语句：

CREATE TABLE `tests` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `num` decimal(40,20) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

执行两条UPDATE语句：

1. UPDATE testsSETnum= ? WHEREtests.id = ?，参数为[1.11111111111111111, 1]
2. UPDATE testsSETnum = COALESCE(tests.num, 0) + ? WHERE tests.id = ?，参数为[1.11111111111111111, 2]

执行结果 #

• MySQL 5.7：
  id=1，num=1.11111111111111111000
  id=2，num=1.11111111111111120000（精度丢失）
• MySQL 8.0：
  id=1和id=2的num均为1.11111111111111111000

代码场景（Go语言） #

c, err := ent.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8", ent.Debug())
if err != nil {
    return
}
val, _ := decimal.NewFromString("1.11111111111111111")
c.Test.Update().SetNum(val).Where(test.ID(1)).Exec(context.Background())
c.Test.Update().AddNum(val).Where(test.ID(2)).Exec(context.Background())

请问为何相同语句在MySQL 5.7与8.0版本中会得到不同结果？

原因分析 #

这个差异源于MySQL对数值运算类型转换规则的优化，核心区别在于COALESCE函数的返回值类型推导逻辑：

1. MySQL 5.7的处理逻辑

   • 执行COALESCE(tests.num, 0)时，0是整数类型，MySQL会将decimal(40,20)类型的tests.num（此时为NULL）转换为整数类型的0。后续与传入的高精度小数参数相加时，会触发类型转换为浮点数（double）进行运算。
   • 浮点数的精度有限，无法精确存储1.11111111111111111这类十进制小数，运算后出现精度丢失，最终存入decimal字段时就变成了1.11111111111111120000。
   • 第一条直接赋值的语句，MySQL会直接将传入的高精度参数转换为decimal(40,20)类型存储，不会经过浮点数转换，因此精度完整保留。

2. MySQL 8.0的优化改进

   • MySQL 8.0调整了COALESCE函数的类型推导规则：当函数参数包含decimal类型时，会优先将其他参数（比如这里的整数0）转换为decimal类型，而非整数或浮点数。
   • 这样COALESCE(tests.num, 0)的返回值为decimal(40,20)类型，后续与传入的decimal参数相加时，整个运算都在decimal类型下进行，不会触发浮点数转换，因此能完整保留精度，最终结果和直接赋值一致。

另外，Go代码中使用decimal.NewFromString生成高精度数值，但在MySQL 5.7中因运算环节的类型转换问题，仍会导致精度丢失，而8.0的类型推导优化规避了这一问题。

内容的提问来源于stack exchange，提问作者solitary