在安全模式下删除mysql [英] mysql delete under safe mode

问题描述

我有一位餐桌指导员，我想删除薪水在一定范围内的记录 直观的方式是这样的:

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;

但是，在安全模式下，如果不提供主键(ID)，则无法删除记录.

However, under safe mode, I cannot delete a record without providing a primary key(ID).

所以我写了以下sql:

So I write the following sql:

delete from instructor where ID in (select ID from instructor where salary between 13000 and 15000);

但是，有一个错误:

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);

它不会产生错误.

我的问题是:

1. 此错误消息的真正含义是什么，为什么我的代码是错误的?
2. 如何重写此代码以使其在安全模式下工作?

谢谢！

推荐答案

谷歌搜索，流行的答案似乎是:

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.

因此，您的第二个查询离标记更近了，但是又遇到了另一个问题:MySQL对子查询施加了一些限制，其中之一是您无法在子查询中从中选择表时修改表.

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:

通常，您不能修改表并从同一表中选择 在子查询中.例如，此限制适用于以下语句 以下形式:

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 ...);

异常:如果对FROM子句中的已修改表使用子查询，则上述禁止条件不适用.示例:

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 ...);

此处FROM子句中子查询的结果存储为临时表，因此在更新到t时已经选择了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.

最后一点是您的答案.在临时表中选择目标ID，然后通过引用该表中的ID进行删除:

That last bit is your answer. Select target IDs in a temporary table, then delete by referencing the IDs in that table:

DELETE FROM instructor WHERE id IN (
  SELECT temp.id FROM (
    SELECT id FROM instructor WHERE salary BETWEEN 13000 AND 15000
  ) AS temp
);

SQLFiddle演示.

这篇关于在安全模式下删除mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！