MySQL中Varchar和Int隱式轉換的小秘密

作者：王振軍 2023-12-12 08:50:22

經過半天的探索，「發現這是MySQL優化器中，判斷數據類型不匹配的比較時，MySQL 優化器會進行隱式類型轉換！」下面我們一起來看看這個隱式轉換，到底是怎么轉換的！

一、前言

在一個陽光明媚的下午，我們的測試在運行SQL時發現了一個靈異事件。別著急，等我慢慢說來，是一個查詢庫存的SQL，控制臺打印了，查詢為0條記錄。想著不太信，自己把SQL粘出來執行一下，「剛好有個varchar類型的字段，查詢的是一堆數字，忘記加引號了。」結果查詢出來了一條！

從頭看到結尾，發現我們查詢條件的字段值為231120103，把數據庫中231120103-1的查詢出來了！

經過半天的探索，「發現這是MySQL優化器中，判斷數據類型不匹配的比較時，MySQL 優化器會進行隱式類型轉換！」

下面我們一起來看看這個隱式轉換，到底是怎么轉換的！

要知其然，知其所以然。

二、實踐出真知

1、建表

CREATE TABLE `str_test`  (
  `id` int(0) NOT NULL,
  `str_column` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `int_column` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

我們新建一個表，里面有varchar和int類型，插入幾條方便測試！

INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (1, '123', 123);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (2, '123-1---1122', 12);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (3, 'abc', 1);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (4, '783221667772672728', 2147483647);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (5, '783221667772672798', 2147483647);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (6, '0', 0);

2、測試查詢

我們先以int類型查詢varchar作為測試：

SELECT * FROM `str_test` WHERE str_column = 123;

大家是不是認為這里只能查詢出一條數據，答案是錯誤的！我們后面統一說結論，這里先看測試！

我們在插入一條str_column位數超過18位的！讓轉化時丟失精度，從而實現多查的情況！

我們看到查詢的和被查詢出來的是不一樣的！

我們在以varchar來查詢int字段：

SELECT * FROM `str_test` WHERE int_column = '12A333';

還是可以查詢到數據！

3、結論

經過上面的測試是不是已經汗流浹背了！不要慌，下面我們來揭曉答案！

有興趣的可以看看官網文檔：MySQL5.7文檔：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

?
當整數與字符串進行比較時，無論數據庫是int還是varchar，只要類型不一致時，MySQL會嘗試將字符串轉換為整數進行比較。
如果字符串以有效的數字開頭，則將其轉換為相應的整數值。
解析規則：從開頭解析直到遇到非數字的字符結束，前面的會作為比較的值，非數字后面的直接拋棄。
如果字符串以非數字字符開頭，將被轉化為0。
數值過大時，回傳精度損失，也會出現匹配。沒找到具體的臨界值，超過18位會出現浮點數精度損失！
?

三、隱式轉換的缺點

精度損失：隱式轉換可能導致精度損失問題，上面我們演示過了。
性能開銷：在進行大規模數據處理時，頻繁的隱式轉換可能會對性能產生影響。
索引失效：存在隱式轉換會讓優化器無法使用索引進行優化查詢，影響響應時間。
數據安全風險：如果是一個刪除語句，像上面演示的會出現匹配到其他行，從而導致數據被誤刪。還有多查的問題。

四、總結

當然這個其實也是一個面試題，大家是不是已經會了！

之前在MySQL索引失效時，就了解過隱式轉換，只知道會轉換，今天才有了更深刻的認識。

其實這個情況我們還是要避免的，不能是MySQL不給我們報錯，我們就這樣不規范的寫。

這要是生產上，一個刪除匹配到多條，和刪庫跑路性質一樣了！

大家還是要小心哈，一定不要出現隱式轉換！

責任編輯：姜華來源：小王博客基地