存储函数中的临时表? [英] Temporary Table in Stored Functions?

问题描述

我正在编写一个函数，该函数需要将TABLE变量用于(我听说它们在MySQL或临时表中不存在.

I'm writing a function that I need to use either a TABLE variable for (I hear they don't exist in MySQL) or a temporary table.

但是，似乎临时表似乎只能在存储过程中工作，而不能在函数中工作.我不断收到此错误:

However, it seems that temporary tables only seem to work in stored procedures, not functions. I keep getting this error:

在存储的函数中不允许显式或隐式提交 触发.

Explicit or implicit commit is not allowed in stored function or trigger.

---

我要构建的是

---

What I'm trying to build is a solution to an earlier question of mine. It's a function that receives a start date, an end date, and a comma-deliminated string. It first finds all the months between the start and end date and saves them as individual records in the first temporary table. It then parses out the comma-deliminated string and saves those into a second temporary table. Then it does a select join on the two, and if records are present, it returns true, otherwise false.

我的意图是将其用作另一个查询WHERE子句的一部分，因此它必须是一个函数而不是存储过程.

My intention is to use this as part of another queries WHERE clause, so it needs to be a function and not a stored procedure.

如何在存储的函数中使用临时表?如果我做不到，该怎么办?

How can I use temporary tables in stored functions? And if I can't, what can I do instead?

这是我(当前已损坏)的功能(或作为要点):

Here's my (currently broken) function (or as a gist):

-- need to parse out a string like '4,2,1' and insert values into temporary table
-- MySQL doesn't have a native string split function, so we make our own
-- taken from: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
DROP FUNCTION IF EXISTS SPLIT_STR;
CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');

-- need to find all months between the start and end date and insert each into a temporary table
DROP FUNCTION IF EXISTS months_within_range;

DELIMITER //

CREATE FUNCTION months_within_range(starts_at DATE, ends_at DATE, filter_range VARCHAR(255)) RETURNS TINYINT
BEGIN

    DROP TABLE IF EXISTS months_between_dates;
    DROP TABLE IF EXISTS filter_months;
    CREATE TEMPORARY TABLE months_between_dates (month_stuff VARCHAR(7));
    CREATE TEMPORARY TABLE filter_months (filter_month VARCHAR(7));

    SET @month_count = (SELECT PERIOD_DIFF(DATE_FORMAT(ends_at, "%Y%m"), DATE_FORMAT(starts_at, "%Y%m")));
    -- PERIOD_DIFF only gives us the one month, but we want to compare to, so add one
    -- as in, the range between 2011-01-31 and 2011-12-01 should be 12, not 11

    INSERT INTO months_between_dates (month_stuff) VALUES (DATE_FORMAT(starts_at, "%Y-%m"));
    SET @month_count = @month_count + 1;
    -- start he counter at 1, since we've already included the first month above
    SET @counter = 1;
    WHILE @counter < @month_count DO
        INSERT INTO months_between_dates (month_stuff) VALUES (DATE_FORMAT(starts_at + INTERVAL @counter MONTH, "%Y-%m"));
        SET @counter = @counter + 1;
    END WHILE;

    -- break up the filtered string
    SET @counter = 1;
    -- an infinite loop, since we don't know how many parameters are in the filtered string
    filters: LOOP
        SET @filter_month = SPLIT_STR(filter_range, ',', @counter);
        IF @filter_month = '' THEN LEAVE filters;
        ELSE
            INSERT INTO filter_months (filter_month) VALUES (@filter_month);
            SET @counter = @counter + 1;
        END IF;
    END LOOP;

    SELECT COUNT(*) INTO @matches FROM months_between_dates INNER JOIN filter_months ON months_between_dates.month_stuff = filter_months.filter_month;

    IF @matches >= 1 THEN RETURN 1;
    ELSE RETURN 0;

END//

DELIMITER ;

推荐答案

drop table语句会导致隐式提交，这在mysql函数中是不允许的.删除临时表虽然不会导致提交.如果您不担心存在名为months_between_dates或filter_months的常规(非临时)表，则应该可以更改

drop table statements cause an implicit commit, which is not allowed in a mysql function. drop temporary table doesn't cause the commit though. if you're not worried about regular (non-temporary) tables named months_between_dates or filter_months existing you should be able to change

DROP TABLE IF EXISTS months_between_dates;
DROP TABLE IF EXISTS filter_months;

到

DROP TEMPORARY TABLE IF EXISTS months_between_dates;
DROP TEMPORARY TABLE IF EXISTS filter_months;

这篇关于存储函数中的临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！