MySQL 隱式轉(zhuǎn)換的坑，一起來看看究竟！
1
前言
某一天，開發(fā)問我，為什么針對(duì)一個(gè)查詢會(huì)有兩條記錄，且其中一條記錄并不符合條件select * from tablea where xxno = 170325171202362928;xxno為 170325171202362928 和 170325171202362930的都出現(xiàn)在結(jié)果中。
一個(gè)等值查詢?yōu)槭裁磿?huì)有另外一個(gè)不同值的記錄查詢出來呢？
我們一起來看看究竟！
2
分析
我們查看該表結(jié)構(gòu)，發(fā)現(xiàn)xxno 為varchar 類型，但是等號(hào)右邊是一個(gè)數(shù)值類型，這種情況下MySQL會(huì)如何進(jìn)行處理呢？
官方文檔如下：https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
也就是說，他會(huì)將等于號(hào)的兩邊轉(zhuǎn)換成浮點(diǎn)數(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:
如果比較使用了浮點(diǎn)型，那么比較會(huì)是近似的，將導(dǎo)致結(jié)果看起來不一致，也就是可能導(dǎo)致查詢結(jié)果錯(cuò)誤。
我們測(cè)試下剛剛生產(chǎn)的例子：
mysql > select '170325171202362928' = 170325171202362930;
+-------------------------------------------+
| '170325171202362928' = 170325171202362930 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)
可以發(fā)現(xiàn)，字符串的'170325171202362928' 和 數(shù)值的170325171202362930比較竟然是相等的。
我們?cè)倏聪伦址?170325171202362928' 和字符串'170325171202362930' 轉(zhuǎn)化為浮點(diǎn)型的結(jié)果：
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)，將兩個(gè)不同的字符串轉(zhuǎn)化為浮點(diǎn)數(shù)后，結(jié)果是一樣的，MySQL 開發(fā) 36 條軍規(guī)，這篇推薦看下。
所以只要是轉(zhuǎn)化為浮點(diǎn)數(shù)之后的值是相等的，那么，經(jīng)過隱式轉(zhuǎn)化后的比較也會(huì)相等，我們繼續(xù)進(jìn)行測(cè)試其他轉(zhuǎn)化為浮點(diǎn)型相等的字符串的結(jié)果。
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'轉(zhuǎn)化為浮點(diǎn)型結(jié)果一樣，我們看下他們和數(shù)值的比較結(jié)果。
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)
結(jié)果也是符合預(yù)期的。
因此，當(dāng)MySQL遇到字段類型不匹配的時(shí)候，會(huì)進(jìn)行各種隱式轉(zhuǎn)化，一定要小心，有可能導(dǎo)致精度丟失。
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'.
我們進(jìn)行測(cè)試：
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í)我們可以看到，我們用數(shù)值型的3和str_col進(jìn)行比較的時(shí)候，他無法利用索引，同時(shí)取出來的值也是錯(cuò)誤的：
mysql > show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |
+---------+------+----------------------------------------+
MySQL針對(duì)3c 和 4d這兩個(gè)值進(jìn)行了轉(zhuǎn)化，變成了3和4
3
小結(jié)
在數(shù)據(jù)庫(kù)中進(jìn)行查詢的時(shí)候，不管是Oracle還是MySQL，一定要注意字段類型，杜絕隱式轉(zhuǎn)化，不僅會(huì)導(dǎo)致查詢緩慢，還會(huì)導(dǎo)致結(jié)果錯(cuò)誤。