数据类型不一致导致删除多余数据问题
1、问题简介
由于定时调度删除数据的SQL中,查询条件的参数类型为数值类型,而数据库中的字段为字符类型,导致查询出了条件之外的数据,从而导致删除了多余的数据。
2、场景模拟
测试使用的数据如下,其中BILLID字段为varchar类型:
ID | BILLID |
---|---|
1 | 1556610551389761201 |
2 | 1556610551389761202 |
3 | 1556610551389761203 |
4 | 1556610551389761204 |
5 | 1556610551389761205 |
6 | 1556610551389761206 |
7 | 1556610551389761207 |
8 | 1556610551389761208 |
9 | 1556610551389761209 |
10 | 1556610551389761210 |
11 | 9007199254740992 |
12 | 9007199254740993 |
13 | 9007199254740994 |
而使用数值参数从此表中过滤时,会查询出条件外的数据:
3、解决办法
查询条件中使用与数据表字段类型一致的参数值即可:
select * from bill where billid in ('1556610551389761205', '1556610551389761206');
4、原因分析
在MySQL中,当运算符与不同类型的操作数一起使用时,将进行类型转换以使操作数兼容;有些转换是隐式发生的。转换规则如下:
-
如果一个或两个参数都为NULL,则比较的结果为NULL;除了
<=>
比较运算符。对于NULL <=> NULL
,结果为true,不需要转换。 -
如果两个参数都是字符串,则将它们作为字符串进行比较。
-
如果两个参数都是整数,则将它们作为整数进行比较。
-
如果十六进制值和非数字进行比较,则将十六进制值视为二进制字符串。
-
如果其中一个参数是TIMESTAMP或DATETIME列,而另一个参数是常量,则该常量将在执行比较之前转换为时间戳。
-
如果其中一个参数是十进制值,则比较取决于另一个参数;如果另一个参数是十进制或整数值,则将这些参数作为十进制值进行比较; 如果另一个参数是浮点值,则将这些参数作为浮点值进行比较。
-
在所有其他情况下,参数都作为浮点数(双精度)进行比较。例如,字符串和数值操作数的比较是作为浮点数的比较进行的。
浮点数与整数类型较大的值之间的比较是近似的,因为在比较之前,整数会被转换为双精度浮点数,但浮点数无法准确表示所有64位整数。例如,253+1 不能表示为浮点数,并且在浮点数比较之前四舍五入到 253 或 253+2 ,具体取决于平台。
Math.pow(2,53)=9007199254740992
执行下面两句SQL:
select billid from bill where billid = 9007199254740993;
select billid from bill where billid = 9007199254740992;
查询结果相同:
9007199254740992
9007199254740993
将9007199254740992和9007199254740993转为浮点数的值都是:9.007199254740992e15
。
SELECT '9007199254740992'+0.0
SELECT '9007199254740993'+0.0
因此SELECT '9007199254740992' = 9007199254740993
返回1,说明它们在比较时认为相等。
下面是9007199254740992、9007199254740993、9007199254740994的二进制表示:
9007199254740992:
53
|
100000000000000000000000000000000000000000000000000000
9007199254740993:
53
|
100000000000000000000000000000000000000000000000000001
9007199254740994:
53
|
100000000000000000000000000000000000000000000000000010