看看 MySQL令人咋舌的隱式轉換

root@mysqldb?22:12:??[xucl]>?show?create?table?t1G ***************************?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)

int?Arg_comparator::compare_real() { ??/* ????Fix?yet?another?manifestation?of?Bug#2338.?'Volatile'?will?instruct ????gcc?to?flush?double?values?out?of?80-bit?Intel?FPU?registers?before ????performing?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?null_value=?1; ??return?-1; }

{ ??char?buf[DTOA_BUFF_SIZE]; ??double?res; ??DBUG_ASSERT(end?!=?NULL?&amp;&amp;?((str?!=?NULL?&amp;&amp;?*end?!=?NULL)?|| ??????????????????????????????(str?==?NULL?&amp;&amp;?*end?==?NULL))?&amp;&amp; ??????????????error?!=?NULL);  ??res=?my_strtod_int(str,?end,?error,?buf,?sizeof(buf)); ??return?(*error?==?0)???res?:?(res?<p>真正轉換函數my_strtod_int位置在dtoa.c（太復雜了，簡單貼個注釋吧）</p><pre class="brush:php;toolbar:false;">/* ??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?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?that ?????Clinger?missed?--?when?we're?computing?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 ?????compute?(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 ?????compute?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?<p>既然是這樣，我們測試下沒有溢出的案例</p><pre class="brush:php;toolbar:false;">root@mysqldb?23:30:??[xucl]&gt;?select?*?from?t1?where?id=2040270261129276; +------------------+ |?id???????????????| +------------------+ |?2040270261129276?| +------------------+ 1?row?in?set?(0.00?sec)  root@mysqldb?23:30:??[xucl]&gt;?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)

1、If?one?or?both?arguments?are?NULL,?the?result?of?the?comparison?is?NULL,?except?for?the?NULL-safe ?equality?comparison?operator.?For?NULL??NULL,?the?result?is?true.?No?conversion?is?needed. 2、If?both?arguments?in?a?comparison?operation?are?strings,?they?are?compared?as?strings. 3、If?both?arguments?are?integers,?they?are?compared?as?integers. 4、Hexadecimal?values?are?treated?as?binary?strings?if?not?compared?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?comparison?is?performed.?This?is done?to?be?more?ODBC-friendly.?This?is?not?done?for?the?arguments?to?IN().?To?be?safe,?always use?complete?datetime,?date,?or?time?strings?when?doing?comparisons.?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?compared?to?a?DATETIME?value,?the?comparison?is?done?as?two?integers. The?integer?is?not?converted?to?a?temporal?value.?To?compare?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,?comparison?depends?on?the?other?argument.?The arguments?are?compared?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?compared?as?floating-point?(real)?numbers.