MySQL 5.7中BIGINT与VARCHAR等值匹配异常问题咨询

问题场景与现象 #

排查发现MySQL查询结果错误的核心原因：关联查询时将BIGINT类型的id与VARCHAR(64)类型的parent_id直接做等值匹配。错误SQL如下：

select t1.id,t1.name,
       t2.id,t2.name
from location t1
     left join location t2 on t2.parent_id = t1.id
where id = 1649972899018952705

测试验证时发现明显异常规律：大数值的BIGINT与不同值的VARCHAR字符串做等值判断时返回1（匹配成功），但小数值的比较结果符合预期：

select 1649972899018952705 = '1649972899018952706' # 返回1 
select  164997289901895270 = '164997289901895271'  # 返回1 
select  164997289901895271 = '164997289901895261'  # 返回1 
select   16499728990189527 = '164997289901895261'  # 返回0
select  164997289901895271 = '16499728990189526'   # 返回0
select   16499728990189527 = '16499728990189526'   # 返回0
select                   1 = '2'                   # 返回0

MySQL类型转换规则 #

当MySQL对不同数据类型的值进行比较时，会遵循隐式类型转换规则：

1. 若一方是数值类型（如BIGINT），另一方是字符串类型（如VARCHAR），MySQL会自动将字符串转换为数值类型再进行比较。
2. 问题核心在于双精度浮点数的精度限制：MySQL把字符串转换为数值时，会用双精度浮点数存储结果。而双精度浮点数仅能精确表示2^53（约9.007×10^15）以内的整数。当BIGINT值超过这个范围时，无法被精确转换为浮点数，会被近似处理——不同的原始大数值可能被转换为同一个近似浮点数，导致等值判断返回1。

不同MySQL版本的差异 #

从MySQL 5.7到8.0，核心的隐式类型转换逻辑没有本质变化，都会出现上述精度丢失问题，但存在细节差异：

• 警告机制：MySQL 8.0对隐式类型转换的警告更严格，当字符串转数值出现精度丢失时，会在日志或执行反馈中抛出警告；而5.7版本默认不会主动提示这类警告。
• sql_mode影响：若开启STRICT_TRANS_TABLES等严格模式，部分场景下会对转换异常进行更严格的校验，但无法解决浮点数精度丢失的核心问题。
• 无论哪个版本，只要BIGINT值超出2^53范围，与字符串做隐式转换比较都会出现匹配异常。

解决方案 #

1. 显式类型转换：将BIGINT类型转换为字符串后再与VARCHAR字段比较，确保是字符串精确匹配：

   select t1.id,t1.name,
          t2.id,t2.name
   from location t1
        left join location t2 on t2.parent_id = CAST(t1.id AS CHAR)
   where id = 1649972899018952705
   

2. 统一字段类型：将parent_id字段的类型修改为BIGINT，从根源上避免类型不匹配导致的隐式转换问题。

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