Scheduled deletion in mysql database

How to set up scheduled deletion in mysql database: first open the mysql file; then set up to delete data three days ago, the code is [DELETE FROM table WHERE created_on

Related free learning recommendations:mysql database( Video)

How to set up scheduled deletion in mysql database:

SQL to delete data three days ago

DELETE FROM table WHERE created_on < DATE_SUB(CURDATE(),INTERVAL 3 DAY);

• CURDATE() returns the current date

• CURNOW() returns the current datetime

• INTERVAL Yes MySQL interval value, usage is INTERVAL expr unit. INTERVAL 3 DAY represents an interval of three days

• ##DATE_SUB(start_date,INTERVAL expr unit);

Write a stored procedure

DELIMITER // create procedure del_data() BEGIN DELETE FROM table WHERE created_on < DATE_SUB(CURDATE(),INTERVAL 3 DAY); END// DELIMITER ;

• DELIMITER means delimiter. Declare "//" as a delimiter before declaring the stored procedure, so that ";" in the stored procedure will not be used as a delimiter. deal with. Restore the delimiter after the statement ends.

• Stored procedures can also take parameters, stored procedure name (parameter)

• Before declaring a stored procedure, you must first use use database_name to switch to the desired The database to be applied, otherwise the stored procedure will be applied to the default database

View and use stored procedures

select * from mysql.proc where db=’数据库名’;

call del_data()

Write an event

SET GLOBAL event_scheduler = ON;

create event del_event on schedule EVERY 1 day STARTS '2019-3-28 00:00:00' do call del_data()

The above is the detailed content of Scheduled deletion in mysql database. For more information, please follow other related articles on the PHP Chinese website!