解析MySQL隐式转换问题
一、问题描述
root@mysqldb22:12:[xucl]>showcreatetablet1\G ***************************1.row*************************** Table:t1 CreateTable:CREATETABLE`t1`( `id`varchar(255)DEFAULTNULL )ENGINE=InnoDBDEFAULTCHARSET=utf8 1rowinset(0.00sec) root@mysqldb22:19:[xucl]>select*fromt1; +--------------------+ |id| +--------------------+ |204027026112927605| |204027026112927603| |2040270261129276| |2040270261129275| |100| |101| +--------------------+ 6rowsinset(0.00sec)
奇怪的现象：
root@mysqldb22:19:[xucl]>select*fromt1whereid=204027026112927603; +--------------------+ |id| +--------------------+ |204027026112927605| |204027026112927603| +--------------------+ 2rowsinset(0.00sec) 640?wx_fmt=jpeg
什么鬼，明明查的是204027026112927603，为什么204027026112927605也出来了
二、源码解释
堆栈调用关系如下所示：
其中JOIN::exec()是执行的入口，Arg_comparator::compare_real()是进行等值判断的函数，其定义如下
intArg_comparator::compare_real()
{
/*
FixyetanothermanifestationofBug#2338.'Volatile'willinstruct
gcctoflushdoublevaluesoutof80-bitIntelFPUregistersbefore
performingthecomparison.
*/
volatiledoubleval1,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(val1null_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）
MySQLstring转成double的定义函数如下：
{
charbuf[DTOA_BUFF_SIZE];
doubleres;
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（太复杂了，简单贴个注释吧）
/* strtodforIEEE--arithmeticmachines. Thisstrtodreturnsanearestmachinenumbertotheinputdecimal string(orsetserrnotoEOVERFLOW).TiesarebrokenbytheIEEEround-even rule. InspiredlooselybyWilliamD.Clinger'spaper"HowtoReadFloating PointNumbersAccurately"[Proc.ACMSIGPLAN'90,pp.92-101]. Modifications: 1.WeonlyrequireIEEE(notIEEEdouble-extended). 2.Wegetbywithfloating-pointarithmeticinacasethat Clingermissed--whenwe'recomputingd*10^n forasmallintegerdandtheintegernisnottoo muchlargerthan22(themaximumintegerkforwhich wecanrepresent10^kexactly),wemaybeableto compute(d*10^k)*10^(e-k)withjustoneroundoff. 3.Ratherthanabit-at-a-timeadjustmentofthebinary resultinthehardcase,weusefloating-point arithmetictodeterminetheadjustmenttowithin onebit;onlyinreallyhardcasesdoweneedto computeasecondresidual. 4.Becauseof3.,wedon'tneedalargetableofpowersof10 forten-to-e(justsomesmalltables,e.g.of10^k for0<=k<=22). */
既然是这样，我们测试下没有溢出的案例
root@mysqldb23:30:[xucl]>select*fromt1whereid=2040270261129276; +------------------+ |id| +------------------+ |2040270261129276| +------------------+ 1rowinset(0.00sec) root@mysqldb23:30:[xucl]>select*fromt1whereid=101; +------+ |id| +------+ |101| +------+ 1rowinset(0.00sec)
结果符合预期，而在本例中，正确的写法应当是
root@mysqldb22:19:[xucl]>select*fromt1whereid='204027026112927603'; +--------------------+ |id| +--------------------+ |204027026112927603| +--------------------+ 1rowinset(0.01sec)
三、结论
避免发生隐式类型转换，隐式转换的类型主要有字段类型不一致、in参数包含多个类型、字符集类型或校对规则不一致等
隐式类型转换可能导致无法使用索引、查询结果不准确等，因此在使用时必须仔细甄别
数字类型的建议在字段定义时就定义为int或者bigint，表关联时关联字段必须保持类型、字符集、校对规则都一致
最后贴一下官网对于隐式类型转换的说明吧
1、IfoneorbothargumentsareNULL,theresultofthecomparisonisNULL,exceptfortheNULL-safe
<=>equalitycomparisonoperator.ForNULL<=>NULL,theresultistrue.Noconversionisneeded.
2、Ifbothargumentsinacomparisonoperationarestrings,theyarecomparedasstrings.
3、Ifbothargumentsareintegers,theyarecomparedasintegers.
4、Hexadecimalvaluesaretreatedasbinarystringsifnotcomparedtoanumber.
5、IfoneoftheargumentsisaTIMESTAMPorDATETIMEcolumnandtheotherargumentisa
constant,theconstantisconvertedtoatimestampbeforethecomparisonisperformed.Thisis
donetobemoreODBC-friendly.ThisisnotdonefortheargumentstoIN().Tobesafe,always
usecompletedatetime,date,ortimestringswhendoingcomparisons.Forexample,toachievebest
resultswhenusingBETWEENwithdateortimevalues,useCAST()toexplicitlyconvertthevaluesto
thedesireddatatype.
Asingle-rowsubqueryfromatableortablesisnotconsideredaconstant.Forexample,ifasubquery
returnsanintegertobecomparedtoaDATETIMEvalue,thecomparisonisdoneastwointegers.
Theintegerisnotconvertedtoatemporalvalue.TocomparetheoperandsasDATETIMEvalues,
useCAST()toexplicitlyconvertthesubqueryvaluetoDATETIME.
6、Ifoneoftheargumentsisadecimalvalue,comparisondependsontheotherargument.The
argumentsarecomparedasdecimalvaluesiftheotherargumentisadecimalorintegervalue,oras
floating-pointvaluesiftheotherargumentisafloating-pointvalue.
7、Inallothercases,theargumentsarecomparedasfloating-point(real)numbers.
总结
以上所述是小编给大家介绍的MySQL隐式转换，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。