如何在mysql事件中使用多个sql语句？

These are the statements

这些是陈述

INSERT INTO toolate (name,type,date) 
SELECT  name, type ,date 
FROM homework 
WHERE date < CURRENT_DATE()

and

和

DELETE FROM homework WHERE date < CURRENT_DATE()

I need to combine these two so that my event will work in a proper order. Firstly the INSERT statement then the DELETE one.

我需要将这两者结合起来，以便我的事件能够以正确的顺序运行。首先是INSERT语句然后是DELETE语句。

That way I can still see homework that's past date while having a clean homework table and it needs to happen automatically thus why I'm using events. Of course I will welcome a different solution.

这样我仍然可以看到过去的日期作业，同时有一个干净的家庭作业表，它需要自动发生，因此我为什么要使用事件。当然，我会欢迎另一种解决方案。

1 个解决方案

#1

You can't combine these two in a single query. However, an alternative would be to use STORED PROCEDURE and execute these two inside a transaction with EXIT HANDLER e.g.:

您不能在单个查询中组合这两个。但是，另一种方法是使用STORED PROCEDURE并在EXIT HANDLER的交易中执行这两个例如：

BEGIN

    START TRANSACTION;

        DECLARE EXIT HANDLER FOR SQLEXCEPTION 

        BEGIN
            ROLLBACK;
            EXIT PROCEDURE;
        END;

        INSERT INTO toolate (name,type,date) 
        SELECT  name, type ,date 
        FROM homework 
        WHERE date < CURRENT_DATE()

        DELETE FROM homework WHERE date < CURRENT_DATE()

    COMMIT;
END

This will make sure both of these queries are executed sequencially, and if DELETE query fails, INSERT will be rolled back.

这将确保这两个查询都按顺序执行，如果DELETE查询失败，则将回滚INSERT。

Here's MtSQL's documentation for stored procedures.

这是MtSQL存储过程的文档。

#1