0%

MySQL的类型转换(比较)

查询数据时出现了异常,查询结果中包含不相关的数据
结果是MySQL默认的比较时类型转换造成
比较类型不同时,(int,char,decimal,datetime等)对应不同的策略

原因

MySQL5.7文档中有介绍:

  1. 如果其中一个或两个参数都是NULL,则结果是NULL,除非使用NULL安全的<=>
  2. 两边参数都是字符串,则按照字符串比较
  3. 两边参数都是整型,则按照整型比较
  4. 十六进制不与数字比较时,将被视为二进制字符串
  5. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
  6. 一个参数是 decimal 类型,另外也是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
  7. 其他情况下,两个参数都将被转为浮点数后进行比较

表中有一列数据为bigint类型,但字段类型被设置为varchar
所以当使用bigint类型数据去匹配时
会被转换为floating-point (real) numbers,即double类型,进行比较,例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT '25181511871369695'+0;
+-----------------------+
| '25181511871369695'+0 |
+-----------------------+
| 2.5181511871369696e16 |
+-----------------------+
1 row in set

mysql> SELECT '25181511871369694'+0;
+-----------------------+
| '25181511871369694'+0 |
+-----------------------+
| 2.5181511871369696e16 |
+-----------------------+
1 row in set

mysql> select '25181511871369695' = 25181511871369694;
+-----------------------------------------+
| '25181511871369695' = 25181511871369694 |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set

从而造成查询到不相关的数据

解决方法

使用CAST转换类型

在查询语句中转换

1
2
3
4
5
6
7
mysql> select '25181511871369695' = CAST(25181511871369694 AS char);
+-------------------------------------------------------+
| '25181511871369695' = CAST(25181511871369694 AS char) |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
1 row in set

或者在代码中转换皆可

修改为正确的字段类型

检查数据,确认对业务无影响,直接修改字段类型

参考链接

Type Conversion in Expression Evaluation
原文链接