这篇文章主要介绍MySQL中隐式转换的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

一、问题描述

root@mysqldb22:12:[xucl]>showcreatetablet1\G***************************1.row***************************Table:t1CreateTable:CREATETABLE`t1`(`id`varchar(255)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(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)

什么鬼，明明查的是204027026112927603，为什么204027026112927605也出来了

二、源码解释

堆栈调用关系如下所示：

其中JOIN::exec()是执行的入口，Arg_comparator::compare_real()是进行等值判断的函数，其定义如下

intArg_comparator::compare_real(){/*FixyetanothermanifestationofBug#2338.'Volatile'willinstructgcctoflushdoublevaluesoutof80-bitIntelFPUregistersbeforeperformingthecomparison.*/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(val1<val2)return-1;if(val1==val2)return0;return1;}}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）

{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.Thisstrtodreturnsanearestmachinenumbertotheinputdecimalstring(orsetserrnotoEOVERFLOW).TiesarebrokenbytheIEEEround-evenrule.InspiredlooselybyWilliamD.Clinger'spaper"HowtoReadFloatingPointNumbersAccurately"[Proc.ACMSIGPLAN'90,pp.92-101].Modifications:1.WeonlyrequireIEEE(notIEEEdouble-extended).2.Wegetbywithfloating-pointarithmeticinacasethatClingermissed--whenwe'recomputingd*10^nforasmallintegerdandtheintegernisnottoomuchlargerthan22(themaximumintegerkforwhichwecanrepresent10^kexactly),wemaybeabletocompute(d*10^k)*10^(e-k)withjustoneroundoff.3.Ratherthanabit-at-a-timeadjustmentofthebinaryresultinthehardcase,weusefloating-pointarithmetictodeterminetheadjustmenttowithinonebit;onlyinreallyhardcasesdoweneedtocomputeasecondresidual.4.Becauseof3.,wedon'tneedalargetableofpowersof10forten-to-e(justsomesmalltables,e.g.of10^kfor0<=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、IfoneoftheargumentsisaTIMESTAMPorDATETIMEcolumnandtheotherargumentisaconstant,theconstantisconvertedtoatimestampbeforethecomparisonisperformed.ThisisdonetobemoreODBC-friendly.ThisisnotdonefortheargumentstoIN().Tobesafe,alwaysusecompletedatetime,date,ortimestringswhendoingcomparisons.Forexample,toachievebestresultswhenusingBETWEENwithdateortimevalues,useCAST()toexplicitlyconvertthevaluestothedesireddatatype.Asingle-rowsubqueryfromatableortablesisnotconsideredaconstant.Forexample,ifasubqueryreturnsanintegertobecomparedtoaDATETIMEvalue,thecomparisonisdoneastwointegers.Theintegerisnotconvertedtoatemporalvalue.TocomparetheoperandsasDATETIMEvalues,useCAST()toexplicitlyconvertthesubqueryvaluetoDATETIME.6、Ifoneoftheargumentsisadecimalvalue,comparisondependsontheotherargument.Theargumentsarecomparedasdecimalvaluesiftheotherargumentisadecimalorintegervalue,orasfloating-pointvaluesiftheotherargumentisafloating-pointvalue.7、Inallothercases,theargumentsarecomparedasfloating-point(real)numbers.

以上是“MySQL中隐式转换的示例分析”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！