mysql 定时执行sql

SHOW VARIABLES LIKE 'event_scheduler';

SET GLOBAL event_scheduler = ON;

[mysqld]
event_scheduler=ON

CREATE EVENT IF NOT EXISTS daily_backup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
BEGIN
    -- 这里写你需要执行的SQL语句，例如备份表
    SELECT * INTO OUTFILE '/path/to/backup/table_backup.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM your_table;
END;

CREATE EVENT IF NOT EXISTS hourly_stats
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    INSERT INTO stats_table (metric, value)
    SELECT 'active_users', COUNT(*) FROM users WHERE status = 'active';
END;

CREATE EVENT IF NOT EXISTS one_time_task
ON SCHEDULE AT '2024-04-27 15:30:00'
DO
BEGIN
    -- 这里写你需要执行的SQL语句
    UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END;

SHOW EVENTS;

SHOW CREATE EVENT event_name;

ALTER EVENT event_name
ON SCHEDULE EVERY 2 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY;

DROP EVENT IF EXISTS event_name;

GRANT EVENT, ALTER, INSERT, UPDATE ON your_database.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;

CREATE EVENT IF NOT EXISTS daily_active_users_stats
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
BEGIN
    INSERT INTO daily_active_users (stat_date, active_user_count)
    SELECT CURDATE() - INTERVAL 1 DAY, COUNT(*)
    FROM user_activity
    WHERE activity_date = CURDATE() - INTERVAL 1 DAY;
END;