冯老师的困惑 —— 一个跑了两年的 BUG

快乐的皮拉夫的个人博客 / 164 / 16 / 创建于 1个月前 / 更新于 1个月前

冯老师发现了 BUG

「嘿，运维，别摸鱼了，来给你看个问题。」

我抬头望了一眼，冯老师兴奋地冲我摆着手，像发现宝藏一样。

「啥问题？忙着呢。」

「一个你们跑了两年的 BUG，给你看看长什么样，来——」

两年的 BUG ？—— 这成功吸引了我的好奇心。于是，我放下手中的工作，向着冯老师的工位走去。

「看看这段代码，能看出什么问题来吗？」

...
//订单编号
$orderSn = '20240522123241209798';
//根据订单编号获取记录 
$order = DB::table('orders')->select("SELECT * FROM `orders` WHERE `order_sn` = {$orderSn} LIMIT 1;");
...

除了使用原生的 SQL 查询让我感觉有些不规范之外，我倒是还发现了一个问题，不过我并没有直接说出来，而是装作一副浑然不知的样子——我倒是要看看冯老师卖的什么关子：

「接着说，冯总。」

冯老师看着我不为所动的表情，脸上开始流露出一丝傲娇的喜悦来：

「来，再给你看看它的执行结果，看出什么问题来了吗？」

…	payer_id	order_sn	order_type	…
…	10	20240522123241209810	3	…

我微微一笑，因为和我预想的一样，我拍了拍冯老师的肩膀：

「好好干，加油。」

然后转身离开了。

冯老师见我一副不为所动的样子，有些失落，本来准备在我面前装一把的，结果又吃了瘪。最后只能无奈地笑着叹了口气：

「你们这群大佬，一个 BUG 线上跑了两年了，居然还没发现。要不是我踩了坑，不知道要坑害多少人啊」

「不过这倒是一个值得研究的话题，你把 SQL 发给我，我好好研究一下。不加引号都能跑这么多年，确实挺有意思的。」

「哈哈，可以啊，小伙子，一眼看到了问题所在。写到你的文章里吧，不过这得算我的功劳啊。」

「没问题，冯老师。」

一个引号引发的「血案」

这个问题其实不难推测，仅从代码上也能看出大致原因 —— WHERE 条件的 {$orderSn} 外面忘记包裹引号了。这样产生的后果，就是会触发 MySQL 的隐式类型转换问题。

不过，我更感兴趣的是以下几点问题：

隐式转换的规则是怎样的？
为何跑了两年的 BUG ，直到现在才暴露出来？
隐式转换对索引是否会产生影响？

为了了解 MySQL 隐式转换的内容，我特意把官方的开发手册《12.3 表达式计算中的类型转换》又翻译了一遍（没找到完整的翻译版，于是当了把志愿者）。看完以后，回头再看这个问题，就比较容易理解了。

所以，强烈建议大家去看一看。

这里我们先建一个表，来模拟这种情况，建表 SQL 如下：

