使用MySQL中的事件 [英] Working with Events in MySQL

问题描述

我有一个存储过程，它基本上从一个表中选择数据并插入到另一个表中。基本上我是手动进行数据归档。现在，我想写一个事件就像讨论这里

但是，在阅读那篇文章并在线研究之后，我发现不可能在存储过程中创建一个事件。有没有办法在MySQLWorkbench中完成我的目标？

解决方案

我相信你是在相反的方向思考： t在存储过程中创建事件，但您可以创建存储过程并从事件中调用它。

示例：

 分隔符$$ 
 create procedure myProc（）
  - 数据从mainTable到backupTable，
  - 然后清除（truncate）mainTable 
 begin 
 insert into backupTable select * from mainTable; 
 truncate mainTable; 
 end $$ 
 delimiter; 
 
  - 现在，假设您要每小时执行此过程：
 delimiter $$ 
按1小时计划创建事件myEvent 
 
 do 
 begin 
 call myProc（）; 
 end $$ 
 delimiter; 
  

您可以将其作为任何其他查询写入工作台，或直接在命令行客户端。 / p>

---

关于您的问题

阅读您的评论后，我相信你对什么是MySQL Workbench有点困惑。

MySQL Workbench只是一个图形应用程序，连接到MySQL服务器并执行查询和管理任务。但是Workbench 不是 MySQL的核心...它只是一个查看器（可能是类固醇，但是毕竟是一个查看器）。

现在，事件调度程序不驻留在Workbench中，而是在您要连接的MySQL服务器实例中。正如表，视图，过程和函数不存储在Workbench界面中但是在服务器中，事件也存储在服务器中。

相信它是一个相关的SNAFU，排定的事件不显示在图形界面的任何地方，但...一段时间后，一个人学会生活与那种沮丧和继续生活）

也许你唯一关心的是：嘿，如果我想知道什么事件被设置为在事件调度器中运行？您可以执行show events查询以显示当前数据库中的事件列表，并且可以执行show create event yourEvent以显示 create event 语法

我坚持：阅读手册，并保留一份手册（下载MySQL版本的手册此处）。

I have a stored procedure which basically selects data from one table and insert into another table. Basically I am doing data archiving manually. Now, I want to write an event just like discussed here

However, after reading that post and researching online, I came to know that it's not possible to create an event inside a stored procedure. Is there a way to accomplish my goal in MySQLWorkbench?

解决方案

I believe you are thinking this in the oposite direction: You can't create an event in a stored procedure, but you can create a stored procedure and call it from an event.

Example:

delimiter $$
create procedure myProc()
-- Dummy procedure to move the data from mainTable to backupTable, 
-- and then clear (truncate) mainTable
begin
    insert into backupTable select * from mainTable;
    truncate mainTable;
end $$
delimiter ;

-- Now, suposing that you want to execute this procedure every hour:
delimiter $$
create event myEvent
    on schedule every 1 hour
    do
        begin
            call myProc();
        end $$
delimiter ;

You can write this as any other query in the workbench, or directly in the command line client.

---

About your concern

After reading your comment, I believe you are a bit confused about what MySQL Workbench is.

MySQL Workbench is only a graphical application that allows you to connect to a MySQL server and perform queries and administration tasks. But Workbench is not the core of MySQL... it is only a viewer (with steroids, maybe, but a viewer after all).

Now, the event scheduler does not reside in Workbench, but in the MySQL server instance you are connecting to. Just as the tables, views, procedures and functions are not stored in the Workbench interface but in the server, the events are also stored in the server.

(Yes, I believe it is a relevant SNAFU that the scheduled events don't show anywhere in the graphical interface, but... after a while, one learns to live with that kind of frustrations and to move on with life)

Maybe your only concern is: "Hey, and what if I want to know what events are set to run in the event scheduler?" You can execute a "show events" query to show a list of the events in the current database, and you can execute "show create event yourEvent" to show the create event syntax for that event.

I insist: Read the manual, and keep a copy at hand (download the manual for your MySQL version here).

这篇关于使用MySQL中的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！