新來的實習生把數據庫搞炸了......

作者：isysc1 2020-07-27 08:26:03

SQL 寫的秒，漲薪呱呱叫!就在前不久公司新來的實習生因為寫錯了一條SQL把數據庫搞炸了。

圖片來自 Pexels

新來的實習生小楊寫了一條 SQL 語句：

SELECT wx_id from `user` WHERE wx_id = 2

當小楊迫不及待準備下班回家的時候，隔壁的王經理一把抓住了小楊，并用 EXPLAIN 命令教育了小楊，小楊流下了沒有文化的淚水。

這條 SQL 語句中，wx_id 是具有索引的，但是王經理查出來的結果卻是這樣的：

王經理的教育

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

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

[[334990]]

小楊腦袋瓜瘋狂亂撞，仔細回想表結構，忽然想到，wx_id 字段是 varchar 類型，自己查詢的時候竟然沒有加引號。

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

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

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

正餐來了

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

經過小楊研究發現，如果字段是 varchar類型，等號右側必須加引號才走索引;如果字段是 int 類型，那么等號右側加不加引號都是會走索引的。

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

真相圖

但是結論出來，還是無法回答經理的奪命三連問。

小楊搬來了答案

在 MySQL 查詢中，當查詢條件左右兩側類型不匹配的時候會發生隱式轉換：

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

一旦對索引字段做函數操作，MySQL 會放棄使用索引。

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

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

小楊追問：“你還能在告訴我一些隱式轉換的知識嗎?”

我反手就是一個英文文檔：

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.

貼心的我幫你們翻譯成了中文：

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

再分享一個隱式轉換的坑：你是否偶爾刪除了一些不知道的數據?

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 的那一條記錄，結果把 id 為 6 的那一條也查詢出來了。我想說明什么情況呢?

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

這樣當進行 Select，Update或者 Delete 的時候就可能會多操作一些數據。所以應該加引號的地方別忘記了。

總而言之

隱式類型轉換有無法命中索引的風險，在高并發、大數據量的情況下，命不中索引帶來的后果可不止被運維和經理混合雙打哦!且寫 SQL 且 EXPLAIN!

作者：isysc1

編輯：陶家龍

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

責任編輯：武曉燕來源：碼兒嘟嘟騎