首页 > 数据库 > MySQL

记一次通过id关联查询时过滤失效问题，实际上是varchar和bigint转换问题

来源：SegmentFault

时间：2023-01-20 19:37:19 334浏览收藏

大家好，今天本人给大家带来文章《记一次通过id关联查询时过滤失效问题，实际上是varchar和bigint转换问题》，文中内容主要涉及到MySQL、Java、javascript，如果你对数据库方面的知识点感兴趣，那就请各位朋友继续看下去吧~希望能真正帮到你们，谢谢！

今天在测试库上做一个关联查询时出现了捞出多余的值的情况，现在换个表名重现一下再解释。

如图所示，现有两张表

user表，通过rid和和role表的id关联
role表，只有id和alias字段

从图中的数据来看，理论上执行下面的sql后应该出现下图的结果，一个用户对应一个role，有1条数据

SELECT * from rd_user u, rd_role r where u.rid = r.id where r.id = 123456789012345611

但是实际上执行上面的语句却出现了2条数据，如图

一开始我被搞懵了，怀疑是自己对join的理解出问题了，百度各种join后确定自己的理解没问题。然后又试了好几种分步查询，还是出现错误的结果。最后重新查看表结构时发现一个非常坑爹的点，user表的rid数据类型居然是varchar ，然而role的id字段是bigint

自己又新建了两张表试了下，发现用varchar和bigint关联在一般情况下是没问题的，但是在不断尝试后发现，当这个字段长度大于16位时（这个结论不太准确），就会出现上图的情况。于是把user表的rid字段改成bigint后查询出错的问题就解决了。

按理说到这里就完事了，但是我还是放不下，为啥数据库会出错呢？
于是又查了会儿资料，有了一些小发现。

在一篇文章中，一个博主出现了另一种情况：
他的是char和int问题，在执行

 select 0 = 'abc'

时发现结果是true

根据他的博文写到mysql官方文档说在数字和字符串执行比较时会自动做转换。

其中，如果varchar是数字+字母的组合，会自动截取前面数字部分，再和数字做比较。

比如下面这句结果也是true。

select 1 = '1abc'

官方文档地址

MySQL :: MySQL 5.7 Reference Manual :: 12.4.2 Comparison Functions and Operators

Comparison operations result in a value of
1
(
TRUE
),
0
(
FALSE
), or
NULL
. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.
比较运算的结果为 1 (TRUE)、0 (FALSE) 或 NULL。这些操作适用于数字和字符串。必要时，字符串会自动转换为数字，数字会自动转换为字符串。

只看这句的话，官网说的比较笼统，没说怎么个转换法，也解决不了我的问题。

百度关键词换几轮了没出现想要的结果，去bing上搜索，有了一个发现。其实那个回答也是来自官网的手册。

MySQL :: MySQL 8.0 Reference Manual :: 12.3 Type Conversion in Expression Evaluation

这一节讲的是进行比较操作时数据如何做转化

In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.
在所有其他情况下，参数将作为浮点（双精度）数字进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。

也就是说，在进行varchar和bigint比较时，实际上是将它们转成double后再比较的，double精度丢失就算不知道原理肯定也听过了。

既然都查到这里了，我还想知道到值多大时精度会丢失。有兴趣的同学可以自己查一下浮点数精度丢失，这里贴出关键部分。

计算机中，浮点数由 IEEE 754标准定下的。IEEE 754是啥？

百科里这么解释：IEEE 754规定了四种表示浮点数值的方式：单精确度（32位）、双精确度（64位）、延伸单精确度（43比特以上，很少使用）与延伸双精确度（79比特以上，通常以80位实现）。