具有插入选择和用户变量的MySQL存储过程

Problem: I have a MySQL stored procedure and I want to reset a user variable to 0 each time the procedure is called, but the variable seems to be remembering its value from each previous run and I cannot initialize it to zero.

问题:我有一个MySQL存储过程,我想在每次调用过程时将用户变量重置为0,但是变量似乎记住了每个先前运行的值,我无法将其初始化为零。

Details: I have a data table (called data) which I read from and then generate records in a 1-to-1 correlation in another table (called results). I do this in a stored procedure that utilizes an Insert Select statement. I also use a user variable @mycount which increments with each row. Here is a simplified version of my code:

详细信息:我有一个数据表(称为数据),我从中读取数据表,然后在另一个表(称为结果)中以1对1的相关性生成记录。我在使用Insert Select语句的存储过程中执行此操作。我还使用了一个用户变量@mycount,它随每行递增。这是我的代码的简化版本:

DELIMITER //
CREATE PROCEDURE doSomething
BEGIN
    SET @mycount := 0;
    INSERT INTO results (data_id, mycounter)
        SELECT data.id, (@mycount := @mycount+1)
        FROM data LEFT JOIN results ON data.id = results.data_id
        WHERE ... GROUP BY ...
        HAVING ...
        ORDER BY ...;
END //
DELIMITER ;

Analysis: This almost works as desired except I need @mycount to reset to zero each time it is run and the SET statement above is not achieving that. When debugging, I can see that the last value of @mycount is always remembered from the previous run of the stored procedure. I've also tried setting it to zero after the insert.

分析:这几乎按预期工作,除了我每次运行时都需要@mycount重置为零,而上面的SET语句没有实现。在调试时,我可以看到@mycount的最后一个值总是从上一次存储过程运行中记住。我也尝试在插入后将其设置为零。

Notes:

I am using a @ variable to increment and keep track of row number. Perhaps there is another option here?

我正在使用@变量来增加并跟踪行号。也许这里有另一种选择?

The problem seems related to the Having clause. When I run a similar but different query without the having clause, the variable resets no problem.

问题似乎与Having子句有关。当我在没有having子句的情况下运行类似但不同的查询时,该变量不会重置。

Question: Why can't I set @mycount to zero before running each time? If it IS resetting and it's just that my Having clause causes an unexpected count, is there some better way to rewrite my SQL?

问题:为什么我不能在每次运行之前将@mycount设置为零?如果它正在重置并且只是我的Having子句导致意外计数,是否有更好的方法来重写我的SQL?

I'm hoping someone has run into something similar, since this seems pretty obscure.

我希望有人碰到类似的东西,因为这看起来很模糊。

3 个解决方案

#1

I think you are using session variable(i.e; @mycount).Check below site you will get an answer

我认为你正在使用会话变量(即; @mycount)。检查网站下面你会得到一个答案

MySQL: @variable vs. variable. Whats the difference?

MySQL:@variable与变量。有什么不同?

#2

I dont find any reason why your code should not work. Anyways, try the below method.

我没有找到任何理由为什么你的代码不起作用。无论如何,尝试以下方法。

INSERT INTO results (data_id, mycounter)
select a.id, a.cnt from (
select @mycount := 0 from dual
join ( SELECT data.id, (@mycount := @mycount+1) cnt
        FROM data LEFT JOIN results ON data.id = results.data_id
        WHERE ... GROUP BY ...)) a;

In this case, you dont need to specify SET @mycount := 0;

在这种情况下,您不需要指定SET @mycount:= 0;

#3

In the following SQL Fiddle I can not reproduce the problem you pose, in theory works as expected.

在下面的SQL Fiddle我无法重现你提出的问题,理论上按预期工作。

UPDATE

Try:

DELIMITER //

CREATE PROCEDURE doSomething()
BEGIN
    SET @mycount := 0;
    INSERT INTO results (data_id, mycounter)
        SELECT der.id, (@mycount := @mycount + 1)
        FROM (
            SELECT data.id
            FROM data
                LEFT JOIN results ON data.id = results.data_id
            WHERE ...
            GROUP BY ...
            HAVING ...
            ORDER BY ...
        ) der;
END //

DELIMITER ;

#1