【注意】sql语句where条件中的数据类型不一致,不仅存在性能问题,还会有数据准确性方面的bug...
隐式类型转换规则
MySQL 在进行比较操作时,如果比较双方的数据类型不一致,通常会尝试将其中一个数据类型转换为另一个数据类型,以便进行比较。
对于 select * from t_order where order_no = 1538808276987285507
,当 order_no 为 varchar字符串类型的情况下,MySQL 会把 varchar
类型的 order_no
列的值转换为数字类型,然后再和数字 1538808276987285507
进行比较。
转换过程示例
假设 t_order
表有如下数据:
CREATE TABLE t_order (
order_no varchar(20)
);
INSERT INTO t_order (order_no) VALUES
('1538808276987285507'),
('abc123'),
('123abc');
当执行 SELECT * FROM t_order WHERE order_no = 1538808276987285507;
时,MySQL 会将 order_no
列的值转换为数字类型。具体转换如下:
- 对于值
'1538808276987285507'
,转换为数字1538808276987285507
,与查询条件匹配。 - 对于值
'abc123'
,由于开头不是数字,转换为数字0
,不与查询条件匹配。 - 对于值
'123abc'
,转换为数字123
,不与查询条件匹配。
可能存在的问题
- 性能问题:隐式类型转换可能会导致索引失效,因为索引是基于原始数据类型创建的。当进行隐式类型转换时,MySQL 无法直接使用索引进行查找,而是需要对每一行数据进行转换和比较,从而降低查询性能。
- 数据准确性问题:如果
varchar
列的值不能正确转换为数字,可能会导致意外的结果。例如,'abc'
转换为数字会得到0
,这可能会使查询结果包含一些不符合预期的数据。
关于数据准确性问题,我们再来说道一下。
先执行 INSERT INTO t_order (order_no) VALUES('1538808276987285506');
然后,执行 select * from t_order where order_no = 1538808276987285507
, 会惊奇地发现查询出2条结果!为什么呢?
原因分析:返回两条数据的原因在于 隐式类型转换引发的数值精度丢失。由于 order_no 字段定义为varchar字符串类型,当使用数值 1538808276987285507 进行匹配时,数据库会尝试将 varchar 转换为数值类型(如 DOUBLE 或 BIGINT),而该数值为 19 位,超过 DOUBLE 类型的有效精度,导致转换时末几位被截断或四舍五入,使得 '1538808276987285507' 和 '1538808276987285506' 这两个原本不同的字符串被转换为相同的浮点数值,最终匹配到两条记录。
Mysql知识点:BIGINT(8字节)最大值为 9223372036854775807(18位)。 DOUBLE(浮点数)的精度约为 15-17位有效数字。
我们可以使用下面sql验证一下,观察隐式转换结果:
SELECT
CAST('1538808276987285507' AS DOUBLE) AS val1,
CAST('1538808276987285506' AS DOUBLE) AS val2;
通过输出可知,两者转换后的浮点数值完全相同:
val1 val2
1538808276987285500 1538808276987285500
注重细节、编写严谨的代码
为了避免隐式类型转换带来的问题,建议在编写 SQL 语句时,确保比较双方的数据类型一致。对于上述查询,可以将查询条件修改为字符串形式:
SELECT * FROM t_order WHERE order_no = '1538808276987285507';
这样可以确保 MySQL 直接使用字符串进行比较,避免了隐式类型转换,提高查询性能和结果的准确性。 这也要求我们,在日常开发中,要关注细节,程序中严格按照字段数据类型来赋值,进而规避这样的疏漏。
当看到一些不好的代码时,会发现我还算优秀;当看到优秀的代码时,也才意识到持续学习的重要!--buguge
本文来自博客园,转载请注明原文链接:https://www.cnblogs.com/buguge/p/18708901
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
2021-02-10 item "tracker_server" in file:/***/WEB-INF/lib/***.jar!/fdfs_client.conf not found