MySQL 類型轉換的隱秘邏輯：你可能踩過的坑
在 MySQL 中，有很多看上去邏輯相同，但性能卻差異巨大的 SQL 語句。對這些語句使用不當的話，就會不經意間導致整個數據庫的壓力變大。
今天精選真實案例與你分享，希望再遇到相似的問題時，你可以做到舉一反三、快速解決問題。
1、問題開篇
一張用戶表 `users` ，其中字段 `phone` 添加了普通索引。
CREATETABLEusers (
idbigint(20) unsignedNOTNULL AUTO_INCREMENT COMMENT'主鍵ID',
namevarchar(32) COLLATE utf8mb4_unicode_ci NOTNULLDEFAULT''COMMENT'名稱',
phone varchar(16) COLLATE utf8mb4_unicode_ci NOTNULLDEFAULT''COMMENT'手機',
created_at timestampNOTNULLDEFAULT'1970-01-01 16:00:00'COMMENT'創建時間',
updated_at timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARY KEY (id),
KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';分別執行以下SQL：
1）字符串類型查詢
EXPLAINSELECT * FROMusersWHERE phone = '2';執行計劃如下：
圖片
2）數值型查詢
EXPLAINSELECT * FROMusersWHERE phone = 2;執行計劃如下：
圖片
發現問題：
當索引字段 `phone` 為字符串類型時，字符串查詢時候使用了索引`idx_phone`，而數值類型查詢時候竟無法使用索引`idx_phone`。
2、問題引申
假如索引字段為整型的話，那用字符串查詢時會不會走索引呢？
實踐出真知，我們來驗證一下。
同樣如上表，修改字段 `phone` 類型由 varchar 變更為 bigint：
ALTERTABLEusersMODIFYCOLUMN phone bigint(16) NOTNULLCOMMENT'手機';然后，分別執行以下SQL：
1）字符串類型查詢
EXPLAINSELECT * FROMusersWHERE phone = '2';執行計劃如下：
圖片
2）數值型查詢
EXPLAINSELECT * FROMusersWHERE phone = 2;執行計劃如下：
圖片
執行后發現，無論是以字符串查詢還是以數值型查詢都會用到索引。
小結：
- 當索引字段是數值類型時，數值型或者字符型查詢都不影響索引的使用。
- 當索引字段是字符類型時，數值型查詢無法使用索引，字符型查詢可正常使用索引。
3、跟進探究
為什么會是這樣呢？其根源就是MySQL的隱式類型轉換。
3.1 什么是隱式類型轉換？
在MySQL中，當操作符與不同類型的操作數一起使用時，會發生類型轉換以使操作數兼容，則會發生隱式類型轉換。
即 MySQL會根據需要自動將數字轉換為字符串，或者將字符串轉換為數字。
mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
->'2 test'很明顯，上面的SQL語句的執行過程中就出現了隱式轉化。
從結果我們可以判定，SQL1中將字符串的“1”轉換為數字1，而在SQL2 中，將數字2轉換為字符串“2”。
3.2 如何避免隱式類型轉換？
3.2.1 清楚轉換規則
只有當清楚的知道隱式類型轉換的規則，才能從根本上避免產生隱式類型轉換。
參考MySQL文檔相關描述，確定隱式類型轉換規則：
1、兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換
2、兩個參數都是字符串，會按照字符串來比較，不做類型轉換
3、兩個參數都是整數，按照整數來比較，不做類型轉換
4、十六進制的值和非數字做比較時，會被當做二進制串
5、有一個參數是 TIMESTAMP 或 DATETIME，并且另外一個參數是常量，常量會被轉換為 timestamp
6、有一個參數是 decimal 類型，如果另外一個參數是 decimal 或者整數，會將整數轉換為 decimal 后進行比較，如果另外一個參數是浮點數，則會把 decimal 轉換為浮點數進行比較
7、所有其他情況下，兩個參數都會被轉換為浮點數再進行比較驗證示例：
mysql> SELECT 'aa' + 1;
-> '1'
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning |1292| Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+上述示例中，將字符串 'aa' 和1進行求和，因為 'aa' 和數字1的類型不同，通過上述轉換規則并且經查看warnings可以確認：隱式類型轉化將字符串轉為了 double 類型。
由于字符串是非數字型的，所以就會被轉換為0，因此計算結果：0+1=1
3.2.2 使用內置函數顯示轉換
MySQL對數據進行類型轉換，提供了cast() 和 convert()。
相同點：兩者都是進行數據類型轉換，實現的功能基本等同
不同點：兩者的語法不同：cast(value as type) 、 convert(value,type)
將數值型轉換為字符串型，應用示例如下：
mysql> SELECT CAST(123aschar);
-> '123'
mysql> SELECT CONVERT(123, char);
-> '123'假如應用在開篇描述問題的查詢中，則如下所示：
EXPLAINSELECT * FROMusersWHERE phone = CAST(123ASCHAR);執行計劃所示：
圖片
結果顯示同應用字符串類型參數一樣，可使用索引`idx_phone`。
3.2.3 類型保持一致
最簡單的一種，保證查詢應用規范，SQL參數類型與數據庫中字段類型保持一致即可。
3.3 字符類型轉換
另外，關于字符串類型轉換的一些補充：
mysql> select '1a2b3c' = 1;
-> 1
mysql> select 'a1b2c3' = 0;
-> 1從上面的例子可以得出：
- 如果字符串的第一個字符就是非數字的字符，那么轉換為數字就是0；
- 如果字符串以數字開頭，那轉換的數字就是開頭的那些數字對應的值，直到遇到非數字字符才結束。
4、總結
本文主要從問題入手，繼而進行問題引申，最終挖掘出問題根源：MySQL隱式類型轉換。
同時也告誡我們日常在寫SQL時一定要檢查參數類型與數據庫字段類型是否一致，否則可能造成隱式類型轉換，不能正常應用索引，造成慢查詢，甚至拖垮整個數據庫服務集群。
如果參數不一致，也可以考慮使用CAST函數顯性轉換成一致類型。
