解析MySQL隐式转换问题
一、问题描述
root@mysqldb 22:12: [xucl]> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@mysqldb 22:19: [xucl]> select * from t1; +--------------------+ | id | +--------------------+ | 204027026112927605 | | 204027026112927603 | | 2040270261129276 | | 2040270261129275 | | 100 | | 101 | +--------------------+ 6 rows in set (0.00 sec)
奇怪的现象:
root@mysqldb 22:19: [xucl]> select * from t1 where id=204027026112927603; +--------------------+ | id | +--------------------+ | 204027026112927605 | | 204027026112927603 | +--------------------+ 2 rows in set (0.00 sec) 640?wx_fmt=jpeg
什么鬼,明明查的是204027026112927603,为什么204027026112927605也出来了
二、源码解释
堆栈调用关系如下所示:
其中JOIN::exec()是执行的入口,Arg_parator::pare_real()是进行等值判断的函数,其定义如下
int Arg_parator::pare_real() { /* Fix yet another manifestation of Bug#2338. 'Volatile' will instruct g to flush double values out of 80-bit Intel FPU registers before performing the parison. */ volatile double val1, val2; val1= (*a)->val_real(); if (!(*a)->null_value) { val2= (*b)->val_real(); if (!(*b)->null_value) { if (set_null) owner->null_value= 0; if (val1 < val2) return -1; if (val1 == val2) return 0; return 1; } } if (set_null) owner->null_value= 1; return -1; }
比较步骤如下图所示,逐行读取t1表的id列放入val1,而常量204027026112927603存在于cache中,类型为double类型(2.0402702611292762E+17),所以到这里传值给val2后val2=2.0402702611292762E+17。
当扫描到第一行时,204027026112927605转成doule的值为2.0402702611292762e17,等式成立,判定为符合条件的行,继续往下扫描,同理204027026112927603也同样符合
如何检测string类型的数字转成doule类型是否溢出呢?这里经过测试,当数字超过16位以后,转成double类型就已经不准确了,例如20402702611292711会表示成20402702611292712(如图中val1)
MySQL string转成double的定义函数如下:
{ char buf[DTOA_BUFF_SIZE]; double res; DBUG_ASSERT(end != NULL && ((str != NULL && *end != NULL) || (str == NULL && *end == NULL)) && error != NULL); res= my_strtod_int(str, end, error, buf, sizeof(buf)); return (*error == 0) ? res : (res < 0 ? -DBL_MAX : DBL_MAX); }
真正转换函数my_strtod_int位置在dtoa.c(太复杂了,简单贴个注释吧)
/* strtod for IEEE--arithmetic machines. This strtod returns a nearest machine number to the input decimal string (or sets errno to EOVERFLOW). Ties are broken by the IEEE round-even rule. Inspired loosely by William D. Clinger's paper "How to Read Floating Point Numbers Aurately" [Proc. ACM SIGPLAN '90, pp. 92-101]. Modifications: 1. We only require IEEE (not IEEE double-extended). 2. We get by with floating-point arithmetic in a case that Clinger missed -- when we're puting d * 10^n for a small integer d and the integer n is not too much larger than 22 (the maximum integer k for which we can represent 10^k exactly), we may be able to pute (d*10^k) * 10^(e-k) with just one roundoff. 3. Rather than a bit-at-a-time adjustment of the binary result in the hard case, we use floating-point arithmetic to determine the adjustment to within one bit; only in really hard cases do we need to pute a second residual. 4. Because of 3., we don't need a large table of powers of 10 for ten-to-e (just some small tables, e.g. of 10^k for 0 <= k <= 22). */
既然是这样,我们测试下没有溢出的案例
root@mysqldb 23:30: [xucl]> select * from t1 where id=2040270261129276; +------------------+ | id | +------------------+ | 2040270261129276 | +------------------+ 1 row in set (0.00 sec) root@mysqldb 23:30: [xucl]> select * from t1 where id=101; +------+ | id | +------+ | 101 | +------+ 1 row in set (0.00 sec)
结果符合预期,而在本例中,正确的写法应当是
root@mysqldb 22:19: [xucl]> select * from t1 where id='204027026112927603'; +--------------------+ | id | +--------------------+ | 204027026112927603 | +--------------------+ 1 row in set (0.01 sec)
三、结论
避免发生隐式类型转换,隐式转换的类型主要有字段类型不一致、in参数包含多个类型、字符集类型或校对规则不一致等
隐式类型转换可能导致无法使用索引、查询结果不准确等,因此在使用时必须仔细甄别
数字类型的建议在字段定义时就定义为int或者bigint,表关联时关联字段必须保持类型、字符集、校对规则都一致
最后贴一下官网对于隐式类型转换的说明吧
1、If one or both arguments are NULL, the result of the parison is NULL, except for the NULL-safe
<=> equality parison operator. For NULL <=> NULL, the result is true. No conversion is needed.
2、If both arguments in a parison operation are strings, they are pared as strings.
3、If both arguments are integers, they are pared as integers.
4、Hexadecimal values are treated as binary strings if not pared to a number.
5、If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a
constant, the constant is converted to a timestamp before the parison is performed. This is
done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always
use plete datetime, date, or time strings when doing parisons. For example, to achieve best
results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to
the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery
returns an integer to be pared to a DATETIME value, the parison is done as two integers.
The integer is not converted to a temporal value. To pare the operands as DATETIME values,
use CAST() to explicitly convert the subquery value to DATETIME.
6、If one of the arguments is a decimal value, parison depends on the other argument. The
arguments are pared as decimal values if the other argument is a decimal or integer value, or as
floating-point values if the other argument is a floating-point value.
7、In all other cases, the arguments are pared as floating-point (real) numbers.
总结
以上所述是长沙网络推广给大家介绍的MySQL隐式转换,希望对大家有所帮助,如果大家有任何疑问请给我留言,长沙网络推广会及时回复大家的。在此也非常感谢大家对狼蚁SEO网站的支持!
如果你觉得本文对你有帮助,欢迎网络推广网站推广转载,烦请注明出处,谢谢!
编程语言
- 机械手焊接机器人编程 机械手焊接设备编程
- vb数据库编程实例,vb做数据库
- tiobe世界编程语言排行榜,全球编程语言排行
- 成都java编程培训 成都it编程培训中心
- 编程基础培训,编程基础知识培训
- flash游戏编程基础教程,flash游戏开发教程
- 不会英语能学编程吗 不会英语可以学程序员吗
- 爱心代码编程C语言 爱心代码编程c语言公式
- c语言编程学习入门 c语言编程快速入门
- 西门子plc编程培训 西门子plc编程培训班
- 哪里可以学编程 台州哪里可以学编程
- 少儿编程哪个好 少儿编程哪个品牌好
- 编程是什么意思,图形编程是什么意思
- c语言编程软件下载,c语言编程软件在哪下载
- java编程软件下载,java编程下载手机版
- ug编程培训学校,ug编程培训教程