另一个“#1054:“字段列表"中的未知列"神秘 [英] Another "#1054: Unknown column in 'field list'" mystery

问题描述

已寻找任何解决方案，但未成功... 神秘之处在于我的一些功能.我在下面介绍了其中两个.它们基本相同，但Fun1可以正常工作，而Fun2则不能.该错误与主题-字段列表"中的未知列"中的错误相同.我注意到，它取决于声明的变量类型-如果它是字符串(如tadnotation)，则不会遇到问题，如果它是时间，十进制，枚举，则会导致错误.有人可以向我解释吗?当然，我也在寻找解决方案，因此欢迎大家提出建议.

Have looked for any solution but unsuccessfully... The mystery is about body of my few functions. I presented two of them below. They are pretty the same but Fun1 works fine and Fun2 doesn't. The error is as in topic - "Unknown column in 'field list'". I've noticed that it rather depends on declared variable type - if it's string (like tadnotation) there is no problem met, if it's time, decimal, enum, it causes errors. Can someone explain it to me? And of course I'm also searching for the solution, so every tip is welcome.

每个功能的输入均相同:

Inputs are the same for every function:

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

Fun1:

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$$

Fun2:

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"相似):

Table (only 'daily_operation' because 'monthly_operation' is similar):

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子句:

What works is if I change SET-CASE clause with:

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;

推荐答案

对于我来说，使用

For me assignment with CASE expression is a bit confusing (hard to read) so I'd use CASE clause with INTO @var instead:

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进行DECLARE.

Note, you do not need to DECLARE @session_variables used in SP.

这篇关于另一个“#1054:“字段列表"中的未知列"神秘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！