mysql 在安全模式下删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21841353/
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 under safe mode
提问by roland luo
I have a table instructor and I want to delete the records that have salary in a range An intuitive way is like this:
我有一个表导师，我想删除一个范围内有薪水的记录 一个直观的方法是这样的：
delete from instructor where salary between 13000 and 15000;
However, under safe mode, I cannot delete a record without providing a primary key(ID).
但是，在安全模式下，我无法在不提供主键（ID）的情况下删除记录。
So I write the following sql:
所以我写了下面的sql：
delete from instructor where ID in (select ID from instructor where salary between 13000 and 15000);
However, there is an error:
但是，有一个错误：
You can't specify target table 'instructor' for update in FROM clause
I am confused because when I write
我很困惑，因为当我写
select * from instructor where ID in (select ID from instructor where salary between 13000 and 15000);
it does not produce an error.
它不会产生错误。
My question is:
我的问题是：
- what does this error message really mean and why my code is wrong?
- how to rewrite this code to make it work under safe mode?
- 此错误消息的真正含义是什么，为什么我的代码是错误的？
- 如何重写此代码以使其在安全模式下工作？
Thanks!
谢谢！
回答by rutter
Googling around, the popular answer seems to be "just turn off safe mode":
谷歌搜索，流行的答案似乎是“关闭安全模式”：
SET SQL_SAFE_UPDATES = 0;
DELETE FROM instructor WHERE salary BETWEEN 13000 AND 15000;
SET SQL_SAFE_UPDATES = 1;
If I'm honest, I can't say I've ever made a habit of running in safe mode. Still, I'm not entirely comfortable with this answer since it just assumes you should go change your database config every time you run into a problem.
老实说，我不能说我曾经养成在安全模式下跑步的习惯。尽管如此，我对这个答案并不完全满意，因为它只是假设您每次遇到问题时都应该更改数据库配置。
So, your second query is closer to the mark, but hits another problem: MySQL applies a few restrictions to subqueries, and one of them is that you can't modify a table while selecting from it in a subquery.
因此，您的第二个查询更接近标记，但遇到了另一个问题：MySQL 对子查询应用了一些限制，其中之一是您不能在从子查询中选择表时修改表。
Quoting from the MySQL manual, Restrictions on Subqueries:
引用 MySQL 手册中的Restrictions on Subqueries：
In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
DELETE FROM t WHERE ... (SELECT ... FROM t ...); UPDATE t ... WHERE col = (SELECT ... FROM t ...); {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);Here the result from the subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.
通常，您不能在子查询中修改表并从同一个表中进行选择。例如，此限制适用于以下形式的语句：
DELETE FROM t WHERE ... (SELECT ... FROM t ...); UPDATE t ... WHERE col = (SELECT ... FROM t ...); {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);例外：如果您在 FROM 子句中对修改后的表使用子查询，则上述禁令不适用。例子：
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);此处，FROM 子句中子查询的结果存储为临时表，因此在更新 t 时已经选择了 t 中的相关行。
That last bit is your answer. Select target IDs in a temporary table, then delete by referencing the IDs in that table:
最后一点就是你的答案。在临时表中选择目标 ID，然后通过引用该表中的 ID 进行删除：
DELETE FROM instructor WHERE id IN (
SELECT temp.id FROM (
SELECT id FROM instructor WHERE salary BETWEEN 13000 AND 15000
) AS temp
);
回答by Hugo Zink
You can trick MySQL into thinking you are actually specifying a primary key column. This allows you to "override" safe mode.
您可以欺骗 MySQL 认为您实际上是在指定主键列。这允许您“覆盖”安全模式。
Assuming you have a table with an auto-incrementing numeric primary key, you could do the following:
假设您有一个带有自动递增数字主键的表，您可以执行以下操作：
DELETE FROM tbl WHERE id <> 0
回答by Peter B
Turning off safe mode in Mysql workbench 6.3.4.0
在 Mysql Workbench 6.3.4.0 中关闭安全模式
Edit menu => Preferences => SQL Editor : Other section: click on "Safe updates" ... to uncheck option
编辑菜单 => 首选项 => SQL 编辑器：其他部分：单击“安全更新”...取消选中选项
回答by Joaq
I have a far more simple solution, it is working for me; it is also a workaround but might be usable and you dont have to change your settings. I assume you can use value that will never be there, then you use it on your WHERE clause
我有一个更简单的解决方案，它对我有用；这也是一种解决方法，但可能可用，您不必更改设置。我假设你可以使用永远不会存在的值，然后你在你的 WHERE 子句中使用它
DELETE FROM MyTable WHERE MyField IS_NOT_EQUAL AnyValueNoItemOnMyFieldWillEverHave
从 MyTable 中删除 MyField IS_NOT_EQUAL AnyValueNoItemOnMyFieldWillEverHave
I don't like that solution either too much, that's why I am here, but it works and it seems better than what it has been answered
我也不太喜欢那个解决方案，这就是我在这里的原因，但它有效，而且似乎比已经回答的要好
