在MySQL中删除超过14天的行 [英] Delete rows older than 14 days in MySQL

问题描述

摘要

我需要从超过14天的行中清除表的历史记录.

I need to purge the history of a table from rows that are older than 14 days.

由于不是MySQL专家，我的搜索使我想到了这一点:

Being no MySQL Expert, my searches lead me to this:

delete
    from SYS_VERROUS_POLICE
    where idModificationPolice not in (
                select distinct idModificationPolice
                    from SYS_VERROUS_POLICE
                    where date(dateHeureModification) 
                            between curdate() and curdate() - interval 14 day
            );

抛出异常

但是后来我被这个错误消息困住了:

But then I'm stuck with this error message:

错误代码:1093.您无法在FROM子句中指定目标表'SYS_VERROUS_POLICE'进行更新.

Error Code: 1093. You can't specify target table 'SYS_VERROUS_POLICE' for update in FROM clause.

什么...

上下文

MySQL似乎正在安全模式下运行，因此我无法在匹配日期的地方执行DELETE.

MySQL seems to be operating in safe mode, so I just won't be able to perform a DELETE where matching dates.

在安全模式下，如果我尝试仅使用日期字段进行删除，则不符合要求.

In safe-mode, if I try to delete using only the date field, it doesn't comply.

delete  
  from SYS_VERROUS_POLICE     
 where date(dateHeureModification) < curdate() - interval 14 day    

Error Code: 1175. You are using safe update mode and you tried to update a table without a 
WHERE that uses a KEY column
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 
0,00071 sec

我想念什么吗?

推荐答案

我了解您关于使用安全模式的观点.如果您尝试对未索引的表达式使用UPDATE或DELETE，它会抱怨，因为它无法估计是否会意外删除整个表.

I understand your point about using safe mode. If you try to use UPDATE or DELETE against a non-indexed expression, it complains, because it can't make an estimate of whether you will accidentally delete your whole table.

使用 DATE(dateHeureModification)>上的表达式... 是自然没有索引的.MySQL无法针对函数结果进行索引查找.

Using an expression on DATE(dateHeureModification) > ... is naturally unindexed. MySQL can't do an index lookup against the result of a function.

您可以在删除查询中使用 LIMIT ，使其符合安全更新模式.如果您使用 LIMIT ，MySQL将其视为足以防止意外删除表中所有行的保护措施.

You can use LIMIT in your delete query to make it satisfy the safe-updates mode. MySQL treats it as sufficient protection against accidentally deleting all the rows in the table, if you use LIMIT.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE DATE(dateHeureModification) < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

无论如何要以有限大小的批次运行删除操作是一个好主意，因此它不会创建太多的锁，也不会在撤消段中添加过多的锁.

It's a good idea to run the delete in limited-size batches anyway, so it doesn't create too many locks or add too much to the undo segment.

只需循环执行一次DELETE，一次删除1000行，然后在每批之后检查受影响的行.当受影响的行达到0时，停止循环.

Just keep doing DELETE in a loop, deleting batches of 1000 rows at a time, and check rows-affected after each batch. Stop the loop when rows-affected reaches 0.

另一个想法:我不认为您确实需要WHERE子句中的 DATE()函数.因此，您可以像下面这样进行DELETE，它将可以使用索引.此外，如果您在dateHeureModification上有索引，则查询所有行的查询应该更快.

Another idea: I don't think you really need the DATE() function in your WHERE clause. So you might be able to do the DELETE like below, and it will be able to use an index. Besides, it should be faster to the query to check for any rows if you have an index on dateHeureModification.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE dateHeureModification < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

这篇关于在MySQL中删除超过14天的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！