CREATE TABLE `my_table` (
  `str_col` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `int_col` bigint unsigned NOT NULL DEFAULT '0',
  KEY `str_col` (`str_col`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我们定义了一个 varchar 类型的列和一个 bigint 类型的列。然后先插入一条测试数据：

INSERT INTO `my_table` (`str_col`, `int_col`) VALUES ('20240522123130527271', 18446744073709551615);

注意：
InnoDB 存储引擎下，无符号的 BIGINT 类型存储的最大值是 2^64 - 1，即 18446744073709551615 ，这里为了测试特意取的这个值。

我们先来运行第一条 SQL：

mysql> SELECT * FROM `my_table` WHERE `str_col` = 20240522123130527271;
+----------------------+----------------------+
| str_col              | int_col              |
+----------------------+----------------------+
| 20240522123130527271 | 18446744073709551615 |
+----------------------+----------------------+
1 row in set (0.01 sec)

发现可以查询到记录。我们先来分析一下这条 SQL 。

根据《12.3 表达式计算中的类型转换》中提到的：

除此之外的所有情况，参数都会作为浮点数（双精度）进行比较。例如，字符串和数字之间的比较，都是作为浮点数进行比较的。

也就是说，字符串数据列和数字进行比较时，都会先转换成双精度的浮点数。我们使用 CAST() 函数可以模拟这个过程：

mysql> SELECT CAST(20240522123130527271 AS DOUBLE) AS `int_conversion`, CAST('20240522123130527271' AS DOUBLE) AS `str_conversion`;
+-----------------------+-----------------------+
| int_conversion        | str_conversion        |
+-----------------------+-----------------------+
| 2.0240522123130528e19 | 2.0240522123130528e19 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

我们来观察一下这个结果。通过和原始字段对比发现，转换成双精度的浮点数以后，丢失了三位的精度，如果把浮点数再次转换成原来的数字格式的话，就变成了 20240522123130528000 ，其中右起第四位的 8 是一个「近似值」。

我们可以运行以下的 SQL 验证一下：

SELECT 
    CAST(20240522123130520000 AS DOUBLE)  AS `c0`,
    CAST(20240522123130521000 AS DOUBLE)  AS `c1`,
    CAST(20240522123130522000 AS DOUBLE)  AS `c2`,
    CAST(20240522123130523000 AS DOUBLE)  AS `c3`,
    CAST(20240522123130524000 AS DOUBLE)  AS `c4`,
    CAST(20240522123130525000 AS DOUBLE)  AS `c5`,
    CAST(20240522123130526000 AS DOUBLE)  AS `c6`,
    CAST(20240522123130527000 AS DOUBLE)  AS `c7`,
    CAST(20240522123130528000 AS DOUBLE)  AS `c8`,
    CAST(20240522123130529000 AS DOUBLE)  AS `c9`

结果如下：

转换前数据	转换后数据
20240522123130520000	2.024052212313052e19
20240522123130521000	2.024052212313052e19
20240522123130522000	2.0240522123130524e19
20240522123130523000	2.0240522123130524e19
20240522123130524000	2.0240522123130524e19
20240522123130525000	2.0240522123130528e19
20240522123130526000	2.0240522123130528e19
20240522123130527000	2.0240522123130528e19
20240522123130528000	2.0240522123130528e19
20240522123130529000	2.0240522123130528e19

开始我以为右起第四位的取值是向上取整得到，但经过测试发现，实际并非通过四舍五入或者上下取整的方式得到的，这里用的应该是其他的转换规则。

通过测试，我们发现：数字 20240522123130520000 和 20240522123130521000 转换后的浮点值是一样的，这是不是意味着，当表中存在这两条数据时，执行以下的 SQL 查询出来的也是两条数据呢？

mysql> SELECT CAST(`str_col` AS DOUBLE),`str_col` FROM `my_table` WHERE `str_col` = 20240522123130520000;
+---------------------------+----------------------+
| CAST(`str_col` AS DOUBLE) | str_col              |
+---------------------------+----------------------+
|      2.024052212313052e19 | 20240522123130520000 |
|      2.024052212313052e19 | 20240522123130521000 |
+---------------------------+----------------------+
2 rows in set (0.07 sec)

结果和我们预想的一样！

至此，BUG 产生的原因我们已经基本知道了。接下来就是另一个比较有意思的问题了：「为何这个 BUG 跑了两年才暴露出来呢？」

我们系统的订单编号生成规则比较简单，一共由 20 位组成，前 14 位是由 date('YmdHis') 生成，后 6 位是算法生成的随机数字。

经过测试发现，不同 date('YmdHis') 生成的订单号，转换成的双精度浮点数肯定不同。那我们再来做一组测试：

「同一秒内生成的两个订单号，转换成双精度浮点数以后，相同的可能性有多大？」

我的测试思路如下：

生成 20240522123130000000 到 20240522123130999999 的样本数据，共 1,000,000 个。
计算每个样本转换成双精度浮点数以后的数值。
统计每个双精度浮点数出现的次数。
计算随机生成的两个单号，转换成双精度浮点数后，结果相同的概率。

第 1 、2、3 步生成的结果如下（只取部分数据展示）：

转换成的双精度浮点数	出现的次数
2.024052212313e18	65
2.0240522123131e18	95
2.0240522123130002e18	255
2.0240522123130007e18	255
2.0240522123130012e18	255
…	…

这里共有 612 种不同的转换结果。

第 4 步是一个经典的概率问题。举一个简单的场景：

同时抛出两枚硬币，问两枚硬币同为正面或者同为反面的概率有多大？

我们可以列出所有可能出现的情况：

第一枚硬币	第二枚硬币
正面	正面
正面	反面
反面	正面
反面	反面

不难看出，同为正面或者同为反面的概率为 1 / 2 。

如果通过概率计算的方式，就是：P = P_{同为正面的概率} + P_{同为反面的概率} = 1 / 4 + 1 / 4 = 1 / 2 。

如果理解了这个场景，上面那个概率问题也就不难理解了。结果就是：

P = P₁ + P₂ + … + P_N = C 65_100,000 × C 65_100,000 + C 95_100,000 × C 95_100,000 + C 255_100,000 × C 255_100,000 … + C n_100,000 × C n_100,000 ≈ 37 %

由此可见，同一秒内生成的两个单号，「撞库」的概率还不小。

至于这个 BUG 为什么跑了两年才被发现，我想可能因为系统的并发量还没达到，所以一切看似「正常运行」。

如果字段是 INT 类型，查询条件是字符串类型，那是不是也有这样的问题呢？

mysql> SELECT
    CAST( '18446744073709551615' AS DOUBLE ) AS `c1`,
    CAST( '18446744073709551614' AS DOUBLE ) AS `c2`;
+-----------------------+-----------------------+
| c1                    | c2                    |
+-----------------------+-----------------------+
| 1.8446744073709552e19 | 1.8446744073709552e19 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

通过测试，发现 18446744073709551615 和 18446744073709551614 转换成双精度的浮点数都是一样的值，那么如果执行以下 SQL 的话，理论上，查询到的结果也应该是两条：

mysql> SELECT * FROM `my_table`;
+----------------------+----------------------+
| str_col              | int_col              |
+----------------------+----------------------+
| 20240522123130527271 | 18446744073709551615 |
| 20240522123130520000 | 18446744073709551614 |
| 20240522123130521000 |                    0 |
+----------------------+----------------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM `my_table` WHERE `int_col` = '18446744073709551615';
+----------------------+----------------------+
| str_col              | int_col              |
+----------------------+----------------------+
| 20240522123130527271 | 18446744073709551615 |
+----------------------+----------------------+
1 row in set (0.03 sec)

呃，结果和我们预期的居然不同！！！

18446744073709551615 是 64 位的无符号 INT 型能够存储的最大的值。我们使用一条超过临界值的数据进行测试：

mysql> EXPLAIN SELECT * FROM `my_table` WHERE `int_col` = '18446744073709551616';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM `my_table` WHERE `int_col` = 18446744073709551616;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set (0.04 sec)

通过 EXPLAIN 命令测试发现，出现了一个 Impossible WHERE 的告警提示（因为18446744073709551616已经越界）。

再用 EXPLAIN 查看正常的两条数据：

mysql> EXPLAIN SELECT * FROM `my_table` WHERE `int_col` = '18446744073709551615';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | my_table | NULL       | ref  | int_col       | int_col | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM `my_table` WHERE `int_col` = 18446744073709551615;
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | my_table | NULL       | ref  | int_col       | int_col | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)

发现都正常使用了 int_col 索引（如果是字符串列，通过数字参数查询，是不会使用索引的，后面会讲），这也是个奇怪的问题。

当执行以下 SQL 时，又有了新发现：

mysql> SELECT * FROM `my_table` WHERE `int_col` = 'a18446744073709551615';
+----------------------+---------+
| str_col              | int_col |
+----------------------+---------+
| 20240522123130521000 |       0 |
+----------------------+---------+
1 row in set (0.01 sec)

mysql> SELECT * FROM `my_table` WHERE `int_col` = '18446744073709551615a';
+----------------------+----------------------+
| str_col              | int_col              |
+----------------------+----------------------+
| 20240522123130527271 | 18446744073709551615 |
+----------------------+----------------------+
1 row in set (0.00 sec)

查看索引使用情况，发现依然使用了索引：

mysql> EXPLAIN SELECT * FROM `my_table` WHERE `int_col` = '18446744073709551615a';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | my_table | NULL       | ref  | int_col       | int_col | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set (0.03 sec)

mysql> EXPLAIN SELECT * FROM `my_table` WHERE `int_col` = 'a18446744073709551615';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | my_table | NULL       | ref  | int_col       | int_col | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set (0.01 sec)

大胆猜测一下：在进行比较之前，字符串型参数先转换成整数值，然后再和整型列进行比较。这样才能解释通使用索引和查询结果的问题。

不过，这仅仅是我的猜测，具体原因还需要查看更多资料来解决。后续有了答案会详细展开讨论，有了解的小伙伴也可以评论区进行讨论。

最后再来看一下索引问题。

执行以下 SQL ：

mysql> EXPLAIN SELECT * FROM `my_table` WHERE `str_col` = 20240522123130527271;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | my_table | NULL       | ALL  | str_col       | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM `my_table` WHERE `str_col` = '20240522123130527271';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | my_table | NULL       | ref  | str_col       | str_col | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)

