MySQL中隱式轉(zhuǎn)換的踩坑記錄

本來是一個(gè)平靜而美好的下午，其他部門的同事要一份數(shù)據(jù)報(bào)表臨時(shí)匯報(bào)使用，因?yàn)橄到y(tǒng)目前沒有這個(gè)維度的功能，所以需要寫個(gè)SQL馬上出一下，一個(gè)同事接到這個(gè)任務(wù)，于是開始在測(cè)試環(huán)境拼裝這條 SQL，剛過了幾分鐘，同事已經(jīng)自信的寫好了這條SQL，于是拿給DBA，到線上跑一下，用客戶端工具導(dǎo)出Excel 就好了，畢竟是臨時(shí)方案嘛。

就在SQL執(zhí)行了之后，意外發(fā)生了，先是等了一下，發(fā)現(xiàn)還沒執(zhí)行成功，猜測(cè)可能是數(shù)據(jù)量大的原因，但是隨著時(shí)間滴滴答答流逝，逐漸意識(shí)到情況不對(duì)了，一看監(jiān)控，CPU已經(jīng)上去了，但是線上數(shù)據(jù)量雖然不小，也不至于跑成這樣吧，眼看著要跑死了，趕緊把這個(gè)事務(wù)結(jié)束掉了。

什么原因呢？查詢的條件和 join 連接的字段基本都有索引，按道理不應(yīng)該這樣啊，于是趕緊把SQL拿下來，也沒看出什么問題，于是限制查詢條數(shù)再跑了一次，很快出結(jié)果了，但是結(jié)果卻大跌眼鏡，出來的查詢結(jié)果并不是預(yù)期的。

經(jīng)過一番檢查之后，最終發(fā)現(xiàn)了問題所在，是 join 連接中有一個(gè)字段寫錯(cuò)了，因?yàn)檫@兩個(gè)字段有一部分名稱是相同的，于是智能的 SQL 客戶端給出了提示，順手就給敲上去了。但是接下來，更讓人迷惑了，因?yàn)橐B接的字段是 int 類型，而寫錯(cuò)的這個(gè)字段是 varchar 類型，難道不應(yīng)該報(bào)錯(cuò)嗎？怎么還能正常執(zhí)行，并且還有預(yù)期外的查詢結(jié)果？

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

復(fù)現(xiàn)當(dāng)時(shí)的情景

假設(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;

但是呢，因?yàn)槭侄叮瑢?on 后面的條件寫成了 u.id = o.order_code，完全關(guān)聯(lián)錯(cuò)誤，這兩個(gè)字段完全沒有聯(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í)候，會(huì)不會(huì)查出數(shù)據(jù)來呢？

我的第一感覺是，不僅不會(huì)查出數(shù)據(jù)，而且還會(huì)報(bào)錯(cuò)，因?yàn)檫B接的這兩個(gè)字段類型都不一樣，值更不一樣。

結(jié)果卻被啪啪打臉，不僅沒有報(bào)錯(cuò)，而且還查出了數(shù)據(jù)。

可以把這個(gè)問題簡化一下，簡化成下面這條語句，同樣也會(huì)出現(xiàn)問題。

select * from `order` where order_code = 1;

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

根源所在

相信有的同學(xué)已經(jīng)猜出來了，這里是 MySQL 進(jìn)行了隱式轉(zhuǎn)換，由于查詢條件后面跟的查詢值是整型的，所以 MySQL 將 order_code字段進(jìn)行了字符串到整數(shù)類型的轉(zhuǎn)換，而轉(zhuǎn)換后的結(jié)果正好是 1。

