使用存储过程和表变量名创建一个事件

On a weekly basis I would like to copy all data from the past week and insert it into a table (with the week number included in the table name) in an other database.

我每周都会复制过去一周的所有数据，并将其插入到一个表中（表名中包含周数）在另一个数据库中。

This works fine using a stored procudure:

这使用存储的procudure工作正常：

SET @weekno:=WEEKOFYEAR(NOW());
SET @sql_text = concat('INSERT INTO 
archive.data_2017_week_',@weekno,'  select * from live.data_archive where 
year(time) = 2017 AND week(time) = ',@weekno,'');

PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

However if i try to make a weekly event for this in phpMyAdmin I get MySQL 'Error Code: 1064'.

但是，如果我尝试在phpMyAdmin中为此制作每周事件，我会得到MySQL'错误代码：1064'。

Is it possible to run a prepared statement in an event?

是否可以在事件中运行准备好的声明？

If not, is there a better method to copy weekly data to a table with the week number included in it's name?

如果没有，是否有更好的方法将每周数据复制到一个包含在其名称中的周数的表中？

Many Thanks.

非常感谢。

1 个解决方案

#1

You can simply create a stored procedure as shown below code

您可以简单地创建一个存储过程，如下所示代码

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `YOUR_PROCEDURE`()
BEGIN
    SET @weekno:=WEEKOFYEAR(NOW());
    SET @sql_text = concat('INSERT INTO 
                            archive.data_2017_week_',@weekno,'  select * from live.data_archive where 
                            year(time) = 2017 AND week(time) = ',@weekno,'');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

And then call that procedure using event as shown in below code.

然后使用事件调用该过程，如下面的代码所示。

For basics about it you can check here MySQL Events and Time Duration.

有关它的基础知识，您可以在这里查看MySQL事件和时间持续时间。

CREATE EVENT YOUR_EVENT_NAME
    ON SCHEDULE EVERY 1 week
    DO
      CALL YOUR_PROCEDURE();

#1