MySQL 隐式转换引起的执行结果错误

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

今天给大家，带来的例子是 MySQL Join过程中发生隐士转换导致结果跟预期的不一样的问题。

下面是 即将使用的表和插入数据的 脚本

create table a (nntx_no int ) ;
create table b (nntx_no varchar(20) ) ;
root@mysql3306.sock>[test]>insert into a values (1772 ) ;
Query OK, 1 row affected (0.00 sec)
root@mysql3306.sock>[test]>insert into a values (1773 ) ;
Query OK, 1 row affected (0.00 sec)
root@mysql3306.sock>[test]>insert into b values ('1772' ) ;
Query OK, 1 row affected (0.00 sec)
root@mysql3306.sock>[test]>insert into b values ('1772.0 ~ 34' ) ;
Query OK, 1 row affected (0.01 sec)

下面是表数据

root@mysql3306.sock>[test]>select * from a ;
+---------+
| nntx_no |
+---------+
| 1772 |
| 1773 |
+---------+
2 rows in set (0.00 sec)
root@mysql3306.sock>[test]>select * from b ;
+-------------+
| nntx_no |
+-------------+
| 1772 |
| 1772.0 ~ 34 |
+-------------+
2 rows in set (0.00 sec)

运行如下SQL

select a.nntx_no, b.nntx_no
 -> from a
 -> left join b on a.nntx_no = b.nntx_no
 -> where 1=1
 -> and a.nntx_no = 1772;
+---------+-------------+
| nntx_no | nntx_no |
+---------+-------------+
| 1772 | 1772 |
| 1772 | 1772.0 ~ 34 |
+---------+-------------+

可以看到出了 两行数据，但是其中第二行数据 显然不符合我们的预期

但MySQL却给我们了一个看似错误的结果！！！

现在我们来分析，这个结果的原因

root@mysql3306.sock>[test]>select a.nntx_no, b.nntx_no
 -> from a
 -> left join b on a.nntx_no = b.nntx_no
 -> where 1=1
 -> and a.nntx_no = 1772;
+---------+-------------+
| nntx_no | nntx_no |
+---------+-------------+
| 1772 | 1772 |
| 1772 | 1772.0 ~ 34 |
+---------+-------------+
2 rows in set, 2 warnings (0.00 sec)
root@mysql3306.sock>[test]>show warnings\G
*************************** 1. row ***************************
 Level: Warning
 Code: 1292
Message: Truncated incorrect DOUBLE value: '1772.0 ~ 34'
*************************** 2. row ***************************
 Level: Warning
 Code: 1292
Message: Truncated incorrect DOUBLE value: '1772.0 ~ 34'
2 rows in set (0.00 sec)

从show warnings 中 可以看出一些端倪 有两个warning

显示 Truncated incorrect DOUBLE value: '1772.0 ~ 34'

说明 ‘1772.0 ~ 34’ 转换过程中，被截断了

我们坐下如下实验

root@mysql3306.sock>[test]>select '1772.0 ~ 34' + 0 ;
+-------------------+
| '1772.0 ~ 34' + 0 |
+-------------------+
| 1772 |
+-------------------+

说明 ‘1772.0 ~ 34’ 字符串转换成数字的过程中 被转换成1772 所以MySQL 给我们

得出了上面的结果！

那原因，已经知道了 剩下的就是怎样处理了！

我们从上面的结果中已经知道，原因是字符串变成数字的过程中被截取导致的，

那解决方案就是，不进行数字类型转换就可以了。

如下所示，用了 concat(a.nntx_no,’’) 使数字类型变成字符串类型，从而字符串=字符串

这样就可以了 ！！！

root@mysql3306.sock>[test]>select a.nntx_no, b.nntx_no
 -> from a
 -> left join b on concat(a.nntx_no,'') = b.nntx_no
 -> where 1=1
 -> and a.nntx_no = 1772;
+---------+---------+
| nntx_no | nntx_no |
+---------+---------+
| 1772 | 1772 |
+---------+---------+
1 row in set (0.00 sec)

谢谢大家~ 欢饮转发

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ： 579036588

欢迎加入 知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

我会在头条提供相应例子的视频，希望大家关注

头条号