MySQL返回与搜索条件不匹配的多行

Question

我在本地安装了 MySQL，运行 SELECT VERSION() 返回这个值:5.6.43-84.3

当我运行一个查询时，它返回了多行，而它应该只返回 1 行。让我设置一下，这样更容易解释。

1. 创建测试表:

    CREATE TABLE test_table 
    (
        test_val VARCHAR(255)
    )
    ;

1. 将 3 个值加载到表中:

    INSERT INTO test_table (test_val)
    VALUES 
    ('9671986020630615'),
    ('9671986020630616'),
    ('9671986020630617')
    ;

1. 运行此查询(此查询返回预期的 1 行):

    SELECT * 
    FROM test_table
    WHERE test_val = '9671986020630615'
    ;

1. 运行此查询(此查询不应该返回 3 行):

    SELECT * 
    FROM test_table
    WHERE test_val = 9671986020630615
    ;

以下是我对这种情况的观察:

1. 第一个查询用单引号将 WHERE 子句中的值括起来。
2. 第二个查询没有将 WHERE 子句中的值包围起来。
3. 测试表中的列定义为VARCHAR(255)
4. 第一个查询只返回一行是有道理的，因为它将 WHERE 子句中的字符串与测试表 (VARCHAR(255) )
5. 当 MySQL 将第二个查询的 WHERE 子句中的数值与测试表 (VARCHAR(255)) 中的字符串值进行比较时，发生了一些事情，这是导致 MySQL 返回 3 行而不是 1 行。

第一个查询返回正确的结果是有道理的，因为它是将字符串与字符串进行比较。

从某种程度上说，第二个查询返回了一个错误的数据集(3 行而不是它应该返回的 1 行)。

但我的问题是为什么 MySQL 这样做？为什么当它将一个数字与 3 个不同的 VARCHAR(255) 值进行比较时，当 WHERE 子句中数值的真实值仅匹配 1 行时，它会返回所有 3 行？

所以，本质上，对于第一个查询，MySQL 是这样说的:

'9671986020630615' = '9671986020630615',

'9671986020630615' <> '9671986020630616',

'9671986020630615' <> '9671986020630617'

但是对于第二个查询，它说:

9671986020630615 = '9671986020630615',

9671986020630615 = '9671986020630616',

9671986020630615 = '9671986020630617'

任何帮助将不胜感激。

Answer 1

MySQL 在内部处理所有数字的方式与 Javascript 相同，使用 IEEE double-precision floating point representation .

当您从长数字字符串中省略引号时，即您编写 9671986020630615 代替 '9671986020630615 '，MySQL 将使用该数字。然后，当它运行查询的 WHERE 部分时，它会默默地将每个列值强制转换为 double 字。

但是由于machine epsilon -- the limit of precision -- 对于 double ，9671986020630615、9671986020630616 和 9671986020630617 都具有相同的值。所以 WHERE 找到所有三个。

CAST(9671986020630615 AS DOUBLE) CAST(9671986020630616 AS DOUBLE) CAST(9671986020630617 AS DOUBLE) 
     9.671986020630616e15             9.671986020630616e15             9.671986020630616e15 | 

看看这三个整数如何与 DOUBLE 具有相同的表示形式？