MySQL 对 DELETE 语句的限制
我针对最近遇到的错误整理了一个测试表。它涉及在尝试从 MySQL 表中删除单个记录时使用 LIMIT。
我所说的错误是“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行‘LIMIT 1’附近使用的正确语法
”我放在一起的叫做测试；它有 3 列，id、name 和 created。我在表中填充了几条记录，然后尝试删除一条记录。以下是我用来尝试实现这一目标的声明。
DELETE t FROM test t WHERE t.name = 'foo' LIMIT 1
如果不使用 LIMIT 1，该语句执行得很好，但如果没有，我当然不会使用 LIMIT对它的需要。
我完全意识到我可以使用另一个语句来成功完成此删除。见下文： DELETE FROM test WHERE name = 'foo' LIMIT 1
但是我的问题集中在为什么第一个语句不能与 LIMIT 一起使用。
所以我的问题是，对于生成此错误的第一个语句，我做错了什么？
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.
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"
The table I put together is called test; it has 3 columns, id, name and 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.
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.
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
However my question is centered on why the first statement isn't working with LIMIT.
So my question is, what I have done incorrectly with respect to the first statement to generate this error?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
只需使用
simply use
删除查询只允许在 DELETE“命令”之后使用修饰符来告诉数据库要做什么/如何处理事情。
请参阅此页面
the delete query only allows for modifiers after the DELETE 'command' to tell the database what/how do handle things.
see this page
来自文档：
From the documentation:
@Andre如果我明白你在问什么，我认为唯一缺少的是
FROM之前的 t.* 。@Andre If I understood what you are asking, I think the only thing missing is the t.* before
FROM.有一种解决方法可以通过使用派生表来解决此问题。
由于 LIMIT 位于派生表内部，因此联接将仅匹配 1 行，因此查询将仅删除这一行。
There is a workaround to solve this problem by using a derived table.
Because the LIMIT is inside the derived table the join will match only 1 row and thus the query will delete only this row.
使用 row_count - your_desired_offset
因此，如果我们有 10 行并且想要偏移 3
现在查询
delete from table where this = that order asc limit 7保留最后 3 行，并且 < code>order desc 保留前 3 个：Use
row_count - your_desired_offsetSo if we had 10 rows and want to offset 3
Now the query
delete from table where this = that order asc limit 7keeps the last 3, andorder descto keep the first 3: