数据库MySQL基础入门之MySQL隐式转换

一、问题描述rootmysqldb 22:12: [xucl] show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE t1 (id varchar(255) DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf81 row in set (0.00 sec)rootmysqldb 22:19: [xucl] select * from t1;--------------------| id |--------------------| 204027026112927605 || 204027026112927603 || 2040270261129276 || 2040270261129275 || 100 || 101 |--------------------6 rows in set (0.00 sec)奇怪的现象rootmysqldb 22:19: [xucl] select * from t1 where id204027026112927603;--------------------| id |--------------------| 204027026112927605 || 204027026112927603 |--------------------2 rows in set (0.00 sec)640?wx_fmtjpeg什么鬼明明查的是204027026112927603为什么204027026112927605也出来了源码解释堆栈调用关系如下所示其中JOIN::exec()是执行的入口Arg_comparator::compare_real()是进行等值判断的函数其定义如下int Arg_comparator::compare_real(){/*Fix yet another manifestation of Bug#2338. Volatile will instructgcc to flush double values out of 80-bit Intel FPU registers beforeperforming the comparison.*/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.0402702611292762E17所以到这里传值给val2后val22.0402702611292762E17。当扫描到第一行时204027026112927605转成doule的值为2.0402702611292762e17等式成立判定为符合条件的行继续往下扫描同理204027026112927603也同样符合如何检测string类型的数字转成doule类型是否溢出呢?这里经过测试当数字超过16位以后转成double类型就已经不准确了例如20402702611292711会表示成20402702611292712如图中val1MySQL 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 decimalstring (or sets errno to EOVERFLOW). Ties are broken by the IEEE round-evenrule.Inspired loosely by William D. Clingers paper How to Read FloatingPoint Numbers Accurately [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 thatClinger missed -- when were computing d * 10^nfor a small integer d and the integer n is not toomuch larger than 22 (the maximum integer k for whichwe can represent 10^k exactly), we may be able tocompute (d*10^k) * 10^(e-k) with just one roundoff.3. Rather than a bit-at-a-time adjustment of the binaryresult in the hard case, we use floating-pointarithmetic to determine the adjustment to withinone bit; only in really hard cases do we need tocompute a second residual.4. Because of 3., we dont need a large table of powers of 10for ten-to-e (just some small tables, e.g. of 10^kfor 0 k 22).*/既然是这样我们测试下没有溢出的案例rootmysqldb 23:30: [xucl] select * from t1 where id2040270261129276;------------------| id |------------------| 2040270261129276 |------------------1 row in set (0.00 sec)rootmysqldb 23:30: [xucl] select * from t1 where id101;------| id |------| 101 |------1 row in set (0.00 sec)结果符合预期而在本例中正确的写法应当是rootmysqldb 22:19: [xucl] select * from t1 where id204027026112927603;--------------------| id |--------------------| 204027026112927603 |--------------------1 row in set (0.01 sec)三、结论避免发生隐式类型转换隐式转换的类型主要有字段类型不一致、in参数包含多个类型、字符集类型或校对规则不一致等隐式类型转换可能导致无法使用索引、查询结果不准确等因此在使用时必须仔细甄别数字类型的建议在字段定义时就定义为int或者bigint表关联时关联字段必须保持类型、字符集、校对规则都一致文章来源网络 版权归原作者所有上文内容不用于商业目的如涉及知识产权问题请权利人联系小编我们将立即处理