MySQL删除语句有限制 [英] Mysql delete statement with limit

问题描述

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

I'm trying to delete rows from a table but I get an error.

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

我在50岁时收到此错误:

I get this error at 50:

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的"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

不知道怎么了.

推荐答案

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

You cannot specify offset in DELETE's LIMIT clause.

因此，唯一的方法是将查询重写为类似的内容:

So the only way to do that is to rewrite your query to something like:

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

假设您有主键id列

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

UPD: you need to implement double nesting to fool mysql, since it doesn't allow to select from currently modified table (thanks to Martin Smith)

这篇关于MySQL删除语句有限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！