Deleting all rows older than 5 days in MySQL

To delete all rows older than 5 days, you can use the following syntax −

delete from yourTableName    where datediff(now(), yourTableName.yourDateColumnName) > 5;

Note − Let’s say the current date is 2019-03-10.

To understand the concept, let us create a table. The query to create a table is as follows −

mysql> create table deleteRowsOlderThan5Demo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Post_Date date    -> ); Query OK, 0 rows affected (0.69 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Larry','2019-03- 11'); Query OK, 1 row affected (0.12 sec) mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Mike','2019-02- 12'); Query OK, 1 row affected (0.17 sec) mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Sam','2019-03- 10'); Query OK, 1 row affected (0.12 sec) mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Carol','2019-03- 01'); Query OK, 1 row affected (0.23 sec) mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('David','2019-01- 31'); Query OK, 1 row affected (0.19 sec) mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Maxwell','2019-01- 26'); Query OK, 1 row affected (0.10 sec) mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('John','2019-02- 19'); Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from deleteRowsOlderThan5Demo;

Here is the output −

+----+---------+------------+ | Id | Name | Post_Date | +----+---------+------------+ | 1 | Larry | 2019-03-11 | | 2 | Mike | 2019-02-12 | | 3 | Sam | 2019-03-10 | | 4 | Carol | 2019-03-01 | | 5 | David | 2019-01-31 | | 6 | Maxwell | 2019-01-26 | | 7 | John | 2019-02-19 | +----+---------+------------+ 7 rows in set (0.00 sec)

Here is the query to delete all rows older than 5 days −

mysql> delete from deleteRowsOlderThan5Demo -> where datediff(now(), deleteRowsOlderThan5Demo.Post_Date) > 5; Query OK, 5 rows affected (0.14 sec)

Let us check the table records once again. The query is as follows −

mysql> select *from deleteRowsOlderThan5Demo;

Here is the output −

+----+-------+------------+ | Id | Name | Post_Date | +----+-------+------------+ | 1 | Larry | 2019-03-11 | | 3 | Sam | 2019-03-10 | +----+-------+------------+ 2 rows in set (0.00 sec)