MySQL“索引失效”的隱形殺手：隱式類型轉換，你了解多少？
今天這篇文章再介紹另外一種非常隱蔽，但又很容易導致索引失效的場景：隱式類型轉換。隱式類型轉換使用不當，輕則導致索引失效，性能急劇下降，重則會導致SQL語句未查詢到預期的結果。
什么是隱式類型轉換？
MySQL中的隱式類型轉換規(guī)則是在查詢或操作（如比較、函數調用等）中，涉及到不同數據類型時發(fā)生的自動轉換行為。
如果參與操作的表達式或列的數據類型不匹配，MySQL會根據數據類型的上下文自動進行數據類型轉換以適配預期的數據類型。這種行為對性能和結果有時會有較大的影響，比如索引可能失效或比較結果出現意外。
隱式類型轉換典型案例
在進一步介紹隱式類型轉換的詳細規(guī)則之前，我們先來看兩個比較典型的案例，這里采用的MySQL 8.0.37版本。
場景一：未獲得預期數據，且索引失效
創(chuàng)建&初始化示例表
表結構及數據如下：
-- tb_type_change
CREATETABLE`tb_type_change` (
`col1`varchar(255) NOTNULLDEFAULT'',
`col2`intNOTNULLDEFAULT'0',
KEY`idx_c1` (`col1`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
-- 插入一些數據
insertinto tb_type_change values('1234567890123456789',1);
insertinto tb_type_change values('123456789012345678',2);
insertinto tb_type_change values('123456789012345677',3);
insertinto tb_type_change values('12345678901234567',4);
insertinto tb_type_change values('12345678901234568',5);
insertinto tb_type_change values('123456789012345',6);查詢所有數據：
mysql> select * from tb_type_change;
+---------------------+------+
| col1 | col2 |
+---------------------+------+
| 1234567890123456789 | 1 |
| 123456789012345678 | 2 |
| 123456789012345677 | 3 |
| 12345678901234567 | 4 |
| 12345678901234568 | 5 |
| 123456789012345 | 6 |
+---------------------+------+未獲得預期數據示例
執(zhí)行如下SQL語句：
mysql> select * from tb_type_change where col1 = 123456789012345678;
+--------------------+------+
| col1 | col2 |
+--------------------+------+
| 123456789012345678 | 2 |
| 123456789012345677 | 3 |
+--------------------+------+通過上面的查詢可以看到，SQL語句正常執(zhí)行，但查詢的結果并不是預期的結果。查詢條件是123456789012345678，但結構中竟然包含了“123456789012345677”。這就是因為隱式類型轉換導致未獲得預期數據。
原因分析
在 MySQL 中，當查詢條件中的數據類型與列的數據類型不匹配時，會發(fā)生隱式類型轉換。隱式類型轉換通常會將查詢條件類型轉換為列的類型或反之。如果轉換過程中出現精度丟失或者未精確匹配，就可能導致查詢結果不符合預期。
在上述場景中，表 tb_type_change 中列 col1 的類型是 VARCHAR，但查詢條件 123456789012345678 是一個數字（BIGINT 型）。MySQL 會嘗試將 VARCHAR 列的數據轉換為 BIGINT 類型以進行比較。
- BIGINT 的范圍是 -9223372036854775808 到 9223372036854775807，但在比較時，MySQL 會將col1的值從字符串轉換為數字。
- 轉換過程中，如果 col1 的字符串值超過 BIGINT 的最大精度范圍，MySQL 會截斷或丟失部分精度，使得原始字符串被轉換為近似的 BIGINT 值。
在上述示例中，關于類型的轉換：
- '123456789012345677' 轉換為 123456789012345678（數字，伴隨一定的舍入）。
- '123456789012345678' 轉換為 123456789012345678（數字）。
因此，上述SQL語句查詢出了兩個結果。
場景二：隱式類型轉換，索引失效
創(chuàng)建&初始化示例表
新創(chuàng)建一個表以及插入一些數據：
CREATE TABLEtest (
col1 VARCHAR(255) NOTNULLDEFAULT'',
col2 INTNOTNULL,
KEY idx_col1 (col1), -- 對字符串列 col1 建立了索引
KEY idx_col2 (col2) -- 對整數列 col2 建立了索引
);
INSERTINTOtest (col1, col2) VALUES
('123', 123),
('456', 456),
('789', 789),
('abc', 111),
('xyz', 222);未走索引，全表掃描示例
執(zhí)行如下SQL語句：
mysql> EXPLAIN SELECT * FROMtestWHERE col1 = 123 \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: idx_col1
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Usingwhere會發(fā)現上面的SQL語句，并沒有走idx_col1索引，而是進行了全表掃描。
正常使用索引示例
再執(zhí)行另外一個SQL語句：
mysql> EXPLAIN SELECT * FROMtestWHERE col2 = '123' \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: idx_col2
key: idx_col2
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL此時，針對col2列的查詢，正確使用了idx_col2索引。
原因分析
在上面的示例中：
- 當字段類型為字符串類型，參數為整型時，會導致索引失效；
- 而字段類型為整型，傳入的參數為字符串類型時，不會導致索引失效；
這是因為在字符串與數字進行比較時，MySQL會將字符串類型轉換為數字進行比較，因此當字段類型為字符串時，會在字段上加函數，而導致索引失效。
MySQL隱式類型轉換規(guī)則
下面我們整理一些在MySQL當中常見的隱式類型轉換規(guī)則。
字符串與數字
當一個字符串與一個數字進行比較時，字符串會被轉換為一個數字。轉換是基于字符串的數值前綴。如果字符串沒有數值前綴，則轉換結果為 0。上面的案例中，便是字符串轉數字的場景之一。
示例：
SELECT '6' = 6; -- 返回 1（TRUE），因為字符串 '6' 被轉換為數字 6。
SELECT '6a' = 6; -- 返回 1（TRUE），因為字符串 '6a' 在轉換時被認定為數字 6。不同類型的數值
不同類型的數值（例如 INT 和 DOUBLE）在比較時會轉換為精度更高的數值類型。
示例：
SELECT 5 = 5.0; -- 返回 1（TRUE），整型 5 轉換為浮點數 5.0 進行比較。數值與日期比較
日期格式的數據和整型比較時會將整型轉化為日期格式，但是日期格式的字符串和整型比較會將日期字符串轉化為整型。
SELECT CAST('20230101' as date)=20230101; -- 返回 1（TRUE）
SELECT DATE'2023-01-01' =20230101; -- 返回 1（TRUE）
SELECT '2023-01-01'=20230101; -- 返回 0（FALSE）
SELECT '2023-01-01'=2023; -- 返回 1（TRUE）其他規(guī)則
- 兩個參數至少有一個是NULL時，比較的結果也是 NULL，例外是使用<=>對兩個NULL做比較時會返回 1，這兩種情況都不需要做類型轉換；
- 兩個參數都是字符串，會按照字符串來比較，不做類型轉換；
- 兩個參數都是整數，按照整數來比較，不做類型轉換；
- 十六進制的值和非數字做比較時，會被當做二進制串；
- 有一個參數是TIMESTAMP或DATETIME，并且另外一個參數是常量，常量會被轉換為TIMESTAMP；
- 有一個參數是decimal類型，如果另外一個參數是decimal或者整數，會將整數轉換為decimal后進行比較，如果另外一個參數是浮點數，則會把decimal轉換為浮點數進行比較；
- 同一類型內部的轉換。例如，比較TINYINT和BIGINT時，TINYINT會被轉換為BIGINT。
- 使用BLOB或TEXT類型時，應盡量避免使用不同類型的字面值，因為這可能導致意外的類型轉換或比較結果。
小結
隱式轉換的類型主要有字段類型不一致、IN參數包含多個類型、字符集類型或校對規(guī)則不一致等。數據庫在進行隱式轉換時，如果轉換無法正常進行或產生了錯誤的結果，可能會影響查詢的準確性和性能。因此，在設計數據庫和編寫 SQL 查詢時，需仔細甄別，最好顯式指定所需的數據類型，以避免潛在的問題。
