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

The stored procedure is equivalent to the mysql function. It is a set of sql statements stored in the database server. These sql statement commands are executed by calling the name of this function.

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

View stored procedures

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

Use stored procedures

call del_data()

Write an event

Open the event scheduler

SET GLOBAL event_scheduler = ON;

Create an event

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!