mysql - DELETing w/Using 时限制删除的记录

Question

我通过将多个列移动到它们自己的表并改用外键来规范化数据库。我正在尝试更新应用程序中的查询以尽可能使用联接。在删除数据的查询中，我不希望查找键值而只是运行带有连接的删除。我发现唯一可行的方法是将 USING 添加到查询中 - 但这会破坏我对 LIMIT 子句的使用。

DELETE FROM prism_data
USING prism_data
INNER JOIN prism_players p ON p.player_id = prism_data.player_id
INNER JOIN prism_actions a ON a.action_id = prism_data.action_id
WHERE (a.action = 'water-flow')
LIMIT 5000; <--- Was ok before the using/joins were added

我正在尝试以 5,000 个为一组进行删除。如何修改查询以使限制仍然有效？

Answer 1

不幸的是，根据文档，LIMIT 子句仅对单个表 DELETE 有效:

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

http://dev.mysql.com/doc/refman/5.0/en/delete.html

---

另一方面，允许子查询。也许您可以将您的查询重构为以下形式？

DELETE FROM prism_data
WHERE .... IS IN (SELECT ....)
LIMIT 5000