本來是一個平靜而美好的下午,其他部門的同事要一份數(shù)據(jù)報表臨時匯報使用,因為系統(tǒng)目前沒有這個維度的功能,所以需要寫個SQL馬上出一下,一個同事接到這個任務(wù),于是開始在測試環(huán)境拼裝這條 SQL,剛過了幾分鐘,同事已經(jīng)自信的寫好了這條SQL,于是拿給DBA,到線上跑一下,用客戶端工具導(dǎo)出Excel 就好了,畢竟是臨時方案嘛。
就在SQL執(zhí)行了之后,意外發(fā)生了,先是等了一下,發(fā)現(xiàn)還沒執(zhí)行成功,猜測可能是數(shù)據(jù)量大的原因,但是隨著時間滴滴答答流逝,逐漸意識到情況不對了,一看監(jiān)控,CPU已經(jīng)上去了,但是線上數(shù)據(jù)量雖然不小,也不至于跑成這樣吧,眼看著要跑死了,趕緊把這個事務(wù)結(jié)束掉了。
什么原因呢?查詢的條件和 join 連接的字段基本都有索引,按道理不應(yīng)該這樣啊,于是趕緊把SQL拿下來,也沒看出什么問題,于是限制查詢條數(shù)再跑了一次,很快出結(jié)果了,但是結(jié)果卻大跌眼鏡,出來的查詢結(jié)果并不是預(yù)期的。
經(jīng)過一番檢查之后,最終發(fā)現(xiàn)了問題所在,是 join 連接中有一個字段寫錯了,因為這兩個字段有一部分名稱是相同的,于是智能的 SQL 客戶端給出了提示,順手就給敲上去了。但是接下來,更讓人迷惑了,因為要連接的字段是 int 類型,而寫錯的這個字段是 varchar 類型,難道不應(yīng)該報錯嗎?怎么還能正常執(zhí)行,并且還有預(yù)期外的查詢結(jié)果?
難道是 MySQL 有 bug 了,必須要研究一下了。
復(fù)現(xiàn)當(dāng)時的情景
假設(shè)有兩張表,這兩張表的結(jié)構(gòu)和數(shù)據(jù)是下面這樣的。
第一張 user表。
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8_bin DEFAULT NULL, `age` int(3) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; INSERT INTO `user` VALUES (1, '張三', 28, '2022-09-06 0756', '2022-09-06 0759');
第二張 order表
CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `order_code` varchar(64) COLLATE utf8_bin DEFAULT NULL, `money` decimal(20,0) DEFAULT NULL, `title` varchar(255) COLLATE utf8_bin DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; INSERT INTO `order` VALUES (1, 2, '1d90530e-6ada-47c1-b2fa-adba4545aabd', 100, 'xxx購買兩件商品', '2022-09-06 0725', '2022-09-06 0727');
目的是查看所有用戶的 order 記錄,假設(shè)數(shù)據(jù)量比較少,可以直接查,不考慮性能問題。
本來的 SQL 語句應(yīng)該是這樣子的,查詢 order表中用戶iduser_id在user表的記錄。
select o.* from `user` u left JOIN `order` o on u.id = o.user_id;
但是呢,因為手抖,將 on 后面的條件寫成了 u.id = o.order_code,完全關(guān)聯(lián)錯誤,這兩個字段完全沒有聯(lián)系,而且u.id是 int 類型,o.order_code是varchar類型。
select o.* from `user` u left JOIN `order` o on u.id = o.order_code;
這樣的話, 當(dāng)我們執(zhí)行這條語句的時候,會不會查出數(shù)據(jù)來呢?
我的第一感覺是,不僅不會查出數(shù)據(jù),而且還會報錯,因為連接的這兩個字段類型都不一樣,值更不一樣。
結(jié)果卻被啪啪打臉,不僅沒有報錯,而且還查出了數(shù)據(jù)。
可以把這個問題簡化一下,簡化成下面這條語句,同樣也會出現(xiàn)問題。
select * from `order` where order_code = 1;
明明這條記錄的 order_code 字段的值是 1d90530e-6ada-47c1-b2fa-adba4545aabd,怎么用 order_code=1的條件就把它給查出來了。
根源所在
相信有的同學(xué)已經(jīng)猜出來了,這里是 MySQL 進行了隱式轉(zhuǎn)換,由于查詢條件后面跟的查詢值是整型的,所以 MySQL 將 order_code字段進行了字符串到整數(shù)類型的轉(zhuǎn)換,而轉(zhuǎn)換后的結(jié)果正好是 1。
通過 cast函數(shù)轉(zhuǎn)換驗證一下結(jié)果。
select cast('1d90530e-6ada-47c1-b2fa-adba4545aabd' as unsigned);
再用兩條 SQL 看一下字符串到整數(shù)類型轉(zhuǎn)換的規(guī)則。
select cast('223kkk' as unsigned); select cast('k223kkk' as unsigned);
223kkk轉(zhuǎn)換后的結(jié)果是 223,而k223kkk轉(zhuǎn)換后的結(jié)果是0??偨Y(jié)一下,轉(zhuǎn)換的規(guī)則是:
1、從字符串的左側(cè)開始向右轉(zhuǎn)換,遇到非數(shù)字就停止;
2、如果第一個就是非數(shù)字,最后的結(jié)果就是0;
隱式轉(zhuǎn)換的規(guī)則
當(dāng)操作符與不同類型的操作數(shù)一起使用的時候,就會發(fā)生隱式轉(zhuǎn)換。
例如算數(shù)運算符的前后是不同類型時,會將非數(shù)字類型轉(zhuǎn)換為數(shù)字,比如 '5a'+2,就會將5a轉(zhuǎn)換為數(shù)字類型,然后和2相加,最后的結(jié)果就是 7 。
再比如 concat函數(shù)是連接兩個字符串的,當(dāng)此函數(shù)的參數(shù)出現(xiàn)非字符串類型時,就會將其轉(zhuǎn)換為字符串,例如concat(88,'就是發(fā)'),最后的結(jié)果就是 88就是發(fā)。
MySQL 官方文檔有以下幾條關(guān)于隱式轉(zhuǎn)換的規(guī)則:
1、兩個參數(shù)至少有一個是 NULL 時,比較的結(jié)果也是 NULL,例外是使用 <=> 對兩個 NULL 做比較時會返回 1,這兩種情況都不需要做類型轉(zhuǎn)換;
也就是兩個參數(shù)中如果只有一個是NULL,則不管怎么比較結(jié)果都是 NULL,而兩個 NULL 的值不管是判斷大于、小于或等于,其結(jié)果都是1。
2、兩個參數(shù)都是字符串,會按照字符串來比較,不做類型轉(zhuǎn)換;
3、兩個參數(shù)都是整數(shù),按照整數(shù)來比較,不做類型轉(zhuǎn)換;
4、十六進制的值和非數(shù)字做比較時,會被當(dāng)做二進制字符串;
例如下面這條語句,查詢 user 表中name字段是 0x61 的記錄,0x是16進制寫法,其對應(yīng)的字符串是英文的 'a',也就是它對應(yīng)的 ASCII 碼。
select * from user where name = 0x61;
所以,上面這條語句其實等同于下面這條
select * from user where name = 'a';
可以用 select 0x61;驗證一下。
5、有一個參數(shù)是 TIMESTAMP 或 DATETIME,并且另外一個參數(shù)是常量,常量會被轉(zhuǎn)換為 時間戳;
例如下面這兩條SQL,都是將條件后面的值轉(zhuǎn)換為時間戳再比較了。
6、有一個參數(shù)是 decimal 類型,如果另外一個參數(shù)是 decimal 或者整數(shù),會將整數(shù)轉(zhuǎn)換為 decimal 后進行比較,如果另外一個參數(shù)是浮點數(shù)(一般默認是 double),則會把 decimal 轉(zhuǎn)換為浮點數(shù)進行比較;
在不同的數(shù)值類型之間,總是會向精度要求更高的那一個類型轉(zhuǎn)換,但是有一點要注意,在MySQL 中浮點數(shù)的精度只有53 bit,超過53bit之后的話,如果后面1位是1就進位,如果是0就直接舍棄。所以超大浮點數(shù)在比較的時候其實只是取的近似值。
7、所有其他情況下,兩個參數(shù)都會被轉(zhuǎn)換為浮點數(shù)再進行比較;
如果不符合上面6點規(guī)則,則統(tǒng)一轉(zhuǎn)成浮點數(shù)再進行運算
避免進行隱式轉(zhuǎn)換
我們在平時的開發(fā)過程中,盡量要避免隱式轉(zhuǎn)換,因為一旦發(fā)生隱式轉(zhuǎn)換除了會降低性能外, 還有很大可能會出現(xiàn)不期望的結(jié)果,就像我最開始遇到的那個問題一樣。
之所以性能會降低,還有一個原因就是讓本來有的索引失效。
select * from `order` where order_code = 1;
order_code 是 varchar 類型,假設(shè)我已經(jīng)在 order_code 上建立了索引,如果是用“=”做查詢條件的話,應(yīng)該直接命中索引才對,查詢速度會很快。但是,當(dāng)查詢條件后面的值類型不是 varchar,而是數(shù)值類型的話,MySQL 首先要對 order_code 字段做類型轉(zhuǎn)換,轉(zhuǎn)換為數(shù)值類型,這時候,之前建的索引也就不會命中,只能走全表掃描,查詢性能指數(shù)級下降,搞不好,數(shù)據(jù)庫直接查崩了。
吃一塹長一智,愿各位吃不到這個塹,但能長這個智。
審核編輯:湯梓紅
-
服務(wù)器
+關(guān)注
關(guān)注
12文章
8849瀏覽量
84954 -
SQL
+關(guān)注
關(guān)注
1文章
751瀏覽量
43988 -
MySQL
+關(guān)注
關(guān)注
1文章
793瀏覽量
26353
原文標(biāo)題:一個 MySQL 隱式轉(zhuǎn)換的坑,差點把服務(wù)器搞掛了
文章出處:【微信號:DBDevs,微信公眾號:數(shù)據(jù)分析與開發(fā)】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論