Run on phpMyAdmin local server:
I have two tables: user (with data) and user_t (empty).
They have the same column names, order, and type (the user_t table has an extra column for timestamping the input).
I want to create a scheduled event to check if the timestamp of each user log has a specific value, and if it exists, I want to copy it to user_t Also remove it from user. very simple.
Deletion works fine. It was INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;< CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; that gave me trouble.
This is the code I use 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 results. So, I thought I must be doing something wrong.
Can you sincerely help me get this code working? After all, it's from a tutorial and should be fine.
I've tried using code and even considered looking into transactions , but that seems too bulky for this kind of operation.
So after playing around with nbk's code I finally figured it out and here's the code that worked for me (I generalized the table and column names so they don't 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 ;
Hope it helps people like me in the future. Thanks again nbk for the quick help. Thank you very much, of course your answer is correct.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
You run the event every minute and want to input the last three minutes, I don't know if you have that many new inserts or you need a python script or insert for an hour like this ==>For testing purposes I set the frequency to 1 minute...
You should still override your events.
First, do you really want duplicates, maybe
INSERT IGNOREwhich will enter a new row if the constraint is violatedSecond, if you have more than one statement, you need a
BEGIN ENDDELIMITER $$ 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 ;