如何在MySQL中删除超过14天的行?
要删除,使用 MySQL DELETE。但是,要获取超过 14 天的记录,请将当前日期减去 14 天的日期间隔。以下显示了相同的语法:-
delete from yourTableName where yourColumnName< (curdate() - interval 14 day);
让我们首先创建一个表:-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, DueDate date ); Query OK, 0 rows affected (0.50 sec)
使用 insert 命令在表中插入一些记录:-
mysql> insert into DemoTable(DueDate) values('2019-08-20'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(DueDate) values('2019-08-19'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(DueDate) values('2018-08-19'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(DueDate) values('2018-08-18'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(DueDate) values('2019-08-15'); Query OK, 1 row affected (0.23 sec)
使用 select 语句从表中显示所有记录:-
mysql> select *from DemoTable ;
这将产生以下输出:-
+----+------------+ | Id | DueDate | +----+------------+ | 1 | 2019-08-20 | | 2 | 2019-08-19 | | 3 | 2018-08-19 | | 4 | 2018-08-18 | | 5 | 2019-08-15 | +----+------------+ 5 rows in set (0.00 sec)
以下是删除超过 14 天的行是的查询:-
mysql> delete from DemoTable where DueDate − (curdate() - interval 14 day); Query OK, 4 rows affected (0.62 sec)
让我们再次检查表记录:-
mysql> select *from DemoTable;
这将产生以下结果。现在,所有 14 天前的行都将被删除:-
+----+------------+ | Id | DueDate | +----+------------+ | 1 | 2019-08-20 | +----+------------+ 1 row in set (0.00 sec)
广告