通过执行 SQL 发现，当数据列是字符串类型，而查询参数是数字型时，并不会使用索引。

这让我想到以前的一件事情：同事在客户端执行 SQL 查询时，查询列是字符串类型，但是查询条件参数总是习惯性不加引号，实际和现在讨论的情况是一样的。

总结

一个跑了两年的 BUG ，终于被扒了个底朝天。

主要原因在于触发了 MySQL 的隐式转换规则，这会导致一些不可预期的情况：

查询出与预期不一致的数据。
索引失效。

所以，无论是代码中，还是使用客户端进行查询时，都要规范使用 SQL 。虽然你可能一直没有遇到问题，但不定哪一天，这个锅可能就会降到你的头上。

在写这篇文章的时候，莫名其妙想到了下面这张图，突然感觉好形象。

论坛上传图片功能也出 BUG 了～～～～

误删了一大段代码后，发现程序还可以正常运行，这时候我们一定不要动它。

感谢大家的持续关注～

mysql 定时任务

本作品采用《CC 协议》，转载必须注明作者和本文链接

你应该了解真相，真相会让你自由。

本帖由系统于 4周前自动加精

讨论数量: 16

yangweijie

课程读者 51 声望 / 开发工程师 @ 瑞祥科技

说明订单号这种生成不要纯数字拼个字符串就行单数字也查不出来。

