时间戳

首页 > 解决方案 > MySQL 更新，但删除重复键（由于唯一索引）

mysql - MySQL 更新，但删除重复键（由于唯一索引）

问题描述

我正在寻找有关更新数据库表中的几行的一些见解，这反过来又会违反表的唯一索引。对于某些情况：

我有一个多对多连接表，它定义了另外两个表之间的关系。此表上有一个唯一索引，表示子表外键对于单个父表必须是唯一的。例如：

=============================
| ID | Parent ID | Child ID |
-----------------------------
|  1 |         1 |        1 |
|  1 |         1 |        2 |
|  1 |         1 |        3 |
|  1 |         2 |        1 |
|  1 |         2 |        2 |
|  1 |         3 |        1 |
=============================

我需要弃用一些允许的Child ID值，并将那些现在弃用的 ID 映射到它们的非弃用值。（例如：在上面的示例中，如果我们弃用3=> ，则会导致唯一索引违规，因为and2之间已经存在关系）Parent 1Child 2

问题：有没有办法处理唯一索引违规，只需删除正在更新的当前行？

我知道INSERT ON DUPLICATE KEY UPDATEMySQL 中有一个子句，但是有一个UPDATE ON DUPLICATE KEY DELETE？我认为我们甚至可以接受在执行更新之前删除导致唯一索引的行。（例如：类似的东西UPDATE ON DUPLICATE KEY DELETE EXISTING ROW）

如果可以的话，只是试图避免更容易出错的超级复杂的 SQL 迁移。

感谢您提供任何帮助或提示，如果您需要更多信息，请告诉我，我将提供尽可能多的信息。

更新：感谢比尔的回答，我能够想出这个 SQL 过程：

DELIMITER $$
CREATE PROCEDURE `migrate_deprecated_children`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;

    -- Insert new items, ignoring any that already exist
    INSERT IGNORE INTO JOIN_TABLE (PARENT_ID, CHILD_ID)
        SELECT PARENT_ID,
           (CASE
                WHEN CHILD_ID = 106 THEN 68
                WHEN CHILD_ID IN (109, 111, 124, 131) THEN 110
                WHEN CHILD_ID IN (113, 114) THEN 61
                WHEN CHILD_ID = 115 THEN 48
                WHEN CHILD_ID IN (118, 143, 169, 77, 86) THEN 3
                WHEN CHILD_ID = 121 THEN -1
                WHEN CHILD_ID = 127 THEN 102
                WHEN CHILD_ID IN (134, 80) THEN 173
                WHEN CHILD_ID = 136 THEN 50
                WHEN CHILD_ID = 145 THEN 56
                WHEN CHILD_ID = 146 THEN 0
                WHEN CHILD_ID IN (14, 15) THEN 37
                WHEN CHILD_ID = 54 THEN 94
                WHEN CHILD_ID IN (84, 99) THEN 13
                WHEN CHILD_ID = 87 THEN 83
                WHEN CHILD_ID = 91 THEN 96
                WHEN CHILD_ID = 98 THEN 30
                ELSE CHILD_ID
               END) as CHILD_ID
           FROM JOIN_TABLE;

    -- Delete all deprecated rows
    DELETE FROM JOIN_TABLE WHERE CHILD_ID IN (14,15,54,77,80,84,86,87,91,98,99,106,109,111,113,114,115,118,121,124,127,131,134,136,143,145,146,169);

    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END$$

DELIMITER ;

再次感谢！

标签： mysqlsql-updateunique-constraint

解决方案

MySQL 不支持执行您所描述的单个 SQL 语句。

最简单的解决方案是这个两步过程：

INSERT IGNORE INTO m2mtable (id, parent_id, child_id)
 SELECT id, parent_id, 2
 WHERE child_id = 3;

DELETE FROM m2mtable WHERE child_id = 3;

您应该将两个语句都包装在一个事务中，这样更改对于任何其他客户端来说都是原子的。

并非每项任务都必须在单个 SQL 语句中完成。

推荐阅读