删除MySQL中5天之前的所有行

要删除5天之前的所有行，可以使用以下语法 −

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

注意 - 假设当前日期为2019年3月10日。

为了理解这个概念，我们创建一个表。创建表的查询语句如下 −

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)

使用插入命令在表中插入一些记录。查询语句如下 −

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)

使用选择语句显示表中的所有记录。查询语句如下 −

mysql> select *from deleteRowsOlderThan5Demo;

以下是输出结果 −

+----+---------+------------+
| 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)

以下是删除5天之前的所有行的查询语句 −

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

让我们再次检查表记录。查询语句如下 −

mysql> select *from deleteRowsOlderThan5Demo;

以下是输出结果 −

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

阅读更多：MySQL 教程