buguge - Keep it simple,stupid

知识就是力量,但更重要的,是运用知识的能力why buguge?

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

【注意】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 直接使用字符串进行比较,避免了隐式类型转换,提高查询性能和结果的准确性。 这也要求我们,在日常开发中,要关注细节,程序中严格按照字段数据类型来赋值,进而规避这样的疏漏。

posted on   buguge  阅读(24)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
历史上的今天:
2021-02-10 item "tracker_server" in file:/***/WEB-INF/lib/***.jar!/fdfs_client.conf not found
点击右上角即可分享
微信分享提示