关于mysql:这次被坑惨了MySQL的隐式转换导致了一个线上BUG

55次阅读

共计 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,肯定要留神字段类型,杜绝隐式转化,不仅会导致查问迟缓,还会导致后果谬误。

正文完
 0