1、bug复现

bug出现的位置在一个根据id查询的代码，简化代码如下：

image-20240120203825914

其中user.getId()是有值的，id是主键。按照道理来说，根据id查询，一般来说，要么就查询出一个数据，要么就查不出，不应该会在查询抛出错误。然而，这里却抛出了如下错误：

image-20240120204350093

也就是说根据id查询，查出了3条记录！真是神奇

2、定位问题

先打断点获取id看有没有问题

image-20240120204656716

好像并没有什么问题，将id复制到数据库筛选看

image-20240120204832168

这也没啥问题啊。

最后没办法，只能先把SQL打印出来看看

image-20240120205243506

将SQL复制到数据库直接执行，发现居然查到了3行数据！

image-20240120205438443

明明id都不一样，为什么能查出来？

弄来弄去，发现给id值加上引号就能正确查询

image-20240120210300981

最后对比发现，数据库id的数据格式是varchar，而实体类的id定义成了Long

image-20240120210508903

image-20240120210529109

最后把Long类型的id改成String类型，问题解决

3、MySQL隐式转换

3.1 隐式转换

MySQL在执行操作SQL语句时，会自动的将一数据类型转换成另外的数据类型，这种情况就是隐式转换。

官网文档：

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly.

当运算符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。

官网例子：

1
2
3
4
mysql> SELECT 1 +'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

第一个例子中，把字符的’1’变成了数字的1，第二个例子中，把数字的2转换成了字符的’2’。

与隐式转换相对的就是显示转换，在MySQL中我们通常使用 CAST函数来进行显示转换，因此上述的例子使用显示转换等同于：

1
2
3
4
mysql> SELECT 1 + CAST('1' AS UNSIGNED);
        -> 2
mysql> SELECT CONCAT(CAST(2 AS CHAR),' test');
        -> '2 test'
3.2转换规则

官网中描述的转换规则如下：

The following rules describe how conversion occurs for comparison operations:

  • If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

  • If both arguments in a comparison operation are strings, they are compared as strings.

  • If both arguments are integers, they are compared as integers.

  • Hexadecimal values are treated as binary strings if not compared to a number.

  • If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

    A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

  • If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

  • In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

如果比较参数中有NULL，那结果也是NULL，除非使用的<=>比较符来比较，比如NULL <=> NULL的结果为真，不需要转换。

也就是说，如果参数要和NULL比较，除非使用的<=>比较符来比较，不然结果都是NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT 1 > NULL;
        -> NULL

mysql> SELECT 1 = NULL;
        -> NULL

mysql> SELECT NULL = NULL;
        -> NULL

mysql> SELECT 1 <=> NULL;
        -> 0

mysql> SELECT NULL <=> NULL;
        -> 1

因此我们在查询某个字段为NULL的数据时，不能使用字段=NULL来查询，如：

1
SELECT * FROM `user` WHERE user_name=NULL;

即使存在user_name为NULL的数据，这个SQL也查询不出来，应该改为

1
SELECT * FROM `user` WHERE user_name<=>NULL;

或者还可以使用IS NULL

1
SELECT * FROM `user` WHERE user_name IS NULL;
如果比较的两个参数都是字符串，它们将作为字符串比较。

在进行两个字符串比较时，MySQL会按照ASCII码的顺序进行比较

1
2
3
4
5
SELECT '1' > 'a';
	-> 0 '1'的ASCII码是49，'a'的ASCII码是97
	
SELECT 'bca' > 'bc1';
	-> 1
如果比较的两个参数都是整数，它们将作为整数比较。
十六进制的值和非数字做比较时，会被当做二进制串。
1
2
SELECT 0x61 = 'a';
	-> 1 0x61是十六进制，转成十进制是97，是'a'的ASCII码
如果其中一个参数是TIMESTAMP或DATETIME列，而另一个参数为常量，则在执行比较之前，该常量将转换为时间戳。这样做是为了对ODBC更加友好。对于IN( ) 的参数，并没有这样做。为了安全起见，在进行比较时，请始终使用完整的日期时间、日期或时间字符串。例如，为了在将BETWEEN与日期或时间值一起使用时获得最佳结果。
来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回一个要与DATETIME 值进行比较的整数，则比较将作为两个整数进行。整数不会转换为时间值。要将操作数作为 DATETIME值进行比较，请使用 CAST()将子查询值显式转换为DATETIME。
如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较；如果另一参数是浮点值，则将其作为浮点值进行比较。
在所有其他情况下，参数将作为浮点（双精度）数字进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。

4、问题原因分析

在本次的踩坑中，字符串和数字就是转换成了浮点数进行比较。

本次问题的原因简单来说就是下述的SQL结果为真

1
2
SELECT 1234456789012345619 = '1234456789012345618';
	-> 1

既然是隐式的将数字和字符串都转成了浮点数造成的，那我们就显示的转化看看

1
2
3
4
5
SELECT CAST(1234456789012345619 AS DOUBLE);
	-> 1.2344567890123456e18

SELECT CAST('1234456789012345618' AS DOUBLE);
	-> 1.2344567890123456e18

由此我们可以看到，当转成浮点数时，是以科学计算法的形式，并且保存的是17位有效数字。而上述的数字和字符经过转换后的浮点数是一样的。

但是并不是单纯的截取17位数后进行四舍五入，而是会有一定的误差，如：

1
2
3
4
5
SELECT CAST('123445678901234568' AS DOUBLE);
	-> 1.2344567890123456e17

SELECT CAST(123445678901234569 AS DOUBLE);
	-> 1.2344567890123458e17

所以最好就是数据库表的数据格式和实体类的字段要一一对应

ps:以上数据库版本是MySQL8版本