mysql - 如何在启用二进制日志记录的安全模式下执行多表删除？

Question

假设我有以下数据结构:

DROP TABLE IF EXISTS `A`;
DROP TABLE IF EXISTS `B`;
DROP TABLE IF EXISTS `C`;

CREATE TABLE IF NOT EXISTS `C` (
    `ID_C`
        INT UNSIGNED
        NOT NULL
        AUTO_INCREMENT,

    PRIMARY KEY (`ID_C`)
)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `B` (
    `ID_B`
        INT UNSIGNED
        NOT NULL
        AUTO_INCREMENT,

    `REF_C`
        INT UNSIGNED
        NOT NULL,

    PRIMARY KEY (`ID_B`),

    INDEX `FK_C` (`REF_C` ASC),

    CONSTRAINT `FK_C`
        FOREIGN KEY (`REF_C`)
        REFERENCES `C` (`ID_C`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `A` (
    `ID_A`
        INT UNSIGNED
        NOT NULL
        AUTO_INCREMENT,

    `REF_B`
        INT UNSIGNED
        NOT NULL,

    PRIMARY KEY (`ID_A`),

    INDEX `FK_B` (`REF_B` ASC),

    CONSTRAINT `FK_B`
        FOREIGN KEY (`REF_B`)
        REFERENCES `B` (`ID_B`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO `C`
    (`ID_C`)
VALUES
    (NULL),
    (NULL);

INSERT INTO `B`
    (`ID_B`, `REF_C`)
VALUES
    (NULL, 1),
    (NULL, 1),
    (NULL, 2),
    (NULL, 2);

INSERT INTO `A`
    (`ID_A`, `REF_B`)
VALUES
    (NULL, 1),
    (NULL, 2),
    (NULL, 3),
    (NULL, 4);

表 B 可能有超过 3000 条记录:大约 600 条记录，引用表 C 中的不同行。我的服务器上启用了两个设置:

SELECT
    @@SQL_SAFE_UPDATES as `safe mode`, -- result: 1
    @@LOG_BIN as `binary log`;         -- result: 1

问题: 如何有效地删除表 A 中的所有记录，该表引用了表 C 的记录表 B 没有发出警告？

---

我尝试了什么:

DELETE
    `A`.*
FROM
    `A` INNER JOIN `B` ON `REF_B` = `ID_B`
WHERE
    `B`.`REF_C` = 1;

DBMS 服务器问题 safe_mode 错误:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

我已经删除了 B. 别名:

DELETE
    `A`.*
FROM
    `A` INNER JOIN `B` ON `REF_B` = `ID_B`
WHERE
    `REF_C` = 1;

是的，它成功了，但我有这个:

2 row(s) affected, 1 warning(s): 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

此外，我试图强制使用 PRIMARY KEY:

DELETE
    `A`.*
FROM
    `A` INNER JOIN `B` ON `REF_B` = `ID_B`
WHERE
    `A`.`ID_A` > 0
    AND
    `REF_C` = 1;

但这并没有帮助。我对我的服务器做了坏事还是坏事？什么样的做法是正确的？我错过了什么吗？

提前致谢。任何帮助将不胜感激。

---

P.S.:我知道如何使用 Google 和搜索栏。这是我发现的:

1. https://stackoverflow.com/questions/12275864/multiple-delete-not-working-with-inner-join

2. http://tech-solutions4u.blogspot.ru/2012/09/mysql-multi-delete-issue-in-safe-mode.html

等等。我试过了，但最后，我不喜欢禁用服务器功能(这不是我设置的)的想法，即使是“暂时...”。

---

编辑:

我知道，有一种解决方法可以将 GROUP_CONCAT(ID_B) 保存在临时变量中，并通过它的“标量”值执行删除:

SELECT GROUP_CONCAT(`ID_B`) INTO @tmp FROM `B` WHERE `REF_C` = 1;

DELETE FROM
    `A`
WHERE
    FIND_IN_SET(`REF_B`, @tmp)
    AND
    `ID_A` > 0;

但是它大约是 600 * 5 = 3000 个字符，所以这个想法也不是首选。我的意思是，如果别无选择，这将是最后的选择。

Answer 1

似乎有两个问题:

1. 您开启了安全更新模式。这是来自 MySQL docs 的关于 --safe-update 的示例:

   For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table.

   如您所见，此模式意味着您完全不懂 SQL，随时准备搬起石头砸自己的脚。所以这种模式对每个复杂的查询都会发出警告。如果您不想关闭模式，唯一的选择是选择要删除的所有行的 ID，并在第二个查询中显式地将 ID 传递给 WHERE 子句删除语句。同时，这种做法将解决第二个问题。

2. 第二次警告 error code 1592将更改记录到二进制日志中是完全不同的。

   Error: 1592 SQLSTATE: HY000 (ER_BINLOG_UNSAFE_STATEMENT)

   Message: Statement may not be safe to log in statement format.

   我建议的前一个命令确实有一个问题，我没有指定 ORDER BY 子句但使用了 LIMIT。这种情况下的顺序理论上是未定义的，因此您可以删除 LIMIT 子句，或者明确指定顺序:

   DELETE FROM A USING
   table_a as A
   JOIN (
     SELECT a_id
     FROM table_a as A
     JOIN table_b as B ON A.ref_b = A.b_id
     WHERE B.ref_c = 1
     ORDER BY a_id
     LIMIT 1000
   ) a_ids ON A.a_id = a_ids.a_id;