英文:
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 ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论