共计 3414 个字符,预计需要花费 9 分钟才能阅读完成。
某一天,开发问我,为什么针对一个查问会有两条记录,且其中一条记录并不符合条件 select * from tablea where xxno = 170325171202362928;xxno 为 170325171202362928 和 170325171202362930 的都呈现在后果中。
一个等值查问为什么会有另外一个不同值的记录查问进去呢?
咱们一起来看看到底!
剖析
咱们查看该表构造,发现 xxno 为 varchar 类型,然而等号左边是一个数值类型,这种状况下 MySQL 会如何进行解决呢?官网文档如下:https://dev.mysql.com/doc/ref…
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’.
咱们进行测试
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)
同时咱们能够看到,咱们用数值型的 3 和 str_col 进行比拟的时候,他无奈利用索引,同时取出来的值也是谬误的,
MySQL 针对 3c 和 4d 这两个值进行了转化,变成了 3 和 4
小结
在数据库中进行查问的时候,不论是 Oracle 还是 MySQL,肯定要留神字段类型,杜绝隐式转化,不仅会导致查问迟缓,还会导致后果谬误。