SQL 寫的秒，漲薪呱呱叫!就在前不久公司新來的實(shí)習(xí)生因?yàn)閷戝e(cuò)了一條SQL把數(shù)據(jù)庫搞炸了。

[[334989]]

圖片來自 Pexels

新來的實(shí)習(xí)生小楊寫了一條 SQL 語句：

SELECT wx_id from `user` WHERE wx_id = 2 

當(dāng)小楊迫不及待準(zhǔn)備下班回家的時(shí)候，隔壁的王經(jīng)理一把抓住了小楊，并用 EXPLAIN 命令教育了小楊，小楊流下了沒有文化的淚水。

這條 SQL 語句中，wx_id 是具有索引的，但是王經(jīng)理查出來的結(jié)果卻是這樣的：

王經(jīng)理的教育

小楊仔細(xì)一瞅 key 字段顯示為 Null，很明顯這條 SQL 語句沒有走索引。

小楊心想“糟糕，又寫錯(cuò) SQL 語句了，這下又要面臨運(yùn)維和經(jīng)理的混合雙打了， 不行我得立馬改下這條 SQL 語句，讓我想想哪里出錯(cuò)了”!

[[334990]]

小楊腦袋瓜瘋狂亂撞，仔細(xì)回想表結(jié)構(gòu)，忽然想到，wx_id 字段是 varchar 類型，自己查詢的時(shí)候竟然沒有加引號。

小楊一把搶過經(jīng)理手里的鍵盤，往 wx_id 的查詢條件上加了引號，結(jié)果：

果然這條 SQL 語句開始走了索引。小楊沾沾自喜以為解決了個(gè)天大的 Bug。

經(jīng)理微微一笑問道“你知道為什么為什么加了引號就走了索引嗎?如果字段是 int 類型，那么查詢的時(shí)候需不需要加引號呢?又是為什么呢?”

正餐來了

小楊被問的呆在原地，無法回答。

經(jīng)過小楊研究發(fā)現(xiàn)，如果字段是 varchar類型，等號右側(cè)必須加引號才走索引;如果字段是 int 類型，那么等號右側(cè)加不加引號都是會(huì)走索引的。

什么?你不相信小楊說的話，有圖有真相。(bonus 字段類型為int)

真相圖

但是結(jié)論出來，還是無法回答經(jīng)理的奪命三連問。

小楊搬來了答案

在 MySQL 查詢中，當(dāng)查詢條件左右兩側(cè)類型不匹配的時(shí)候會(huì)發(fā)生隱式轉(zhuǎn)換：

也就是說 
SELECT wx_id from `user` WHERE wx_id = 2 
等價(jià)于 
SELECT wx_id from `user` WHERE CAST(wx_id AS signed int) = 2 

一旦對索引字段做函數(shù)操作，MySQL 會(huì)放棄使用索引。

所以如果字段是 varchar 類型，等號右側(cè)必須加引號才走索引，否則由于隱式轉(zhuǎn)換，MySQL 會(huì)放棄使用索引。那么憑什么 int 加不加引號都可以使用索引呢?

那是因?yàn)?int 類型的數(shù)字只有 2 能轉(zhuǎn)化為'2'，是唯一確定的。所以雖然需要隱式轉(zhuǎn)換，但不影響使用索引

小楊追問：“你還能在告訴我一些隱式轉(zhuǎn)換的知識(shí)嗎?”

我反手就是一個(gè)英文文檔：

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. 
 
If both arguments in a comparison operation are strings, they are compared as strings. 
 
If both arguments are integers, they are compared as integers. 
 
Hexadecimal values are treated as binary strings if not compared to a number. 
 
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. Note that 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. 
 
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. 
 
In all other cases, the arguments are compared as floating-point (real) numbers. 

貼心的我?guī)湍銈兎g成了中文：

1, 兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí)，比較的結(jié)果也是 NULL，例外是使用 <=>  
對兩個(gè) NULL 做比較時(shí)會(huì)返回 1，這兩種情況都不需要做類型轉(zhuǎn)換 
 
2, 兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來比較，不做類型轉(zhuǎn)換 
 
3, 兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換 
 
4, 十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制串 
 
5, 有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為 timestamp 
 
6, 有一個(gè)參數(shù)是 decimal 類型，如果另外一個(gè)參數(shù)是 decimal 或者整數(shù)會(huì)將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較， 
   如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)，則會(huì)把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較 
 
7, 所有其他情況下，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較 

再分享一個(gè)隱式轉(zhuǎn)換的坑：你是否偶爾刪除了一些不知道的數(shù)據(jù)?

mysql> select * from test; 
+----+-------+-----------+ 
| id | name  | password  | 
+----+-------+-----------+ 
|  1 | test1 | password1 | 
|  2 | test2 | password2 | 
|  3 | aaa   | aaaa      | 
|  4 | 55aaa | 55aaaa    | 
|  5 | 1212  | aaa       | 
|  6 | 1212a | aaa       | 
+----+-------+-----------+ 
6 rows in set (0.00 sec) 
 
mysql> select * from test where name = 1212; 
+----+-------+----------+ 
| id | name  | password | 
+----+-------+----------+ 
|  5 | 1212  | aaa      | 
|  6 | 1212a | aaa      | 
+----+-------+----------+ 
2 rows in set, 5 warnings (0.00 sec) 
 
mysql> select * from test where name = '1212'; 
+----+------+----------+ 
| id | name | password | 
+----+------+----------+ 
|  5 | 1212 | aaa      | 
+----+------+----------+ 
1 row in set (0.00 sec) 

上面的例子本意是查詢 id 為 5 的那一條記錄，結(jié)果把 id 為 6 的那一條也查詢出來了。我想說明什么情況呢?

有時(shí)候我們的數(shù)據(jù)庫表中的一些列是 varchar 類型，但是存儲(chǔ)的值為‘1123’這種的純數(shù)字的字符串值，一些同學(xué)寫 SQL 的時(shí)候又不習(xí)慣加引號。

這樣當(dāng)進(jìn)行 Select，Update或者 Delete 的時(shí)候就可能會(huì)多操作一些數(shù)據(jù)。所以應(yīng)該加引號的地方別忘記了。

總而言之

隱式類型轉(zhuǎn)換有無法命中索引的風(fēng)險(xiǎn)，在高并發(fā)、大數(shù)據(jù)量的情況下，命不中索引帶來的后果可不止被運(yùn)維和經(jīng)理混合雙打哦!且寫 SQL 且 EXPLAIN!

作者：isysc1

編輯：陶家龍

出處：轉(zhuǎn)載自微信公眾號碼兒嘟嘟騎(ID：maer_duduqi)