MySQL 隐式转换的坑

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、给一个总结

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

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