MySQL令人大跌眼鏡的隱式轉(zhuǎn)換
MySQL的隱式轉(zhuǎn)換
一、問題描述
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)
select * from t1;
+--------------------+
| id |
+--------------------+
| 204027026112927605 |
| 204027026112927603 |
| 2040270261129276 |
| 2040270261129275 |
| 100 |
| 101 |
+--------------------+
6 rows in set (0.00 sec)
奇怪的現(xiàn)象:
select * from t1 where id=204027026112927603; +--------------------+ | id | +--------------------+ | 204027026112927605 | | 204027026112927603 | +--------------------+ 2 rows in set (0.00 sec)

什么鬼,明明查的是204027026112927603,為什么204027026112927605也出來了
二、源碼解釋
堆棧調(diào)用關(guān)系如下所示:

其中JOIN::exec()是執(zhí)行的入口,Arg_comparator::compare_real()是進(jìn)行等值判斷的函數(shù),其定義如下
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 < 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。

當(dāng)掃描到第一行時(shí),204027026112927605轉(zhuǎn)成doule的值為2.0402702611292762e17,等式成立,判定為符合條件的行,繼續(xù)往下掃描,同理204027026112927603也同樣符合

如何檢測(cè)string類型的數(shù)字轉(zhuǎn)成doule類型是否溢出呢?這里經(jīng)過測(cè)試,當(dāng)數(shù)字超過16位以后,轉(zhuǎn)成double類型就已經(jīng)不準(zhǔn)確了,例如20402702611292711會(huì)表示成20402702611292712(如圖中val1)


MySQL string轉(zhuǎn)成double的定義函數(shù)如下:
{
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);
}
真正轉(zhuǎn)換函數(shù)my_strtod_int位置在dtoa.c(太復(fù)雜了,簡(jiǎn)單貼個(gè)注釋吧)
/*
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 <= k <= 22).
*/
既然是這樣,我們測(cè)試下沒有溢出的案例
select * from t1 where id=2040270261129276; +------------------+ | id | +------------------+ | 2040270261129276 | +------------------+ 1 row in set (0.00 sec) select * from t1 where id=101; +------+ | id | +------+ | 101 | +------+ 1 row in set (0.00 sec)
結(jié)果符合預(yù)期,而在本例中,正確的寫法應(yīng)當(dāng)是
select * from t1 where id='204027026112927603'; +--------------------+ | id | +--------------------+ | 204027026112927603 | +--------------------+ 1 row in set (0.01 sec)
三、結(jié)論
避免發(fā)生隱式類型轉(zhuǎn)換,隱式轉(zhuǎn)換的類型主要有字段類型不一致、in參數(shù)包含多個(gè)類型、字符集類型或校對(duì)規(guī)則不一致等
隱式類型轉(zhuǎn)換可能導(dǎo)致無法使用索引、查詢結(jié)果不準(zhǔn)確等,因此在使用時(shí)必須仔細(xì)甄別
數(shù)字類型的建議在字段定義時(shí)就定義為int或者bigint,表關(guān)聯(lián)時(shí)關(guān)聯(lián)字段必須保持類型、字符集、校對(duì)規(guī)則都一致
最后貼一下官網(wǎng)對(duì)于隱式類型轉(zhuǎn)換的說明吧
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.
以上就是MySQL令人大跌眼鏡的隱式轉(zhuǎn)換的詳細(xì)內(nèi)容,更多關(guān)于MySQL的隱式轉(zhuǎn)換的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!,希望大家以后多多支持腳本之家!
相關(guān)文章
SQL Server簡(jiǎn)單實(shí)現(xiàn)數(shù)據(jù)的日?qǐng)?bào)和月報(bào)功能
這篇文章主要介紹了SQL Server簡(jiǎn)單實(shí)現(xiàn)數(shù)據(jù)的日?qǐng)?bào)和月報(bào)功能,結(jié)合實(shí)例形式對(duì)比分析了SQL Server實(shí)現(xiàn)當(dāng)日及當(dāng)月數(shù)據(jù)的查詢功能相關(guān)技巧,需要的朋友可以參考下2016-06-06
SqlServer數(shù)據(jù)庫備份與還原的實(shí)現(xiàn)步驟
這篇文章主要介紹了SqlServer數(shù)據(jù)庫備份與還原的實(shí)現(xiàn)步驟,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-03-03
SQL?Server數(shù)據(jù)庫備份和恢復(fù)數(shù)據(jù)庫的全過程
最近在功能調(diào)試前需要先將測(cè)試數(shù)據(jù)庫備份,然后功能調(diào)試之后再將測(cè)試數(shù)據(jù)庫還原,這樣就可以重復(fù)的進(jìn)行功能調(diào)試,這篇文章主要給大家介紹了關(guān)于SQL?Server數(shù)據(jù)庫備份和恢復(fù)數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下2022-06-06
SQLServer Execpt和not in 性能區(qū)別
網(wǎng)上有很多 except 和 not in的返回結(jié)果區(qū)別這里就就提了2012-01-01
MsSQL數(shù)據(jù)導(dǎo)入到Mongo的默認(rèn)編碼問題(正確導(dǎo)入Mongo的方法)
今天進(jìn)行了一個(gè)實(shí)驗(yàn)來驗(yàn)證數(shù)據(jù)導(dǎo)入到mongo的效率問題,但是期間出現(xiàn)了默認(rèn)編碼問題,下面看一下正確的方法2013-11-11
sql server通過腳本進(jìn)行數(shù)據(jù)庫壓縮全備份的方法【推薦】
這篇文章主要介紹了sql server通過腳本進(jìn)行數(shù)據(jù)庫壓縮全備份的方法,本文給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-06-06
SQL Server正則表達(dá)式 替換函數(shù)應(yīng)用詳解
在sql開發(fā)過程中,經(jīng)常會(huì)使用正則,本文將以此問題進(jìn)行詳細(xì)介紹SQL Server正則表達(dá)式 替換函數(shù)應(yīng)用,需要了解更多的朋友可以參考下2012-11-11
淺談SQLServer的ISNULL函數(shù)與Mysql的IFNULL函數(shù)用法詳解
本篇文章是對(duì)SQLServer的ISNULL函數(shù)與Mysql的IFNULL函數(shù)進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06

