我在尝试为MySQL编写一个存储过程，当一个服务结束时自动更新相应的记录（每个服务对应一条记录）。原理很简单：用户在参数中提供他们的ID，然后是服务ID及其重复次数。之后，基于这些数据以及服务的开始时间（repetitions表中的start_time列），它应该把结束时间写入end_time列，或在conditionally_complete_time列中写入。此存储过程最复杂的部分是在is_completed变量的值为1 时，将同名的列写入1；如果服务同一天完成则为1，否则（例如延迟完成）为2。另一个功能是能够将conditionally_complete参数设为1，此时服务完成时间将保存在conditionally_complete_time列中。

SQL代码：

CREATE DEFINER=`sparkle`@`%` PROCEDURE `finish_repetition`(
  IN devotion_id INT, IN user_id INT, IN repetition_number INT, 
  IN conditionally_complete INT
)
BEGIN
DECLARE is_completed INT;
DECLARE conditionally_complete_time DATE;
SET 
  is_completed = CASE WHEN (
    SELECT 
      start_time 
    FROM 
      repetitions 
    WHERE 
      devotion_id = devotion_id 
      AND user_id = user_id 
      AND repetition_number = repetition_number
  ) BETWEEN CAST(
    DATE(NOW()) AS DATETIME
  ) 
  AND (
    DATE_ADD(
      CAST(
        DATE(NOW()) AS DATETIME
      ), 
      INTERVAL 86399 SECOND
    )
  ) THEN 1 ELSE 2 END;
SET 
  conditionally_complete_time = IF (
    conditionally_complete = 1 
    AND is_completed = 2, 
    NOW(), 
    NULL
  );
UPDATE 
  devotions a, 
  repetitions b 
SET 
  a.last_refresh_time = NOW(), 
  b.is_completed = is_completed, 
  b.end_time = IF (conditionally_complete != 1, NOW(), NULL), 
  b.conditionally_complete_time = conditionally_complete_time 
WHERE 
  a.devotion_id = devotion_id 
  AND b.devotion_id = devotion_id 
  AND a.user_id = user_id 
  AND b.user_id = user_id 
  AND b.repetition_number = repetition_number;
END

我这样调用存储过程：

CALL finish_repetition(8, 1, 1, 0);

repetitions表预览：

我到底哪里做错了？谢谢。

解决方案

在存储对象中，当你使用的本地变量名与表中同名的列名重复时，该名字总是被视为变量，而带有表别名的名字则被视为列名。

看看你的代码：

CREATE DEFINER=`sparkle`@`%` PROCEDURE `finish_repetition`(
  IN devotion_id INT, 
...
)
BEGIN
... 
    SELECT 
...
    FROM 
      repetitions
    WHERE 
      devotion_id = devotion_id 
...
END

在你的WHERE子句中，你对同一个变量进行了两次引用，这会产生TRUE或 NULL，永远不会得到FALSE。

你必须使用

...
    FROM 
      repetitions
    WHERE 
      devotion_id = repetitions.devotion_id 
...

在这种情况下 devotion_id 是变量的值，而 repetitions.devotion_id 是列的值。

但最佳实践是避免名称重复。例如，可以使用 in_devotion_id 代替 devotion_id。