MySQL 隱式轉換的坑，一起來看看究竟！
點擊關注公眾號，Java干貨及時送達
作者：Harvey
來源：www.fordba.com/mysql-type-convert-analysis.html
一、前言
某一天，開發(fā)問我，為什么針對一個查詢會有兩條記錄，且其中一條記錄并不符合條件select * from tablea where xxno = 170325171202362928;xxno為 170325171202362928 和 170325171202362930的都出現(xiàn)在結果中。
一個等值查詢?yōu)槭裁磿辛硗庖粋€不同值的記錄查詢出來呢？
我們一起來看看究竟！
二、分析
我們查看該表結構，發(fā)現(xiàn)xxno 為varchar 類型，但是等號右邊是一個數(shù)值類型，這種情況下MySQL會如何進行處理呢？
官方文檔如下：https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
也就是說，他會將等于號的兩邊轉換成浮點數(shù)來做比較。
Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:
如果比較使用了浮點型，那么比較會是近似的，將導致結果看起來不一致，也就是可能導致查詢結果錯誤。
我們測試下剛剛生產(chǎn)的例子：
mysql > select '170325171202362928' = 170325171202362930;
+-------------------------------------------+
| '170325171202362928' = 170325171202362930 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)
可以發(fā)現(xiàn)，字符串的'170325171202362928' 和 數(shù)值的170325171202362930比較竟然是相等的。
我們再看下字符串'170325171202362928' 和字符串'170325171202362930' 轉化為浮點型的結果：
mysql > select '170325171202362928'+0.0;
+--------------------------+
| '170325171202362928'+0.0 |
+--------------------------+
| 1.7032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)
mysql > select '170325171202362930'+0.0;
+--------------------------+
| '170325171202362930'+0.0 |
+--------------------------+
| 1.7032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)
我們發(fā)現(xiàn)，將兩個不同的字符串轉化為浮點數(shù)后，結果是一樣的，MySQL 開發(fā) 36 條軍規(guī)，這篇推薦看下。
所以只要是轉化為浮點數(shù)之后的值是相等的，那么，經(jīng)過隱式轉化后的比較也會相等，我們繼續(xù)進行測試其他轉化為浮點型相等的字符串的結果。
mysql > select '170325171202362931'+0.0;
+--------------------------+
| '170325171202362931'+0.0 |
+--------------------------+
| 1.7032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)
mysql > select '170325171202362941'+0.0;
+--------------------------+
| '170325171202362941'+0.0 |
+--------------------------+
| 1.7032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)
字符串'170325171202362931'和'170325171202362941'轉化為浮點型結果一樣，我們看下他們和數(shù)值的比較結果。
mysql > select '170325171202362931' = 170325171202362930;
+-------------------------------------------+
| '170325171202362931' = 170325171202362930 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql > select '170325171202362941' = 170325171202362930;
+-------------------------------------------+
| '170325171202362941' = 170325171202362930 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)
結果也是符合預期的。
因此，當MySQL遇到字段類型不匹配的時候，會進行各種隱式轉化，一定要小心，有可能導致精度丟失。MySQL 系列面試題我都整理好了，關注公眾號Java技術棧，在后臺回復 "面試" 獲取。
For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:
如果字段是字符型，且上面有索引的話，如果查詢條件是用數(shù)值來過濾的，那么該SQL將無法利用字段上的索引
SELECT * FROM tbl_name WHERE str_col=1;
The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.
我們進行測試：
mysql > create table tbl_name(id int ,str_col varchar(10),c3 varchar(5),primary key(id),key idx_str(str_col));
Query OK, 0 rows affected (0.02 sec)
mysql > insert into tbl_name(id,str_col) values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql > insert into tbl_name(id,str_col) values(3,'3c'),(4,'4d');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql > desc select * from tbl_name where str_col='a';
+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+
| 1 | SIMPLE | tbl_name | ref | idx_str | idx_str | 13 | const | 1 | Using where; Using index |
+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+
mysql > desc select * from tbl_name where str_col=3;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl_name | ALL | idx_str | NULL | NULL | NULL | 4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from tbl_name where str_col=3;
+----+---------+------+
| id | str_col | c1 |
+----+---------+------+
| 3 | 3c | NULL |
+----+---------+------+
1 row in set, 2 warnings (0.00 sec)
同時我們可以看到，我們用數(shù)值型的3和str_col進行比較的時候，他無法利用索引，同時取出來的值也是錯誤的：
mysql > show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |
+---------+------+----------------------------------------+
MySQL針對3c 和 4d這兩個值進行了轉化，變成了3和4
三、小結
在數(shù)據(jù)庫中進行查詢的時候，不管是Oracle還是MySQL，一定要注意字段類型，杜絕隱式轉化，不僅會導致查詢緩慢，還會導致結果錯誤。
關注Java技術?？锤喔韶?/strong>
