乐趣区

关于java:MySQL-隐式转换的坑一起来看看究竟

作者:Harvey\
起源:https://www.fordba.com/mysql-…

一、前言

某一天,开发问我,为什么针对一个查问会有两条记录,且其中一条记录并不符合条件
select * from tablea where xxno = 170325171202362928;
xxno170325171202362928170325171202362930的都呈现在后果中。

一个等值查问为什么会有另外一个不同值的记录查问进去呢?咱们一起来看看到底!


二、剖析

咱们查看该表构造,发现xxnovarchar 类型,然而等号左边是一个数值类型,这种状况下 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:

如果比拟应用了浮点型,那么比拟会是近似的,将导致后果看起来不统一,也就是可能导致查问后果谬误。

咱们测试下刚刚生产的例子:

mysql > select '170325171202362928' = 170325171202362930;
+-------------------------------------------+
| '170325171202362928' = 170325171202362930 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

能够发现,字符串的 '170325171202362928' 和 数值的170325171202362930 比拟居然是相等的。

咱们再看下字符串'170325171202362928' 和字符串'170325171202362930' 转化为浮点型的后果

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)

咱们发现,将两个不同的字符串转化为浮点数后,后果是一样的,

所以只有是转化为浮点数之后的值是相等的,那么,通过隐式转化后的比拟也会相等,咱们持续进行测试其余转化为浮点型相等的字符串的后果。

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'转化为浮点型后果一样,咱们看下他们和数值的比拟后果。

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)

后果也是合乎预期的。

因而,当 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)

同时咱们能够看到,咱们用数值型的 3str_col进行比拟的时候,他无奈利用索引,同时取出来的值也是谬误的:

mysql  > show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |
+---------+------+----------------------------------------+

MySQL 针对 3c4d 这两个值进行了转化,变成了 34


三、小结

在数据库中进行查问的时候,不论是 Oracle 还是 MySQL,肯定要留神字段类型,杜绝隐式转化,不仅会导致查问迟缓,还会导致后果谬误。

关注公众号 Java 技术栈,在后盾回复:面试,能够获取我整顿的 MySQL 系列面试题和答案。

近期热文举荐:

1.600+ 道 Java 面试题及答案整顿(2021 最新版)

2. 终于靠开源我的项目弄到 IntelliJ IDEA 激活码了,真香!

3. 阿里 Mock 工具正式开源,干掉市面上所有 Mock 工具!

4.Spring Cloud 2020.0.0 正式公布,全新颠覆性版本!

5.《Java 开发手册(嵩山版)》最新公布,速速下载!

感觉不错,别忘了顺手点赞 + 转发哦!

退出移动版