Mysql delete语句有限制

Question

我正在尝试从表中删除行，但出现错误。

DELETE FROM `chat_messages` ORDER BY `timestamp` DESC LIMIT 20, 50;

我在 50 时收到此错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 50' at line 1

不知道出了什么问题。

Answer 1

您不能在 DELETE 的 LIMIT 子句中指定偏移量。

所以唯一的方法就是将您的查询重写为:

DELETE FROM `chat_messages` 
WHERE `id` IN (
    SELECT `id` FROM (
        SELECT `id` FROM `chat_messages`
        ORDER BY `timestamp` DESC
        LIMIT 20, 50
    ) AS `x`
)

假设您有主键 id 列

UPD:你需要实现双重嵌套来欺骗 mysql，因为它不允许从当前修改的表中进行选择(感谢 Martin Smith)