MySQL 隱式轉(zhuǎn)換的坑，一起來看看究竟！

一、前言

某一天，開發(fā)問我，為什么針對(duì)一個(gè)查詢會(huì)有兩條記錄，且其中一條記錄并不符合條件select * from tablea where xxno = 170325171202362928;xxno為 170325171202362928 和 170325171202362930的都出現(xiàn)在結(jié)果中。

一個(gè)等值查詢?yōu)槭裁磿?huì)有另外一個(gè)不同值的記錄查詢出來呢？

我們一起來看看究竟！

二、分析

我們查看該表結(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

The following rules describe how conversion occurs for comparison operations: .... 省略一萬字 .... In all other cases, the arguments are compared as floating-point (real) numbers.

也就是說，他會(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è)倏聪伦址?code style="">'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)致精度丟失。MySQL 系列面試題我都整理好了，關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù) "面試" 獲取。

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

三、小結(jié)

在數(shù)據(jù)庫(kù)中進(jìn)行查詢的時(shí)候，不管是Oracle還是MySQL，一定要注意字段類型，杜絕隱式轉(zhuǎn)化，不僅會(huì)導(dǎo)致查詢緩慢，還會(huì)導(dǎo)致結(jié)果錯(cuò)誤。

推薦閱讀：一條垃圾SQL，把 64 核 CPU 快跑崩了！

推薦閱讀
國(guó)產(chǎn)小眾瀏覽器因屏蔽視頻廣告，被索賠100萬（后續(xù)）
年輕人“不講武德”：因看黃片上癮，把網(wǎng)站和786名女主播起訴了
中國(guó)聯(lián)通官網(wǎng)被發(fā)現(xiàn)含木馬腳本，可向用戶推廣色情APP
張一鳴：每個(gè)逆襲的年輕人，都具備的底層能力

關(guān)于程序員大白

程序員大白是一群哈工大，東北大學(xué)，西湖大學(xué)和上海交通大學(xué)的碩士博士運(yùn)營(yíng)維護(hù)的號(hào)，大家樂于分享高質(zhì)量文章，喜歡總結(jié)知識(shí)，歡迎關(guān)注[程序員大白]，大家一起學(xué)習(xí)進(jìn)步！