mysql合并主键

Question

合并具有外键的记录的最佳方法是什么。关于“合并记录”的主题很多，但它们主要是选择和分组行。这显然可以在代码中完成，但安全的数据库解决方案必须更高效。

这是我能想到的最好的解决方案。存储过程中的事务。关闭外键检查。更新所有外键。删除重复记录。

有没有人有更好的方法来做到这一点？或者是否还需要进行任何其他检查以保持 key 完整性？

数据库示例 - 未经测试

用户“John Doe”(id_user=2，重复 id = 5)不小心创建了第二个帐户，并希望将所有数据与原始帐户合并。

tbl_users

id_user  | first_name | last_name
1        | John       | Doe
2        | Jane       | Doe
3        | john       | Smith
4        | Jane       | Smith
5        | John       | Doe
6        | Blah       | Meh

tbl_likes

id_user   | apple | orange
2         | 1     | 0
5         | 1     | 1

tbl_random

id_user   | col1   | col2
1         | Aaa    | Bbb
3         | Ccc    | Ddd
5         | Eee    | Fff


DELIMITER $$

CREATE PROCEDURE `tns_merge_user` (IN id_user_old INT, IN id_user_new INT)
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

    START TRANSACTION;

        # Prevent other queries modifying tables during merge
        LOCK TABLES tbl_users WRITE;
        LOCK TABLES tbl_likes WRITE;
        LOCK TABLES tbl_random WRITE;

        #Disable foreign key checks
        SET FOREIGN_KEY_CHECKS=0;

        # Perform merge queries on related tables
        UPDATE tbl_likes SET id_user = id_user_new;
        UPDATE tbl_random SET id_user = id_user_new;

        # Delete duplicate user record
        DELETE FROM tbl_users WHERE id_user = id_user_old;

        # Re-enable foreign key checks
        SET FOREIGN_KEY_CHECKS=1;
        UNLOCK TABLES;

    COMMIT;
END
$$

Answer 1

所以我还没有收到任何答复。我开始做开发测试。但还没有在生产中使用它。

架构

CREATE TABLE `_test_profile` (
    `id_profile` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_profile__merge` int(11) NOT NULL DEFAULT '0',
    `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id_profile`),
    KEY `idx__id_profile__merge` (`id_profile__merge`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `_test_email` (
    `id_email` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_profile` int(10) unsigned NOT NULL,
    `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `type` enum('primary','postal') COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id_email`),
    KEY `idx_id_profile` (`id_profile`),
    CONSTRAINT `fk__test_email__id_profile` FOREIGN KEY (`id_profile`) REFERENCES `_test_profile` (`id_profile`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `_test_address` (
    `id_address` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_profile` int(10) unsigned NOT NULL,
    `address` text COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id_address`),
    KEY `idx_id_profile` (`id_profile`),
    CONSTRAINT `fk__test_address__id_profile` FOREIGN KEY (`id_profile`) REFERENCES `_test_profile` (`id_profile`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

测试插入

INSERT INTO _test_profile(`name`) VALUES
    ('test01'),
    ('test02'),
    ('test03');

INSERT INTO _test_email(id_profile,`type`,`email`) VALUES
    (1,'primary','test01@primary.com'),
    (2,'primary','test02@primary.com'),
    (2,'postal', 'test02@primary.com'),
    (3,'postal','test03@primary.com');

INSERT INTO _test_address(id_profile,`address`) VALUES
    (1,'Address One'),
    (2,'Address Two');

合并交易

需要包装在一个存储过程中，并用一个变量来确认它是否有效。当我开始进行适当的测试时，我将使用完整的存储过程代码编辑此答案以供将来引用。

START TRANSACTION;

    # Prevent other queries modifying tables during merge
    LOCK TABLES _test_profile WRITE;

    #Disable foreign key checks
    SET FOREIGN_KEY_CHECKS=0;

    # Modify record to be merged into
    UPDATE _test_profile
    SET id_profile__merge = 1, id_profile = 0
    WHERE id_profile =1;

    SET FOREIGN_KEY_CHECKS=1;

    # Modify record to be merged into
    UPDATE _test_profile
    SET id_profile = 1
    WHERE id_profile =3;

    #Disable foreign key checks
    SET FOREIGN_KEY_CHECKS=0;

    # Delete duplicate user record
    DELETE FROM _test_profile WHERE id_profile = 1;

    # Modify record to be merged into
    UPDATE _test_profile
    SET id_profile = 1, id_profile__merge = 0
    WHERE id_profile__merge =1;

    # Re-enable foreign key checks
    SET FOREIGN_KEY_CHECKS=1;
    UNLOCK TABLES;

COMMIT;

---

我不会在此答案中发布未经测试的代码，但一旦功能完全正常，我会将其添加到我的 git 配置文件中。