为何MySQL使用唯一索引并隐式类型转换时返回多行数据？

这不是Bug，是MySQL隐式类型转换的特性引发的问题。当用数值类型匹配字符串列时，MySQL不会把数值转成字符串，反而会将字符串列的所有值转换为数值类型进行比较。

问题根源 #

你写的查询SELECT * FROM index_test WHERE string = 42356411000001102中，右侧的数值42356411000001102会被MySQL当作DOUBLE类型处理。DOUBLE的有效精度仅为53位二进制，对应十进制约15-17位，而你的两个字符串值是17位数字，转成DOUBLE时末尾的差异会被精度丢失抹平，导致CAST("42356411000001102" AS DOUBLE)与CAST("42356411000001104" AS DOUBLE)结果相等，因此查询返回了两行。

解决方案 #

• 严格保持类型匹配，直接用字符串作为查询条件：

  SELECT * FROM index_test WHERE string = "42356411000001102";
  

• 显式将数值转换为字符串后再比较：

  SELECT * FROM index_test WHERE string = CAST(42356411000001102 AS CHAR);
  

测试验证代码 #

DROP TABLE IF EXISTS index_test;

CREATE TABLE index_test (
    string VARCHAR(40),
    number BIGINT,
    
    UNIQUE INDEX uq_string(string),
    UNIQUE INDEX uq_number(number)
) ENGINE = INNODB;

INSERT INTO index_test
(string, number)
VALUES
("42356411000001102", 42356411000001102),
("42356411000001104", 42356411000001104);

-- 正常返回1行：原生BIGINT匹配
SELECT * FROM index_test WHERE number = 42356411000001102;
-- 正常返回1行：原生VARCHAR匹配
SELECT * FROM index_test WHERE string = "42356411000001102";
-- 正常返回1行：字符串转BIGINT匹配（无精度丢失）
SELECT * FROM index_test WHERE number = "42356411000001102";
-- 返回2行：字符串转DOUBLE时精度丢失，导致匹配两行
SELECT * FROM index_test WHERE string = 42356411000001102;

-- 验证精度丢失：两个值转DOUBLE后相等
SELECT CAST("42356411000001102" AS DOUBLE), CAST("42356411000001104" AS DOUBLE);

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