通過 cast函數(shù)轉(zhuǎn)換驗(yà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、如果第一個(gè)就是非數(shù)字，最后的結(jié)果就是0；

隱式轉(zhuǎn)換的規(guī)則

當(dāng)操作符與不同類型的操作數(shù)一起使用的時(shí)候，就會(huì)發(fā)生隱式轉(zhuǎn)換。

例如算數(shù)運(yùn)算符的前后是不同類型時(shí)，會(huì)將非數(shù)字類型轉(zhuǎn)換為數(shù)字，比如 '5a'+2，就會(huì)將5a轉(zhuǎn)換為數(shù)字類型，然后和2相加，最后的結(jié)果就是 7 。

再比如 concat函數(shù)是連接兩個(gè)字符串的，當(dāng)此函數(shù)的參數(shù)出現(xiàn)非字符串類型時(shí)，就會(huì)將其轉(zhuǎn)換為字符串，例如concat(88,'就是發(fā)')，最后的結(jié)果就是 88就是發(fā)。

MySQL 官方文檔有以下幾條關(guān)于隱式轉(zhuǎn)換的規(guī)則：

1、兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí)，比較的結(jié)果也是 NULL，例外是使用 <=> 對(duì)兩個(gè) NULL 做比較時(shí)會(huì)返回 1，這兩種情況都不需要做類型轉(zhuǎn)換；

也就是兩個(gè)參數(shù)中如果只有一個(gè)是NULL，則不管怎么比較結(jié)果都是 NULL，而兩個(gè) NULL 的值不管是判斷大于、小于或等于，其結(jié)果都是1。

2、兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來比較，不做類型轉(zhuǎn)換；

3、兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換；

4、十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制字符串；

例如下面這條語句，查詢 user 表中name字段是 0x61 的記錄，0x是16進(jìn)制寫法，其對(duì)應(yīng)的字符串是英文的 'a'，也就是它對(duì)應(yīng)的 ASCII 碼。

select * from user where name = 0x61;

所以，上面這條語句其實(shí)等同于下面這條

select * from user where name = 'a';

可以用 select 0x61;驗(yàn)證一下。

5、有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為 時(shí)間戳；

例如下面這兩條SQL，都是將條件后面的值轉(zhuǎn)換為時(shí)間戳再比較了。

6、有一個(gè)參數(shù)是 decimal 類型，如果另外一個(gè)參數(shù)是 decimal 或者整數(shù)，會(huì)將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)（一般默認(rèn)是 double），則會(huì)把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較；

在不同的數(shù)值類型之間，總是會(huì)向精度要求更高的那一個(gè)類型轉(zhuǎn)換，但是有一點(diǎn)要注意，在MySQL 中浮點(diǎn)數(shù)的精度只有53 bit，超過53bit之后的話，如果后面1位是1就進(jìn)位，如果是0就直接舍棄。所以超大浮點(diǎn)數(shù)在比較的時(shí)候其實(shí)只是取的近似值。

7、所有其他情況下，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較；

如果不符合上面6點(diǎn)規(guī)則，則統(tǒng)一轉(zhuǎn)成浮點(diǎn)數(shù)再進(jìn)行運(yùn)算

避免進(jìn)行隱式轉(zhuǎn)換

我們?cè)谄綍r(shí)的開發(fā)過程中，盡量要避免隱式轉(zhuǎn)換，因?yàn)橐坏┌l(fā)生隱式轉(zhuǎn)換除了會(huì)降低性能外， 還有很大可能會(huì)出現(xiàn)不期望的結(jié)果，就像我最開始遇到的那個(gè)問題一樣。

之所以性能會(huì)降低，還有一個(gè)原因就是讓本來有的索引失效。

select * from `order` where order_code = 1;

order_code 是 varchar 類型，假設(shè)我已經(jīng)在 order_code 上建立了索引，如果是用“=”做查詢條件的話，應(yīng)該直接命中索引才對(duì)，查詢速度會(huì)很快。但是，當(dāng)查詢條件后面的值類型不是 varchar，而是數(shù)值類型的話，MySQL 首先要對(duì) order_code 字段做類型轉(zhuǎn)換，轉(zhuǎn)換為數(shù)值類型，這時(shí)候，之前建的索引也就不會(huì)命中，只能走全表掃描，查詢性能指數(shù)級(jí)下降，搞不好，數(shù)據(jù)庫直接查崩了。

吃一塹長一智，愿各位吃不到這個(gè)塹，但能長這個(gè)智。

審核編輯：湯梓紅