4周前评论

快乐的皮拉夫（楼主）

是的，这里容易踩坑。

yyy123456

课程读者 332 声望

使用 laravel 的 orm 应该不会碰到这种情况，另外，订单号怎么着也应该是字符串字段，不应该是整型字段。

是的，ORM 会自动判断并加上引号。订单号都是字符串字段，这里是为了做实验，故意设置的参照字段。

weize

@快乐的皮拉夫我这边也出现这种问题了，但是我在文档里没找到laravel orm对这种字段格式的处理

大孟

3 声望

很实用，遇到过这种问题。

哈哈，姗姗来迟～

陈先生

课程读者 645 声望 / PHP_EOL @ NaN

:sweat_smile: 让我好奇的不是这个bug，而不是为什么要这么写。 DB 提供了完整的链式调用和条件查询操作，这段代码完全是舍近求远的操作。我的评价是：bug 的本质不是文中所说的bug，而是这段代码。

怎么说呢，这就好比郭靖学武功一样，如果按倒序来的话，先学会了降龙十八掌，可能就看不上江南七怪教的那些杂牌武功了。不过这也不能否定那些杂牌武功的作用，毕竟人家江南七怪也是靠着这些功夫行走了一辈子江湖呢。其实，真实的情况是，我只选取了出问题的一句 SQL，实际上这是一个非常复杂的 SQL，估计也是因为太过复杂，所以才会使用这种方式的。

徵羽宫

课程读者 298 声望

一眼看到了引号的问题，后面是不是不用看了。

不能错过任何进步的机会呀，哈哈～

不愧是大佬，一眼发现问题

随波逐流

课程读者 169 声望 / 南波万 @ 峨眉山类人猿洞

同时也反应出，订单数据量不大，查询效率影响不高。

要不单单从数据类型导致索引失效的问题引发查询时间过长，客服没有骂娘就不错了。

是的，都是隐藏的坑。

lairdutemps

9 声望

字符串被强行转int了，坑

3周前评论

天天跟扫雷一样，刺激。

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

冯老师的困惑 —— 一个跑了两年的 BUG

冯老师发现了 BUG

一个引号引发的「血案」

总结

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

冯老师的困惑 —— 一个跑了两年的 BUG

冯老师发现了 BUG

一个引号引发的「血案」

总结

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录