DELETE 语句上的 MySQL 限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6111961/
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 LIMIT on DELETE statement
提问by Andre
I put together a test table for a error I recently came across. It involves the use of LIMIT when attempting to delete a single record from a MySQL table.
我为最近遇到的错误整理了一个测试表。它涉及在尝试从 MySQL 表中删除单个记录时使用 LIMIT。
The error I speak of is "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 'LIMIT 1' at line 1"
我所说的错误是“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的‘LIMIT 1’附近使用的正确语法”
The table I put together is called test; it has 3 columns, id, nameand created. I populated the table with several records and then attempted to delete one. Below is the statement I used to try and accomplish this.
我放在一起的表叫做test；它有 3 列，id，name和created。我用几条记录填充表，然后尝试删除一条。以下是我用来尝试完成此操作的语句。
DELETE t FROM test t WHERE t.name = 'foo' LIMIT 1
DELETE t FROM test t WHERE t.name = 'foo' LIMIT 1
Without the use of LIMIT 1, the statement executes just fine, but of course I wouldn't be using LIMIT if there wasn't a need for it.
如果不使用 LIMIT 1，语句执行得很好，但是如果不需要它，我当然不会使用 LIMIT。
I'm fully aware that I can use another statement to accomplish this DELETE successfully. See below:
DELETE FROM test WHERE name = 'foo' LIMIT 1
我完全知道我可以使用另一个语句来成功完成此 DELETE。见下文：
DELETE FROM test WHERE name = 'foo' LIMIT 1
However my question is centered on why the first statement isn't working with LIMIT.
但是，我的问题集中在为什么第一个语句不适用于 LIMIT。
So my question is, what I have done incorrectly with respect to the first statement to generate this error?
所以我的问题是，关于产生此错误的第一个语句，我做错了什么？
采纳答案by Ian Wood
回答by Manoj Gupta
simply use
简单地使用
DELETE FROM test WHERE 1= 1 LIMIT 10
回答by ThelmaJay
DELETE t.* FROM test t WHERE t.name = 'foo' LIMIT 1
@Andre If I understood what you are asking, I think the only thing missing is the t.* before FROM.
@Andre 如果我明白你在问什么，我认为唯一缺少的是 t.* before FROM。
回答by Michel de Ruiter
回答by Joshua joseph bissot
Use row_count - your_desired_offset
用 row_count - your_desired_offset
So if we had 10 rows and want to offset 3
所以如果我们有 10 行并且想要偏移 3
10 - 3 = 7
Now the query delete from table where this = that order asc limit 7keeps the last 3, and order descto keep the first 3:
现在查询delete from table where this = that order asc limit 7保留最后 3 个，并 order desc保留前 3 个：
$row_count - $offset = $limit
Delete from table where entry = criteria order by ts asc limit $limit
回答by Shaolin
There is a workaround to solve this problem by using a subquery.
有一种解决方法可以通过使用子查询来解决此问题。
DELETE t1 FROM test t1 JOIN (SELECT t.id FROM test LIMIT 1) t2 ON t1.id = t2.id
DELETE t1 FROM test t1 JOIN (SELECT t.id FROM test LIMIT 1) t2 ON t1.id = t2.id
Because the LIMIT is inside the subquery the join will match only 1 row and thus the query will delete only this row.
因为 LIMIT 在子查询中，连接将只匹配 1 行，因此查询将只删除这一行。
回答by davidman77
First I struggled a bit with a
DELETE FROM ... USING ... WHERE query,...
Since i wanted to test first
so i tried with SELECT FROM ... USING... WHERE ...
and this caused an error , ...
Then i wanted to reduce the number of deletions adding
LIMIT 10
which also produced an error
Then i removed the "LIMIT" and - hurray - it worked:
"1867 rows deleted. (Query took 1.3025 seconds.)"
首先，我用 DELETE FROM ... USING ... WHERE 查询有点挣扎，...因为我想先测试所以我尝试使用 SELECT FROM ... USING... WHERE ... 这导致了错误，...然后我想减少删除的次数，添加
LIMIT 10 这也产生了错误然后我删除了“LIMIT”并且 - 欢呼 - 它起作用了：“删除了 1867 行。（查询用了 1.3025 秒。）”
The query was:
查询是：
DELETE FROM tableX
USING tableX , tableX as Dup
WHERE NOT tableX .id = Dup.id
AND tableX .id > Dup.id
AND tableX .email= Dup.email
AND tableX .mobil = Dup.mobil
This worked.
这奏效了。
