MySQL存储过程执行后莫名返回查询中断问题排查

核心错误点 #

1. 参数与字段同名导致条件失效 #

存储过程的输入参数devotion_id、user_id、repetition_number与repetitions表的字段名完全重复，MySQL解析时会优先使用表字段而非输入参数。例如WHERE devotion_id = devotion_id会被解析为repetitions.devotion_id = repetitions.devotion_id，永远为真：

• 子查询SELECT start_time FROM repetitions ...会返回该用户所有的repetitions记录，触发Subquery returns more than 1 row错误；
• UPDATE的WHERE条件无法精准匹配目标记录，可能误更新多条数据。

2. 当日完成的判断逻辑颠倒 #

需求是服务在开始日期的当日完成则标记is_completed=1，逾期完成则标记为2，但当前代码判断的是start_time是否在当前日期范围内，逻辑完全相反。正确逻辑应判断完成时间（NOW()）是否与start_time的日期一致。

3. 变量类型不匹配 #

声明conditionally_complete_time为DATE类型，但赋值使用NOW()（返回DATETIME类型），会自动截断时间部分，无法完整记录完成的具体时间，应改为DATETIME类型。

4. 表连接方式不规范 #

使用逗号连接devotions和repetitions属于旧版SQL语法，且未明确关联逻辑，存在产生笛卡尔积的风险，应使用显式INNER JOIN。

修正后的存储过程代码 #

CREATE DEFINER=`sparkle`@`%` PROCEDURE `finish_repetition`(
  IN p_devotion_id INT, 
  IN p_user_id INT, 
  IN p_repetition_number INT, 
  IN p_conditionally_complete INT
)
BEGIN
DECLARE v_is_completed INT;
DECLARE v_conditionally_complete_time DATETIME;

-- 获取目标记录的start_time，避免参数与字段同名冲突
SELECT start_time INTO @start_time
FROM repetitions 
WHERE devotion_id = p_devotion_id 
  AND user_id = p_user_id 
  AND repetition_number = p_repetition_number;

-- 修正当日完成的判断逻辑：完成时间与start_time日期一致则为1，否则为2
SET v_is_completed = CASE 
  WHEN DATE(NOW()) = DATE(@start_time) THEN 1 
  ELSE 2 
END;

-- 修正条件完成时间的赋值逻辑（若需求为仅逾期时存入，可保留AND v_is_completed=2）
SET v_conditionally_complete_time = IF(
  p_conditionally_complete = 1, 
  NOW(), 
  NULL
);

-- 使用显式JOIN更新，避免笛卡尔积
UPDATE devotions a
INNER JOIN repetitions b 
  ON a.devotion_id = p_devotion_id 
  AND a.user_id = p_user_id 
  AND b.devotion_id = p_devotion_id 
  AND b.user_id = p_user_id 
  AND b.repetition_number = p_repetition_number
SET 
  a.last_refresh_time = NOW(), 
  b.is_completed = v_is_completed, 
  b.end_time = IF(p_conditionally_complete != 1, NOW(), NULL), 
  b.conditionally_complete_time = v_conditionally_complete_time;

END

关键修改说明 #

• 参数前缀加p_，变量前缀加v_，彻底避免与表字段名冲突；
• 先通过SELECT ... INTO获取目标记录的start_time，避免子查询返回多行的问题；
• 修正is_completed的判断逻辑，对比完成时间与start_time的日期；
• 将v_conditionally_complete_time改为DATETIME类型，完整记录完成时间；
• 使用INNER JOIN替代逗号连接，明确表关联关系，消除误更新风险；
• 若需求为仅当逾期且参数为1时才存入conditionally_complete_time，可在IF判断中添加AND v_is_completed=2。

内容的提问来源于stack exchange，提问作者Sparkle