詳解隱祕的 MySQL 型別轉換問題詳解

1、問題開篇

本文來自於我近期工作中遇到的一個真實問題，稍作整理後分享給大家~

一張使用者表，其中 phone 新增了普通索引：

CREATE TABLE users (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
	name varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '名稱',
	phone varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手機',
	created_at timestamp NOT NULL DEFAULT '1970-01-01 16:00:00' COMMENT '建立時間',
	updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
	PRIMARY KEY (id),
	KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='使用者表';

分別執行以下 SQL：

1、字串型別查詢

EXPLAIN SELECT * FROM users WHERE phone = '2';

執行計劃如下：

2、數值型查詢

EXPLAIN SELECT * FROM users WHERE phone = 2;

執行計劃如下：

發現問題：

當索引欄位 `phone` 為字串型別時，字串查詢時候使用了索引`idx_phone`，而數值型別查詢時候竟無法使用索引`idx_phone`。

2、問題引申

假如索引欄位為整型的話，那用字串查詢時會不會走索引呢？

實踐出真知，我們來驗證一下。

同樣如上表，修改欄位 `phone` 型別由 varchar 變更為 bigint：

ALTER TABLE users MODIFY COLUMN phone bigint(16) NOT NULL COMMENT '手機';

然後，分別執行以下 SQL：

1、字串型別查詢

EXPLAIN SELECT * FROM users WHERE phone = '2';

執行計劃如下：

2、數值型查詢

EXPLAIN SELECT * FROM users WHERE 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(123 as char);
-> '123'
mysql> SELECT CONVERT(123, char);
-> '123'

假如應用在開篇描述問題的查詢中，則如下所示：

EXPLAIN SELECT * FROM users WHERE phone = CAST(123 AS CHAR);

結果所示：

結果顯示同應用字串型別引數一樣，可使用索引`idx_phone`。

3.2.3 型別保持一致

最簡單的一種，保證查詢應用規範，SQL 引數型別與資料庫中欄位型別保持一致即可。

3.3 字元型別轉換

另外，關於字串型別轉換的一些補充：

mysql> select '1a2b3c' = 1;
-> 1
mysql> select 'a1b2c3' = 0;
-> 1

從上面的例子可以得出：

• 如果字串的第一個字元就是非數位的字元，那麼轉換為數位就是 0；
• 如果字串以數位開頭，那轉換的數位就是開頭的那些數位對應的值，直到遇到非數位字元才結束。

4、總結

本文主要從問題入手，繼而進行問題引申，最終挖掘出問題根源：MySQL 隱式型別轉換。

同時也告誡我們日常在寫 SQL 時一定要檢查引數型別與資料庫欄位型別是否一致，否則可能造成隱式型別轉換，不能正常應用索引，造成慢查詢，甚至拖垮整個資料庫服務叢集。

如果引數不一致，也可以考慮使用 CAST 函數顯性轉換成一致型別。

資料表設計及應用絕非易事，需要考慮的因素太多了，大家應用過程注意保持敬畏心。

以上就是詳解隱祕的 MySQL 型別轉換問題詳解的詳細內容，更多關於MySQL 型別轉換的資料請關注it145.com其它相關文章！