在MySQL中,从一个表复制数据到另一个表的定时事件返回空结果。

huangapple go评论55阅读模式
英文:

Scheduled event to copy data from one table to another in MySQL returns empty results

问题

在phpMyAdmin本地服务器上运行:
我有两个表:user(有数据)和user_t(为空)。它们具有相同的列名、顺序和类型(user_t表有一个额外的时间戳输入列)。

我想要创建一个“计划事件”来检查每个用户日志的时间戳是否具有特定值,如果是的话,我想将其复制到user_t,同时从user中删除它。非常直接。

删除操作运行正常。问题出在这段代码上:INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;

这是我用于创建事件的代码:

CREATE EVENT users_u
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
DELETE FROM user WHERE reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;

但它一直返回“空结果”。所以,我认为一定有我做错的地方。你能帮我让这段代码运行吗?毕竟,这是来自教程,应该没问题。

我尝试过修改代码,甚至考虑过使用“事务”,但对于这种操作来说似乎太复杂了。

编辑

所以在与nbk的代码尝试后,我终于搞明白了,下面是对我有效的代码(我泛化了表名和列名,以免误导任何人):

DELIMITER $$
CREATE EVENT event_name 
ON SCHEDULE EVERY 1 MINUTE 
STARTS CURRENT_TIMESTAMP 
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO 
	BEGIN 
	INSERT INTO empty_table (col_name1, col_name2, col_name3, col_name4, col_name5) 
	SELECT col_name1, col_name2, col_name3, col_name4, col_name5 FROM data_table WHERE col_name < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; 
	DELETE FROM data_table WHERE col_name < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; 
	END $$
DELIMITER ;

希望它对未来的某人有所帮助。
再次感谢nbk,感谢您的快速帮助。非常感激,当然,您的答案是正确的。

英文:

Running on phpMyAdmin local server:
I have two tables: user (with data) and user_t (empty).
They have identical column name, order and type (the user_t table has one extra column for timestamping the input though).

I want to create a scheduled event to check if the timestamp of each user's log has a certain value and when it does, I want to copy it to user_t while deleting it from user. Pretty straightforward.

The deletion works fine. It is the INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; that gives me trouble.

This is the code I am using to create the event:

CREATE EVENT users_u
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
DELETE FROM user WHERE reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;

However, it keeps returning empty result. So, I take it there must be something I am doing wrong.
Could you find it in your hearts to help me make this code work? After all, it is from a tutorial and should be fine.

I have tried playing with the code and even considered looking into transaction but that seemed too bulky for this kind of operation.

EDIT

So after some playing with the code from nbk, I finally figured it out and here is the code that worked for me (I generalised the table and column names so they would not mislead anyone):

    DELIMITER $$
CREATE EVENT event_name 
ON SCHEDULE EVERY 1 MINUTE 
STARTS CURRENT_TIMESTAMP 
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO 
	BEGIN 
	INSERT INTO empty_table (col_name1, col_name2, col_name3, col_name4, col_name5) 
	SELECT col_name1, col_name2, col_name3, col_name4, col_name5 FROM data_table WHERE col_name < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; 
	DELETE FROM data_table WHERE col_name < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; 
	END $$
DELIMITER ;

Hopefully it helps someone like me in the future.
Thanks again, nbk, for your quick help. Much appreciated and, of course, your answer is CORRECT.

答案1

得分: 1

你每分钟运行一次事件,并想输入最后三分钟的数据。我怀疑你是否有那么多新的插入数据,或者你需要一个Python脚本之类的东西,来为一个小时进行插入。

==> 为了测试目的,我将频率设置为每1分钟...

但你应该重新编写你的事件。

首先,你是否真的想要重复数据,也许可以使用INSERT IGNORE,它将插入新的行,如果违反了约束条件。

其次,如果有多个语句,你需要使用BEGIN END

DELIMITER $$
CREATE EVENT users_u
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
    BEGIN
        INSERT IGNORE INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
        DELETE FROM user WHERE reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
    END $$
DELIMITER ;
英文:

You run every minute the event and want to enter the last three minutes, i dount that you have that many new inserts, or you need a python script or so that inserts for one hour
==> for testing purposes, I set up the frequency to 1 minute...

Still you should rewrite your event.

first do you really want duplicates, maybe a INSERT IGNORE,which would enter new row, if it violates a constraint

second if you have mire than one statement you need a BEGIN END

DELIMITER $$
CREATE EVENT users_u
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
	BEGIN
		INSERT IGNORE INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
		DELETE FROM user WHERE reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
	END $$
DELIMITER ;

huangapple
  • 本文由 发表于 2023年2月27日 02:14:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75574062.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定