某一天,開發問我,為什麼針對一個查詢會有兩條記錄,且其中一條記錄並不符合條件select * from tablea where xxno = 170325171202362928;xxno為 170325171202362928 和 170325171202362930的都出現在結果中。
一個等值查詢為什麼會有另外一個不同值的記錄查詢出來呢?
我們一起來看看究竟!
分析
我們查看該表結構,發現xxno 為varchar 類型,但是等號右邊是一個數值類型,這種情況下MySQL會如何進行處理呢?官方文檔如下: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.
也就是說,他會將等於號的兩邊轉換成浮點數來做比較。
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:
如果比較使用了浮點型,那麼比較會是近似的,將導致結果看起來不一致,也就是可能導致查詢結果錯誤。
我們測試下剛剛生產的例子:
可以發現,字符串的'170325171202362928' 和 數值的170325171202362930比較竟然是相等的。我們再看下字符串'170325171202362928' 和字符串'170325171202362930' 轉化為浮點型的結果
我們發現,將兩個不同的字符串轉化為浮點數後,結果是一樣的,
所以只要是轉化為浮點數之後的值是相等的,那麼,經過隱式轉化後的比較也會相等,我們繼續進行測試其他轉化為浮點型相等的字符串的結果
字符串'170325171202362931'和'170325171202362941'轉化為浮點型結果一樣,我們看下他們和數值的比較結果
結果也是符合預期的。
因此,當MySQL遇到字段類型不匹配的時候,會進行各種隱式轉化,一定要小心,有可能導致精度丟失。
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:
如果字段是字符型,且上面有索引的話,如果查詢條件是用數值來過濾的,那麼該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'.
我們進行測試
<code>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)/<code>
同時我們可以看到,我們用數值型的3和str_col進行比較的時候,他無法利用索引,同時取出來的值也是錯誤的,
MySQL針對3c 和 4d這兩個值進行了轉化,變成了3和4
小結
在數據庫中進行查詢的時候,不管是Oracle還是MySQL,一定要注意字段類型,杜絕隱式轉化,不僅會導致查詢緩慢,還會導致結果錯誤。