在MySQL中使用带有LIMIT的LEFT JOIN进行删除 [英] DELETE using LEFT JOIN with LIMIT in MySQL

问题描述

我正在尝试删除数据库中的孤立帖子，并创建了以下查询:

I'm trying to delete orphan posts in my database and I have created this query:

DELETE post.*
      FROM foro_post AS post
      LEFT JOIN foro_thread AS thread USING(threadid)
      WHERE thread.threadid IS NULL

问题是我要限制，因为我的表有超过7,000,000条记录.

The problem is that I want to limit because my table has over 7,000,000 records.

由于我无法在查询中使用LIMIT，因此我尝试了此方法并使其实际起作用，但是我不确定这是否是一种有效的解决方案，或者是否可以做得更好.

As I can't use LIMIT with the query, I tried this and actually worked, but I'm not sure if is an efficient solution or if it could be done better.

DELETE post.*
      FROM foro_post AS post
      LEFT JOIN foro_thread AS thread USING(threadid)
      WHERE thread.threadid IS NULL
      AND post.postid < 500

      // Where < 500 should be increasing as I delete records

我如何才能更有效地做到这一点?

How can I do this more efficiently?

推荐答案

您不能直接在

You can't use LIMIT directly within DELETE when you're referencing multiple tables at the same time, but you can get around that by encasing what you want to delete within a subselect:

DELETE po 
FROM   foro_post po
JOIN   (
       SELECT    p.postid
       FROM      foro_post p
       LEFT JOIN foro_thread t ON p.threadid = t.threadid
       WHERE     t.threadid IS NULL
       ORDER BY  p.postid
       LIMIT     50
       ) pp ON po.postid = pp.postid

这篇关于在MySQL中使用带有LIMIT的LEFT JOIN进行删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！