时间戳

首页 > 解决方案 > SQL删除日期早于x天的所有行

database - SQL删除日期早于x天的所有行

问题描述

我有一个如下表，我正在尝试从当前日期删除所有 createdDate 早于 10 天的行，例如要从表中删除的最后 3 个流

ID  Name    createdDate(string)
76  Stream1 2018-10-18T00:00:00 
70  Stream2 2018-10-17T00:00:00 
50  Stream3 2018-10-03T00:00:00 
32  Stream4 2018-09-22T00:00:00 
21  Stream5 2018-09-21T00:00:00

我在下面尝试过查询但没有用，有人可以帮忙吗

DELETE FROM myTable WHERE TO_DATE(createdDate) < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 DAY))

DELETE FROM myTable WHERE TO_DATE(createdDate) < NOW()- INTERVAL 10 DAY;

标签： databasesqlite

解决方案

为了性能，为了允许 MySQL 使用范围扫描（使用索引createdDate作为前导列），我将在子句中引用裸列。WHERE

我会在比较的文字方面进行任何必要的操作，以得出一个我们可以将裸列与之进行比较的字符串。

我会先写一个 SELECT 语句，以验证它是否正常工作（在我们运行 DELETE 并发现它删除的内容比我们预期的要多之前）

我们可以在一个简单的 SELECT 语句中测试表达式，例如

SELECT DATE_FORMAT( NOW() + INTERVAL -10 DAY , '%Y-%m-%dT00:00:00') AS dt

然后我们可以在 WHERE 子句中使用该表达式

SELECT t.* 
  FROM myTable t 
WHERE t.createddate  <   DATE_FORMAT( NOW() + INTERVAL -10 DAY , '%Y-%m-%dT00:00:00')
ORDER BY t.createddate DESC

该表达式使用午夜'00:00:00'作为时间分量；还有其他表达式可以达到等效的结果。

WHERE t.createddate  <   DATE_FORMAT( DATE(NOW()) + INTERVAL -10 DAY , '%Y-%m-%dT%T')

我们确实需要缩小范围并具体说明“比当前日期早 10 天”的含义。

一旦我们确定查询返回了我们想要删除的行，我将通过替换 SELECT 关键字并将其转换为 DELETE 语句，并省略 ORDER BY 

SELECT t.* 
  FROM myTable t 
WHERE t.createddate  <   DATE_FORMAT( NOW() + INTERVAL -10 DAY , '%Y-%m-%dT00:00:00')

推荐阅读