主页 / dba / 问题 / 49077
Accepted
James
Asked: 2013-09-02 12:39:15 +0800 CST

MySQL：如果外键约束，更新而不是删除？

  • 772

我有一个奇怪的问题。我知道插入重复键更新。我的问题是，由于外键约束而失败的删除是否有类似的东西？

例如：

从 table1 中删除 value='something';

但是，table2 有一个外键，它取决于我要在 table1 中删除的值，因此删除失败。我想做这样的事情：

从表 1 中删除，其中外键上的 value='something' 失败 update some_other_value='something else';

我知道这看起来很奇怪，但我有充分的理由这样做（无需详细说明，它与版本化历史数据有关，如果在其他地方引用了值，则无法销毁这些数据。）我可以想当然，如何使用多个查询来做到这一点，但如果可以的话，我想在一个查询中做到这一点。我很确定这是不可能的，但我想在放弃之前问一下:)

谢谢！

mysql foreign-key

1 个回答

  1. Best Answer

    您不能直接执行此操作，因为（我能想到的）无法交替UPDATE或DELETE从同一个查询中进行……通常查询只能执行一种类型的CRUD操作（ON DUPLICATE KEY UPDATE这是一个例外）。

    但是，您可以使用存储过程使其“感觉像”单个查询，您可以在其中CONTINUE HANDLER为您预期的外键错误设置一个。处理程序将捕获错误并可用于设置一个变量，您可以测试该变量以查看您是否遇到错误，因此还需要尝试更新查询。

    DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

    这将尝试删除；如果遇到外键错误，它将尝试更新（并且外键错误将被抑制）。该过程将返回一个记录集，其中包含每个查询影响的行数以及是否发生外键错误。如果“input_value”不是一个INT或WHERE子句更复杂，则需要相应地修改结构。

    我在这里看到的潜在问题是，如果 where 子句太宽泛，并且匹配一些会导致外键错误的行和其他不会导致外键错误的行，那么查询将不会单独处理这些行。这可以通过一个更复杂的过程来完成，该过程使用游标来查找 where 子句中的行，并在识别它们之后尝试通过主键单独删除或更新它们。这种方法效率较低，但更精确。

    • 3

相关问题