【转】MySQL 隐式转换的坑

原文地址 https://www.modb.pro/db/1717445748688773120

1、发现问题

开发拿到一个SQL问我，为什么一个SQL得不到想要的结果 原本身SQL较复杂，掩盖了简单的问题。这里我用测试数据做一个场景复现，并深入了解问题背后的MySQL原理

2、问题复现

创建测试表，并插入测试数据

create table test_convert
(id int,
 str1 varchar(50),
 str2 varchar(50),
 num DECIMAL(65,0) 
 )
 ;
 
insert into test_convert
select 3,'10004100011000510086','123',10004100011000510086
union all 
select 4,'10004100011000510087','aaa',10004100011000510087;
select * from test_convert;

表数据如上。

select * from  test_convert where str1 = 10004100011000510086; 

执行结果如上 10004100011000510087 的数据也跟着查出来了

3、分析原因

我第一个反映就是看看MySQL是不是改写了我的语句

explain
select * from  test_convert where str1 = 10004100011000510086;   -- 结果错误不会产生warnings  
show warnings;

通过show warnings 查看 发现即没有警告出现，优化器也没有改变我的语句

细心的朋友可能已发现，str1字段是varchar(50)字符型， 我的sql语句比较是数值型，这里发生了隐式转换

通过查看官方文档，可以得到 字符型与数值型比较，最终是双双都转化为浮点型来比较

我以前记得转换的时候如果有异常至少会报warnings

但上述语句却没有告警

那我就再做几种场景的试验

a) 表字段为数值型，比较值为字符串，且不可转换

select * from test_convert where id = '3a';   -- 结果错误但有warnings 
show warnings;

这种情况，结果是错误的。但MySQL给了告警

b) 表字段为字符型，比较值为数值型，可以转换，但会丢失精度

select * from  test_convert where str1 = 10004100011000510086;   -- 结果错误不会产生warnings  
show warnings;

这种情况，结果是错误的。但MySQL不会告警

c) 表字段为字符型，比较值为数值型，可以转换，且不可转换

select * from  test_convert where str2 = 0;  -- 结果错误但有warnings 
show warnings;

这种情况，结果是错误的。但MySQL给了告警

4、给一个总结

出现这种因隐式转换产生的错误，实在是低级错误

第一个是强制开发绝对不允许隐式转换发生 第二个是给当下一些流行的审核工具提个需求。 这种产生隐式转换的语句应该拦住，不允许执行。

其它补充：

对于这类情况，我们通常的解决办法就是：

在测试环境开启全量审计日志后，通过每小时分析审计日志，把全表扫描和低效率索引的查询的sql都找出来（隐式转换大概率是没有走索引的）。