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

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

文章图片

如图所示，现有两张表

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

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

文章图片

但是实际上执行上面的语句却出现了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位实现）。

文章图片

这里取我关心的，浮点数是8字节64位，位数如图所示，实际表示位数只有52位。
这里有一个在线网站可以计算浮点数
IEEE 754 Calculator (weitz.de)
比如对于整数5.换算成二进制是101(辗转相除法)。计算机会把它换算成第一位是1，然后是小数点，再后面是尾数的形式，看下图。

文章图片

这里注意，标准里强制规定第一位是1。和52位尾数凑成了53个槽，所以在数值为2^53次方处之后的数据都有可能不安全。
我们做个实验，2的53次方-1的值是9007199254740991，2的53次方是9007199254740992，2的53次方+1是9007199254740993
看下图，9007199254740992开始，转换成二进制后的位数都是54位，因为浮点数最大只能存53位，所以最后一位都被舍弃了
9007199254740992 == 9007199254740993 成立

文章图片

浏览器的控制台上也可以直接输出结果

文章图片

到这里终于知道查询出问题的原因了。就是在整数转成二进制后，如果大于53位，只要前53位值一样，它们之间用=号就能返回true。
这次查资料还得出一个结论，官网文档真的很重要。但是由于母语不是英语的原因，我们很少会把文档当书看，只有出问题了才想到它。即使平时偶尔查阅用了翻译工具，但是翻译来的东西看了后在脑袋里留不下印象，我们在这方面真的太吃亏了。