绝对初学者，尝试在MySQL中限制和排序DELETE/JOIN查询

Question

我收到了一个MySQL查询，要求删除数百万条我试图添加限制的条目，这也是按ASC排序的。我几乎不知道我在做什么，而且我被困住了。

据我所知，您不能限制多表删除，但我找到的绕过这个问题的解决方案对我来说太复杂了，我自己无法适应。

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE d.taxonomy = 'edd_log_type' AND a.post_type = 'edd_log' AND e.term_id = 167
ORDER BY ID ASC LIMIT 100;

期望:我正在尝试构造一个查询，该查询将查找并删除最早的100个条目。我设置了一个限制，因为正在处理的条目数量(数百万)会使查询超时。

实际结果:至少据我所知，由于LIMIT不支持多表删除而导致的语法错误。

Answer 1

不应在where子句中使用与左连接表相关的列，因为在这些情况下，使用where this work作为内连接时，应在ON子句中移动相关条件

对于删除(基于您的代码)，您可以将delete语句与一个返回所需的100id的查询连接起来

  delete a,b,c 
  FROM wp_posts a
  LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
  LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
  LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
  LEFT JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
  INNER JOIN  ( 

      select a.id 
      FROM wp_posts a
      LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
      LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
      LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
      LEFT JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
      WHERE a.post_type = 'edd_log'
      ORDER BY ID ASC 
      LIMIT 100

  ) t ON t.id = a.id 
  WHERE AND a.post_type = 'edd_log'

根据Strawberry的建议，delete比使用inner join更好(不能删除不匹配的行)

  delete a,b,c 
  FROM wp_posts a
  INNER  JOIN wp_term_relationships b ON ( a.ID = b.object_id )
  INNER  JOIN wp_postmeta c ON ( a.ID = c.post_id )
  INNER  JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
  INNER  JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
  INNER JOIN  ( 

      select a.id 
      FROM wp_posts a
      INNER  JOIN wp_term_relationships b ON ( a.ID = b.object_id )
      INNER  JOIN wp_postmeta c ON ( a.ID = c.post_id )
      INNER  JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
      INNER  JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
      WHERE a.post_type = 'edd_log'
      ORDER BY ID ASC 
      LIMIT 100

  ) t ON t.id = a.id 
  WHERE AND a.post_type = 'edd_log'