首先，我在数据库设计方面不是很有经验。我有一张哈希表和 ID。添加一组新哈希时，组中的每一行都获得相同的 id。如果新组中的任何哈希已经存在于数据库中，则新组和现有组中的所有哈希都会获得一个新的共享 ID(在重复哈希时有效地合并 ID):

INSERT INTO hashes 
    (id, hash) 
VALUES 
    ($new_id, ...), ($new_id, ...)
ON DUPLICATE KEY UPDATE 
    repeat_count = repeat_count + 1;

INSERT INTO hashes_lookup SELECT DISTINCT id FROM hashes WHERE hash IN (...);
UPDATE hashes JOIN hashes_lookup USING (id) SET id = '$new_id';
TRUNCATE TABLE hashes_lookup;

其他表引用这些 ID，因此如果 ID 发生变化，外键约束会负责跨表更新 ID。然而，这里的问题是我无法在任何子表中强制执行唯一性。如果我这样做，我的查询会失败:

Foreign key constraint for table '...', record '...' would lead to a duplicate entry in table '...'

这个错误是有道理的，给定以下测试用例，其中 id 和 value 是一个复合唯一键:

id | value
---+-------
a  | 1
b  | 2
c  | 1

然后 a 变为 c:

id | value
---+-------
c  | 1
b  | 2
c  | 1

但是 c,1 已经存在了。

如果有一个 ON UPDATE IGNORE CASCADE 选项就更好了，这样如果存在重复的行，任何重复的插入都会被忽略。但是，我很确定这里真正的问题是我的数据库设计，所以我愿意接受任何和所有建议。我目前的解决方案是不强制跨子表的唯一性，这会导致大量冗余行。

编辑:

CREATE TABLE `hashes` (
 `hash` char(64) NOT NULL,
 `id` varchar(128) NOT NULL,
 `repeat_count` int(11) NOT NULL DEFAULT '0',
 `insert_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `update_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY `hash` (`hash`) USING BTREE,
 KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `emails` (
 `id` varchar(128) NOT NULL,
 `group_id` char(5) NOT NULL,
 `email` varchar(500) NOT NULL,
 KEY `index` (`id`) USING BTREE,
 UNIQUE KEY `id` (`id`,`group_id`,`email`(255)) USING BTREE,
 CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`id`) REFERENCES `hashes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

最佳答案

我认为创建表 hash_group 来存储哈希组的 id 会很好:

CREATE TABLE `hash_group` (
 `id` BIGINT AUTO_INCREMENT NOT NULL,
 `group_name` varchar(128) NOT NULL,
 `insert_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `update_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY `group_name` (`group_name`) USING BTREE,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

并更改现有表的结构:

CREATE TABLE `hashes` (
 `hash` char(64) NOT NULL,
 `hash_group_id` BIGINT NOT NULL,
 `repeat_count` int(11) NOT NULL DEFAULT '0',
 `insert_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `update_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY `hash` (`hash`) USING BTREE,
 KEY `hashes_hash_group_id_index` (`hash_group_id`) USING BTREE,
 CONSTRAINT `hashes_hash_group_id_fk` FOREIGN KEY (`hash_group_id`) REFERENCES `hash_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `emails` (
 `hash_group_id` BIGINT NOT NULL,
 `group_id` char(5) NOT NULL,
 `email` varchar(500) NOT NULL,
 KEY `emails_hash_group_id_index` (`hash_group_id`) USING BTREE,
 UNIQUE KEY `emails_unique` (`hash_group_id`,`group_id`,`email`(255)) USING BTREE,
 CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`hash_group_id`) REFERENCES `hash_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

如果需要，还可以创建触发器来更新哈希组:

DELIMITER $$
CREATE TRIGGER `update_hash_group_name` AFTER UPDATE ON `hashes`
FOR EACH ROW
BEGIN
    UPDATE `hash_group` 
    SET `group_name` = md5(now()) -- replace to you hash formula
    WHERE id = NEW.hash_group_id;
END;$$
DELIMITER ;

并创建获取实际组 ID 的函数:

DROP FUNCTION IF EXISTS get_hash_group;

DELIMITER $$
CREATE FUNCTION get_hash_group(id INT) RETURNS INT
BEGIN
  IF (id IS NULL) THEN
    INSERT INTO `hash_group` (`group_name`) 
    VALUES (md5(now())); -- replace to you hash
    RETURN LAST_INSERT_ID();
  END IF;

  RETURN id;
END;$$
DELIMITER ;

场景:

初始填充:

INSERT INTO `hash_group` (id, group_name) VALUES 
(1, 'test1'),
(2, 'test2'),
(3, 'test3');

INSERT INTO `hashes` (hash, hash_group_id) VALUES
('hash11', 1),
('hash12', 1),
('hash13', 1),
('hash2', 2),
('hash3', 3);

INSERT INTO `emails` (hash_group_id, group_id, email)
VALUES
(1, 'g1', 'example1@'),
(2, 'g1', 'example2@'),
(3, 'g1', 'example2@');

更新hash_group场景:

START TRANSACTION;

-- Get @min_group_id - minimum group id (we will leave this id and delete other)

SELECT MIN(hash_group_id) INTO @min_group_id
FROM hashes 
WHERE hash IN ('hash11', 'hash12', 'hash2', 'hash15');

-- Replace other group ids in email table to @min_group_id

UPDATE `emails` 
SET `hash_group_id` = @min_group_id
WHERE `hash_group_id` IN (
  SELECT hash_group_id
  FROM hashes 
  WHERE @min_group_id IS NOT NULL
  AND hash IN ('hash11', 'hash12', 'hash2', 'hash15')
  -- Update only if we are gluy several hash_groups
  AND `hash_group_id` > @min_group_id
);

-- Delete other hash_groups and leave only group with @min_group_id

DELETE FROM `hash_group` WHERE `id` IN (
  SELECT hash_group_id
  FROM hashes 
  WHERE @min_group_id IS NOT NULL
  AND hash IN ('hash11', 'hash12', 'hash2', 'hash15')
  -- Delete only if we are gluy several hash_groups
  AND `hash_group_id` > @min_group_id
);

-- @group_id = existing hash_group.id or create new if @min_group_id is null (all inserted hashes are new)

SELECT get_hash_group(@min_group_id) INTO @group_id;

-- Now we can insert new hashes.

INSERT INTO `hashes` (hash, hash_group_id) VALUES
('hash11', @group_id),
('hash12', @group_id),
('hash2', @group_id),
('hash15', @group_id)
ON DUPLICATE KEY 
UPDATE repeat_count = repeat_count + 1;


COMMIT;

关于mysql - 忽略外键更新的级联？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38486142/