MySQL系列：隐式类型转化可能带来的坑

在开发规范中，我们往往会要求研发避免在where条件中出现隐式类型转换，这么要求大概有以下两方面的原因：

  1. 隐式类型转换可能导致索引失效；
  2. 隐式类型转换可能产生非预期的结果。

注：这里说的是隐式类型转换。

我们可以看下官方关于类型转换的解释：

这里讨论以下情况：

In all other cases, the arguments are compared as floating-point (real) numbers.

 

回顾：一次研发提供过来的数据修正的sql：

1 update tbjxxxxaccout set balancexxx=balancexxx+0.1 where bankxxx=6222000233022332111;

即，根据银行电子账户更新账户余额信息。执行后发现更新了多条数据（电子账户是唯一的），然后回滚操作；重新检查sql，发现bankxxx的条件忘记加引号，正确的sql应该是：

1 update tbjxxxxaccout set balancexxx=balancexxx+0.1 where bankxxx='6222000233022332111';

bankxxx的字段类型为varchar(20)，在没有加引号的情况下发生了隐式数据类型转换，按官方的解释：这种情况的比较会转换为浮点型数据再去比较，即6222000233022332111会转化为string类型再转化为float类型来比较（注：至于为什么会先转换成string再转换成float，这其中内部机制我也没搞明白，有待考证）。

 

下面造几个数据做测试：

 1 CREATE TABLE `t_zw1` (
 2 
 3   `id` int(11) DEFAULT NULL,
 4 
 5   `account_id` char(19) DEFAULT NULL,
 6 
 7   `balance_amount` decimal(18,2) DEFAULT NULL
 8 
 9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10 
11 
12 
13 insert into t_zw1 values(1, '6222000233022332111', 18.9);
14 
15 insert into t_zw1 values(2, '6222000233022332211', 108.1);
16 
17 insert into t_zw1 values(3, '6222000233022332311', 180.3);
18 
19 insert into t_zw1 values(4, '6222000233022334211', 2.3);
20 
21 insert into t_zw1 values(5, '6222000233022334311', 33.3);
22 
23 
24 
25 root@zow 10:09:34>select * from t_zw1;
26 
27 +------+---------------------+----------------+
28 
29 | id   | account_id  | balance_amount |
30 
31 +------+------------------------------+----------------+
32 
33 |    1 | 6222000233022332111 |          18.90 |
34 
35 |    2 | 6222000233022332211 |         108.10 |
36 
37 |    3 | 6222000233022332311 |         180.30 |
38 
39 |    4 | 6222000233022334211 |           2.30 |
40 
41 |    5 | 6222000233022334311 |          33.30 |
42 
43 +------+---------------------+------------------------+

我们看下account为6222000233022332111 的情况：

 1 root@zow 10:17:02>select * from t_zw1 where account_id='6222000233022332111';
 2 
 3 +------+---------------------+----------------+
 4 
 5 | id   | account_id          | balance_amount |
 6 
 7 +------+---------------------+----------------+
 8 
 9 |    1 | 6222000233022332111 |          18.90 |
10 
11 +------+---------------------+----------------+
12 
13 1 row in set (0.00 sec)
14 
15 
16 
17 root@zow 10:17:45>select * from t_zw1 where account_id=6222000233022332111;
18 
19 +------+---------------------+----------------+
20 
21 | id   | account_id          | balance_amount |
22 
23 +------+---------------------+----------------+
24 
25 |    1 | 6222000233022332111 |          18.90 |
26 
27 |    2 | 6222000233022332211 |         108.10 |
28 
29 |    3 | 6222000233022332311 |         180.30 |
30 
31 +------+---------------------+----------------+
32 
33 3 rows in set (0.00 sec)
34 
35 
36 
37 root@zow 10:18:59>select account_id, account_id+0.0, if(6222000233022332111=account_id, 1, 0) from t_zw1;
38 
39 +---------------------+----------------------+------------------------------------------+
40 
41 | account_id          | account_id+0.0       | if(6222000233022332111=account_id, 1, 0) |
42 
43 +---------------------+----------------------+------------------------------------------+
44 
45 | 6222000233022332111 | 6.222000233022332e18 |                                        1 |
46 
47 | 6222000233022332211 | 6.222000233022332e18 |                                        1 |
48 
49 | 6222000233022332311 | 6.222000233022332e18 |                                        1 |
50 
51 | 6222000233022334211 | 6.222000233022334e18 |                                        0 |
52 
53 | 6222000233022334311 | 6.222000233022334e18 |                                        0 |
54 
55 +---------------------+----------------------+------------------------------------------+

可以看出转化为float类型后，由于浮点类型精度的问题，红字的三条件记录转换后的值（约数）是一样的。

 总结：隐式类型转换会带了很多不确定的结果，一定要杜绝where条件中隐式转换。

 

参考：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

 

posted @ 2017-12-26 15:40  ZhangJianHua  阅读(2260)  评论(0)  编辑  收藏  举报