问题的提出
最近在工作当中，无意发现varchar类型的字段，如果与数字进行等值比较，得出来的结果跟预期不一致。当时很是好奇，于是工作之余将此问题原因查明，并作为记录
举例说明
这里就不再取公司的业务数据作为例子，而是直接找到了mysql官网中的例子如下所示
1 | SELECT '9223372036854775807' = 9223372036854775807; |
-> 1
1 | SELECT '9223372036854775807' = 9223372036854775806; |
-> 1
我们可以清晰的看到两个等式左侧的字符串的值是相等的,但右侧的数字却不相等，但是两个等式比较结果确是相同的，如果我们用这种方式(varchar列与数值进行比较运算)来做等值比较，那么产生的结果很可能不是我们预期的内容
官网解释
Comparisons between floating-point numbers and large values of INTEGER type are approximate because the integer is converted to double-precision floating point before comparison, which is not capable of representing all 64-bit integers exactly. For example, the integer value 2^53 + 1 is not representable as a float, and is rounded to 2^53 or 2^53 + 2 before a float comparison, depending on the platform.
我理解这里的大致意思是说，在浮点数和大的Integer类型的数值发生比较的时候，得到的结果是一个近似的结果。
在integer数值与浮点数进行比较之前会被转化为双精度浮点数,这就导致转换后的结果不能精确的表示所有64位整数，举例来说，整型的2^53 + 1是不能被浮点数表示的，发生浮点整数比较时会被近似为2^53或2^53 +2,具体结果视平台不同而不同
看到这里可能会比较疑惑，我明明用的是一个字符串列与数值进行比较，怎么就跟浮点数和整数扯上关系了？我们继续读官方文档
这里在官方文档前面其实描述了,我在这里就直接给出我的理解了，官网的具体链接放于文章末尾，感兴趣的同学可以自行去阅读
当发生比较运算时，以下规则描述了类型转换是如何发生的:
- 如果运算符两侧的操作数有一个或一个以上为NULL，比较结果则为NULL(如果是NULL-safe运算符<=>则不会出现上述结果,select NULL <=> NULL;结果为1,并且不会发生类型转换);
- 如果运算符两侧的操作数都是字符串，它们会被当作字符串比较;
- 运算符两侧都是integer,它们会被当作integer来比较;
- 16进制的value如果不与数字发生比较会被当作二进制串处理;
- 如果其中一个操作数是timestamp或者datetime并且另外一个操作数是常量，常量会被先行转换为时间戳在进行比较，这么做是为了对ODBC更为友好 但这种操作并不支持in()查询,为了安全起见，在这种比较的时候，请尽量使用完整的datetime、date或者是time的字符串来进行比较。举例来说:在date或者是time数据间进行比较的时候,如果想达到最好的比较效果，请尽量使用显示的类型转换(cast )来将两个操作数转换成想要比较的数据类型;
- 一个单行的子查询将不被被当做constant来处理，举例来讲,一个datetime类型的数值和一个返回一个integer类型的子查询,会被当作两个integer数来进行比较，这个integer数将不会被转化为临时变量，如果想要当作datetime类型进行比较 请使用cast进行显示的类型转换;
- 如果操作数中有decimal，这种比较运算将根据另一个操作数的类型来决定。另一个操作数如果是decimal或者integer,则比较会被当作decimal类型进行比较,另一个操作数如果是float类型，则会被当作float类型进行比较;
- 所有其他情况，均会被当做浮点数进行比较，举个例子来讲,一个字符串和一个数字进行比较会被当作浮点数来进行比较
那么通过上述规则,我们不难看出，在用varchar列与数字进行比较的时候则会被当作浮点数进行比较，那么这种情况发生的时候，它们每次并不一定是同一结果，integer操作数会被CPU转换为浮点型，而string的转换会以浮点数的按位乘法的方式进行转换，此外，操作结果还会受到计算机架构、编译器版本、编译器优化等因素的影响。
要想避免这种情况，其中的一种办法就是避免这种浮点数的隐式转换发生，比如我们最开头的例子:
1 | SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806; |
小结
其实我之前之所以没遇到这种问题,完全是因为我自己在写查询语句的时候，使的SQL语句的语义尽量地准确，比如varchar类型的列，我就只使用字符串与之进行比较运算，这就完全规避掉了这种隐式转换的发生,这里其实并不是说楼主水平有多么好或者吹嘘自己，而是想说保持一个良好的编码习惯会为自己避免很多问题的发生，节省很多时间，但是辩证地来看，若经常保持不良好的编码习惯，也能经常收获一些意想不到的问题，这个时候如果肯下点功夫，虽然花费了大量精力去处理问题，但却能收获到一些良好编程习惯接触不到的知识。但无论是采用哪种编码习惯,遇到问题的时候一定要下功夫去查明真相，如果当时没有精力去查明这种问题，工作之余一定要将问题查明原因，否则任你工作经验再多，积累的无非只是”工作年份”而并非”工作经验”