在MySQL中删除所有超过5天的行
要删除所有超过5天的行，您可以使用以下语法-
delete from yourTableName where datediff(now(), yourTableName.yourDateColumnName) > 5;
注意-假设当前日期为2019-03-10。
为了理解这个概念，让我们创建一个表。创建表的查询如下-
mysql> create table deleteRowsOlderThan5Demo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> Post_Date date -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Larry','2019-03-
11');
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Mike','2019-02-
12');
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Sam','2019-03-
10');
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Carol','2019-03-
01');
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('David','2019-01-
31');
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('Maxwell','2019-01-
26');
mysql> insert into deleteRowsOlderThan5Demo(Name,Post_Date) values('John','2019-02-
19');使用select语句显示表中的所有记录。查询如下-
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;
让我们再次检查表记录。查询如下-
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)