MySQL中隱式轉換的踩坑記錄以及解決方法分享

本來是一個平靜而美好的下午，其他部門的同事要一份資料包表臨時彙報使用，因為系統目前沒有這個維度的功能，所以需要寫個SQL馬上出一下，一個同事接到這個任務，於是開始在測試環境拼裝這條 SQL，剛過了幾分鐘，同事已經自信的寫好了這條SQL，於是拿給DBA，到線上跑一下，用使用者端工具匯出Excel 就好了，畢竟是臨時方案嘛。

就在SQL執行了之後，意外發生了，先是等了一下，發現還沒執行成功，猜測可能是資料量大的原因，但是隨著時間滴滴答答流逝，逐漸意識到情況不對了，一看監控，CPU已經上去了，但是線上資料量雖然不小，也不至於跑成這樣吧，眼看著要跑死了，趕緊把這個事務結束掉了。

什麼原因呢？查詢的條件和 join 連線的欄位基本都有索引，按道理不應該這樣啊，於是趕緊把SQL拿下來，也沒看出什麼問題，於是限制查詢條數再跑了一次，很快出結果了，但是結果卻大跌眼鏡，出來的查詢結果並不是預期的。

經過一番檢查之後，最終發現了問題所在，是 join 連線中有一個欄位寫錯了，因為這兩個欄位有一部分名稱是相同的，於是智慧的 SQL 使用者端給出了提示，順手就給敲上去了。但是接下來，更讓人迷惑了，因為要連線的欄位是 int 型別，而寫錯的這個欄位是 varchar 型別，難道不應該報錯嗎？怎麼還能正常執行，並且還有預期外的查詢結果？

難道是 MySQL 有 bug 了，必須要研究一下了。

復現當時的情景

假設有兩張表，這兩張表的結構和資料是下面這樣的。

第一張 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 07:40:56', '2022-09-06 07:40:59');

第二張 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 07:42:25', '2022-09-06 07:42:27');

目的是檢視所有使用者的 order 記錄，假設資料量比較少，可以直接查，不考慮效能問題。

本來的 SQL 語句應該是這樣子的，查詢 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，完全關聯錯誤，這兩個欄位完全沒有聯絡，而且u.id是 int 型別，o.order_code是varchar型別。

select o.* from `user` u 
left JOIN `order` o on u.id = o.order_code;

這樣的話， 當我們執行這條語句的時候，會不會查出資料來呢？

我的第一感覺是，不僅不會查出資料，而且還會報錯，因為連線的這兩個欄位型別都不一樣，值更不一樣。

結果卻被啪啪打臉，不僅沒有報錯，而且還查出了資料。

可以把這個問題簡化一下，簡化成下面這條語句，同樣也會出現問題。

select * from `order` where order_code = 1;

明明這條記錄的 order_code 欄位的值是 1d90530e-6ada-47c1-b2fa-adba4545aabd，怎麼用 order_code=1的條件就把它給查出來了。

根源所在

相信有的同學已經猜出來了，這裡是 MySQL 進行了隱式轉換，由於查詢條件後面跟的查詢值是整型的，所以 MySQL 將 order_code欄位進行了字串到整數型別的轉換，而轉換後的結果正好是 1。

通過 cast函數轉換驗證一下結果。

select cast('1d90530e-6ada-47c1-b2fa-adba4545aabd' as unsigned);

再用兩條 SQL 看一下字串到整數型別轉換的規則。

select cast('223kkk' as unsigned);
select cast('k223kkk' as unsigned);

223kkk轉換後的結果是 223，而k223kkk轉換後的結果是0。總結一下，轉換的規則是：

1、從字串的左側開始向右轉換，遇到非數位就停止；

2、如果第一個就是非數位，最後的結果就是0；

隱式轉換的規則

當操作符與不同型別的運算元一起使用的時候，就會發生隱式轉換。

例如算數運運算元的前後是不同型別時，會將非數位型別轉換為數位，比如 '5a'+2，就會將5a轉換為數位型別，然後和2相加，最後的結果就是 7 。

再比如 concat函數是連線兩個字串的，當此函數的引數出現非字串型別時，就會將其轉換為字串，例如concat(88,'就是發')，最後的結果就是 88就是發。

MySQL 官方檔案有以下幾條關於隱式轉換的規則：

1、兩個引數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做型別轉換；

也就是兩個引數中如果只有一個是NULL，則不管怎麼比較結果都是 NULL，而兩個 NULL 的值不管是判斷大於、小於或等於，其結果都是1。

2、兩個引數都是字串，會按照字串來比較，不做型別轉換；

3、兩個引數都是整數，按照整數來比較，不做型別轉換；

4、十六進位制的值和非數位做比較時，會被當做二進位制字串；

例如下面這條語句，查詢 user 表中name欄位是 0x61 的記錄，0x是16進位制寫法，其對應的字串是英文的 'a'，也就是它對應的 ASCII 碼。

select * from user where name = 0x61;

所以，上面這條語句其實等同於下面這條

select * from user where name = 'a';

可以用 select 0x61;驗證一下。

5、有一個引數是 TIMESTAMP 或 DATETIME，並且另外一個引數是常數，常數會被轉換為 時間戳；

例如下面這兩條SQL，都是將條件後面的值轉換為時間戳再比較了，只不過

6、有一個引數是 decimal 型別，如果另外一個引數是 decimal 或者整數，會將整數轉換為 decimal 後進行比較，如果另外一個引數是浮點數（一般預設是 double），則會把 decimal 轉換為浮點數進行比較；

在不同的數值型別之間，總是會向精度要求更高的那一個型別轉換，但是有一點要注意，在MySQL 中浮點數的精度只有53 bit，超過53bit之後的話，如果後面1位是1就進位，如果是0就直接捨棄。所以超大浮點數在比較的時候其實只是取的近似值。

7、所有其他情況下，兩個引數都會被轉換為浮點數再進行比較；

如果不符合上面6點規則，則統一轉成浮點數再進行運算

避免進行隱式轉換

我們在平時的開發過程中，儘量要避免隱式轉換，因為一旦發生隱式轉換除了會降低效能外， 還有很大可能會出現不期望的結果，就像我最開始遇到的那個問題一樣。

之所以效能會降低，還有一個原因就是讓本來有的索引失效。

select * from `order` where order_code = 1;

order_code 是 varchar 型別，假設我已經在 order_code 上建立了索引，如果是用“=”做查詢條件的話，應該直接命中索引才對，查詢速度會很快。但是，當查詢條件後面的值型別不是 varchar，而是數值型別的話，MySQL 首先要對 order_code 欄位做型別轉換，轉換為數值型別，這時候，之前建的索引也就不會命中，只能走全表掃描，查詢效能指數級下降，搞不好，資料庫直接查崩了。

到此這篇關於MySQL中隱式轉換的踩坑記錄以及解決方法分享的文章就介紹到這了,更多相關MySQL隱式轉換內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！