MySQL5.7环境下字符串数字与数值比较出现等值误判问题咨询

根本原因 #

该问题是隐式类型转换+浮点数精度丢失共同导致的：

• MySQL的等值比较规则要求，当字符串类型和数值类型做比较时，会将两边的内容统一转换为DOUBLE（双精度浮点数）后再进行运算。
• 双精度浮点数的有效精度为53个二进制位，换算为十进制约为15~17位有效数字。你测试用例中的数值均为18位十进制数，已经超出了DOUBLE可以精确表示的范围，转换过程中会执行舍入操作，导致原本不同的原始值被转换为同一个DOUBLE数值，最终比较返回相等的1。

验证方法 #

你可以执行以下语句查看转换后的浮点数结果，就能看到对应数值转换后完全相同：

-- 查看三个异常比较的数值转DOUBLE后的结果
SELECT 
  CAST("349664763740905440" AS DOUBLE) AS str_val,
  CAST(349664763740905470 AS DOUBLE) AS num_val1,
  CAST("349664763740905500" AS DOUBLE) AS str_val2;

执行后可以看到三个返回值完全一致，所以比较会返回1。你给出的第一组和第四组数值舍入后和目标值不同，所以返回0，和测试结果完全匹配。

解决方案 #

• 避免隐式类型转换：比较时统一类型，比如将数值显式转换为字符串后再比较：

  SELECT "349664763740905440" = CAST(349664763740905470 AS CHAR);
  

  该语句会返回符合预期的0。
• 存储层面优化：如果是订单号、业务ID这类不需要做数值运算的长数字，直接设计为VARCHAR/CHAR字符串类型存储，从根源避免隐式转换问题。
• 数值场景优化：如果确实需要存储长数值且需要做运算，使用DECIMAL类型指定足够的精度，不要使用FLOAT/DOUBLE这类近似数值类型。

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