mysql - 搜索小数类型字段时搜索结果不稳定

Question

MySQL 5.5 数据库 SQL:

CREATE TABLE `test` (
  `id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `age` decimal(19,0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
insert into `test` (`id`,`age`) values(`1`,`1234567890123456789`);
insert into `test` (`id`,`age`) values(`2`,`1234567890123456787`);

当我使用这个 SQL 时:

select * from test where age=1234567890123456787 ;

一切正常，但是当我使用这个 SQL 时:

select * from test where age='1234567890123456787' ;

将显示两次重新编码。我不知道为什么当我的代码使用 String 类型搜索十进制类型的字段时，当我的代码将其转换为 Long 时，一切正常。

为什么 MySQL 不能报告错误或自动转换不适合的类型，我不知道这种设计是为了什么？当我使用 Oracle 时，这个问题不存在。

Answer 1

这只是另一个浮点值问题。

将十进制值与字符串进行比较时，会将它们作为 float (实数)进行比较。

以下是来自 MySQL document 的解释:

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:

mysql> SELECT '18015376320243458' = 18015376320243458;
        -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0

Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:

mysql> SELECT '18015376320243459'+0.0;
        -> 1.8015376320243e+16

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.