MySQL Mysql删除命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12382250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql delete order by
提问by Abby E
I have a table and I only display the latest 30 rows by order by ID.
我有一张表，我只按 ID 的顺序显示最新的 30 行。
I'm trying to delete any rows after the 30 newest rows by using this query below.
我正在尝试使用下面的查询删除 30 个最新行之后的任何行。
DELETE FROM table WHERE type = 'test' ORDER BY id DESC LIMIT 30, 60
I keep getting this error below
我一直在下面收到这个错误
#1064 - 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 ' 60' at line 1
What am I doing wrong?
我究竟做错了什么？
回答by John Woo
Try this one,
试试这个，
DELETE FROM table
WHERE ID IN
(
SELECT ID
FROM
(
SELECT ID
FROM table
WHERE Type = 'TEST'
ORDER BY ID
LIMIT 30,60
) a
)
回答by alexn
Second edit: While MySQL supports LIMIT in delete statements, it does not allow an OFFSET. This means that you cannot skip the first 30 rows.
第二次编辑：虽然 MySQL 支持删除语句中的 LIMIT，但它不允许 OFFSET。这意味着您不能跳过前 30 行。
Make a subselect on id (or any other primary key):
对 id （或任何其他主键）进行子选择：
DELETE FROM table WHERE id IN (SELECT id FROM table WHERE type = 'test' ORDER BY id DESC LIMIT 30, 60)
回答by alexn
This is not possible this way. You could try it with a nested select statement, somewhat like this:
这是不可能的。您可以尝试使用嵌套的 select 语句，有点像这样：
DELETE FROM table
WHERE type = 'test'
AND ID IN (SELECT id from table where type = 'test' order by id desc limit 30 )
回答by Gautam3164
Try like this
像这样尝试
DELETE FROM table WHERE id in(SELECT id FROM table WHERE type = "test" order by id desc limit 30, 60)
回答by Grebe.123
I was unable to use the limit clause in the sub-query, so the solution I use, somewhat messy, is:-
我无法在子查询中使用 limit 子句，所以我使用的解决方案有点混乱，是：-
select group_concat(id) into @idList from
(
select id from table order by id desc limit 0,30
) as saveIds;
delete from table where not find_in_set(id,@idList)
Alternatively,
或者，
select group_concat(id) into @idList from
(
select id from table order by id desc limit 30
) as saveIds;
delete from table where find_in_set(id,@idList)
