在 VIEW 中使用的 Mysql 函数不会返回正确的结果。

Question

我面临一个奇怪的问题(我试图寻找以前的答案，但我找不到任何东西)。我创建了这个函数:

CREATE DEFINER=`root`@`localhost` FUNCTION `CALCULATEDATE`(caller VARCHAR(4)) RETURNS date
BEGIN
DECLARE cur_day INT(2);
DECLARE cur_time INT(2);
DECLARE calculated_date DATE;

IF caller = 'HOME' THEN
    SELECT DAY(CONVERT_TZ(NOW(),@@system_time_zone,'US/Pacific')) INTO cur_day; 
    SELECT HOUR(CONVERT_TZ(NOW(),@@system_time_zone,'US/Pacific')) INTO cur_time; 
    /*Case homepage */
    IF cur_day = DAY(NOW()) THEN
        IF cur_time < 7 THEN
            /*return yesterdays date */
            SET calculated_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
        ELSE
            /*return todays date */
            SET calculated_date = CURDATE();
        END IF;
    ELSE
        SET calculated_date = DATE(CONVERT_TZ(NOW(),@@system_time_zone,'US/Pacific'));
    END IF;
ELSE
    /*Case newsletter */

    /*return todays date */
    SET calculated_date = CURDATE();
END IF;

RETURN calculated_date;
END

返回一个日期(基于 PST 时间比较)。

我从 View 中调用此函数:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `dd_vwfeatured` AS
    (SELECT 
        `doms`.`id` AS `id`,
        `doms`.`name` AS `name`

    FROM
        `myelements` `doms`
    WHERE
        `doms`.`id` IN (SELECT 
                `d`.`domainid`
            FROM
                (`daily_featured_picks` `d`
                LEFT JOIN `daily_featured` `f` ON ((`d`.`featuredid` = `f`.`id`)))
            WHERE
                (`f`.`date` = CALCULATEDATE('NEWS')))
    ORDER BY `doms`.`name`)

如果从 mysql 查询调用该函数，则可以完美运行，但在 View 中，我始终会返回所有记录。

你们能帮忙吗？

谢谢，DT

Answer 1

我终于解决了它(不知道为什么它没有在 View 中磨损)。我改变了方法并使用了存储过程。

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN p VARCHAR(4))
BEGIN
DECLARE p_tipo  DATE;

SELECT CALCULATEDATE(p) 
    INTO p_tipo;

SELECT 
        `doms`.`id` AS `id`,
        `doms`.`name` AS `name`,
        `doms`.`description` AS `description`,
        `doms`.`price` AS `price`
    FROM
        `myelements` `doms`
    WHERE
        `doms`.`id` IN (SELECT 
                `d`.`domainid`
            FROM
                (`daily_featured_picks` `d`
                LEFT JOIN `daily_featured` `f` ON ((`d`.`featuredid` = `f`.`id`)))
            WHERE
                (`f`.`date` = p_tipo))
    ORDER BY `doms`.`name`;

END