mysql - 又一个 "#1054: Unknown column in '字段列表'”之谜

Question

一直在寻找任何解决方案，但没有成功......谜团是关于我的几个功能的主体。我在下面介绍了其中两个。它们非常相似，但 Fun1 工作正常而 Fun2 不工作。错误如主题 - “'field list' 中的未知列”。我注意到它更依赖于声明的变量类型——如果它是字符串(如标记法)则没有遇到任何问题，如果它是时间、小数、枚举，则会导致错误。有人可以向我解释吗？当然，我也在寻找解决方案，因此欢迎大家提出建议。

每个函数的输入都是相同的:

(`vtable` ENUM('user','client', 'daily_operation','monthly_operation'), 
`vclient_id` SMALLINT,
`vuser_id` SMALLINT, 
`vid` INT,
`vedition_id` TINYINT)

乐趣1:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `getAdnotation` (`vtable` ENUM('user', 'client', 'daily_operation', 'monthly_operation'), `vclient_id` SMALLINT, `vuser_id` SMALLINT, `vid` INT, `vedition_id` TINYINT) RETURNS TINYTEXT CHARSET utf8
BEGIN

DECLARE tadnotation TINYTEXT;

SET @tadnotation = CASE
    WHEN vtable = 'daily_operation' THEN 
        (SELECT adnotation FROM daily_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1)
    WHEN vtable = 'monthly_operation' THEN 
        (SELECT adnotation FROM monthly_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1)
END;
RETURN @tadnotation;

END$$

乐趣2:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `getStartTime` (`vtable` ENUM('user', 'client', 'daily_operation', 'monthly_operation'), `vclient_id` SMALLINT, `vuser_id` SMALLINT, `vid` INT, `vedition_id` TINYINT) RETURNS TIME
BEGIN

DECLARE tstart_time TIME;

SET @tstart_time = CASE
    WHEN vtable = 'daily_operation' THEN 
        (SELECT start_time FROM daily_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1)
    WHEN vtable = 'monthly_operation' THEN 
        (SELECT start_time FROM monthly_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1)
END;
RETURN @tstart_time;

END$$

表(只有'daily_operation'因为'monthly_operation'是相似的):

CREATE TABLE `daily_operation` (
  `client_id` smallint(6) NOT NULL,
  `user_id` smallint(6) NOT NULL,
  `id` int(11) NOT NULL,
  `edition_id` tinyint(4) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `duration_minutes` smallint(6) NOT NULL,
  `duration_hours` decimal(4,2) NOT NULL,
  `adnotation` tinytext
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果我将 SET-CASE 子句更改为:

IF(vtable = 'daily_operation') THEN
    SET @tstart_time = (SELECT start_time FROM daily_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1);
ELSEIF(vtable = 'monthly_operation') THEN
    SET @tstart_time = (SELECT start_time FROM monthly_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1);
END IF;

Answer 1

为我分配CASE expression有点困惑(难以阅读)所以我会使用 CASE clause 改为使用 INTO @var:

BEGIN
CASE
    WHEN vtable = 'daily_operation' THEN 
        SELECT start_time INTO @result FROM daily_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1;
    WHEN vtable = 'monthly_operation' THEN 
        SELECT start_time INTO @result FROM monthly_operation WHERE (client_id = vclient_id AND user_id = vuser_id AND id = vid AND edition_id = vedition_id) LIMIT 1;
END CASE;

RETURN @result;
END

请注意，您不需要声明在 SP 中使用的@session_variables。