共计 5224 个字符，预计需要花费 14 分钟才能阅读完成。
作者：Harvey\
起源：https://www.fordba.com/mysql-…
一、前言
某一天，开发问我，为什么针对一个查问会有两条记录，且其中一条记录并不符合条件 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:
如果比拟应用了浮点型，那么比拟会是近似的，将导致后果看起来不统一，也就是可能导致查问后果谬误。
咱们测试下刚刚生产的例子：
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)同时咱们能够看到，咱们用数值型的 3 和str_col进行比拟的时候，他无奈利用索引，同时取出来的值也是谬误的：
mysql > show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |
+---------+------+----------------------------------------+MySQL 针对 3c 和 4d 这两个值进行了转化，变成了 3 和4
三、小结
在数据库中进行查问的时候，不论是 Oracle 还是 MySQL，肯定要留神字段类型，杜绝隐式转化，不仅会导致查问迟缓，还会导致后果谬误。
关注公众号 Java 技术栈，在后盾回复：面试，能够获取我整顿的 MySQL 系列面试题和答案。
近期热文举荐：
1.600+ 道 Java 面试题及答案整顿(2021 最新版)
2. 终于靠开源我的项目弄到 IntelliJ IDEA 激活码了，真香！
3. 阿里 Mock 工具正式开源，干掉市面上所有 Mock 工具！
4.Spring Cloud 2020.0.0 正式公布，全新颠覆性版本！
5.《Java 开发手册（嵩山版）》最新公布，速速下载！
感觉不错，别忘了顺手点赞 + 转发哦！
