需要 mysql 模式设计帮助 - 当前模式需要触发器内的动态 sql

Question

我想我的数据库设计得很糟糕，但我目前对我需要在触发器中使用动态sql这一事实感到困惑，这让mysql不高兴。

背景是我创建了一个包含几十个表的会员数据库，其中主要的一个是具有唯一主键“id”的“member”表。还有许多其他表具有引用 member.id 字段的外键。

由于数据已收集多年且几乎没有重复控制，因此“member”表中还有另一个名为“superseded_by”的字段，其中包含取代该字段的成员的 ID。默认情况下，superseded_by 设置为member_id。任何其 superseded_by <> 的人id 被认为是一个骗子。

现在棘手的部分是……当我们识别出一个骗局时，我们希望将 superseded_by 字段设置为指向新的主要成员并使用指向现在冗余的成员 ID 的外键更新所有表。我尝试使用更新后触发器来做到这一点...然后我尝试通过从 information_schema 查询外键并使用动态 sql 来更新它们来变得聪明。

这显然不起作用（错误代码：1336 存储函数或触发器中不允许动态 SQL）。

我假设有一种更好的方法来设计我没有想到的模式/处理欺骗。

请帮忙...

代码片段：

-- ---
-- Table 'member'
-- ---
DROP TABLE IF EXISTS member;
CREATE TABLE member (
    id INTEGER AUTO_INCREMENT,
    superseded_by INTEGER DEFAULT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('M', 'F') DEFAULT NULL,
    mailing_address_id INTEGER DEFAULT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (mailing_address_id) REFERENCES mailing_address (id),
    FOREIGN KEY (superseded_by) REFERENCES member (id)
);
DELIMITER $$
    CREATE TRIGGER set_superseded_by_on_insert BEFORE INSERT ON member FOR EACH ROW
    BEGIN
        SET NEW.superseded_by = NEW.id;
    END$$

    -- Trigger to update other tables (volunteers, donations, presenters, etc.) when member's superseded_by record is updated
    -- Assumes the new superseding person exists (they should also not be superseded by anyone themselves)
    CREATE TRIGGER adjust_foreign_member_keys_on_superseded_by_update AFTER UPDATE ON member FOR EACH ROW
    BEGIN
        DECLARE db, tbl, col VARCHAR(64);
        DECLARE fk_update_statement VARCHAR(200);
        DECLARE no_more_rows BOOLEAN;
        DECLARE fks CURSOR FOR  SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
                                FROM information_schema.TABLE_CONSTRAINTS tc
                                JOIN information_schema.KEY_COLUMN_USAGE kcu ON
                                        tc.table_schema = kcu.table_schema AND tc.constraint_name = kcu.constraint_name
                                WHERE   tc.constraint_type='FOREIGN KEY' AND
                                        kcu.REFERENCED_TABLE_NAME = 'member' AND
                                        kcu.REFERENCED_COLUMN_NAME = 'id';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

        IF NEW.superseded_by <> OLD.superseded_by THEN

            OPEN fks;
            SET no_more_rows = FALSE;
            update_loop: LOOP
                FETCH fks INTO db, tbl, col;
                IF no_more_rows THEN
                    LEAVE update_loop;
                END IF;
                SET @fk_update_statement = CONCAT("UPDATE ", db, ".", tbl, " SET ", col, " = NEW.superseded_by WHERE ", col, " = NEW.id;");
                PREPARE stmt FROM @fk_update_statement;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END LOOP;
            CLOSE fks;

        END IF;
    END$$
DELIMITER ;

Answer 1

为什么要尝试在主表中维护重复项？似乎您最好使用member 表和member_history 表来跟踪以前的更改。您可以通过一个表来存储更改的字段、更改的日期以及旧值和新值来做到这一点。或者您可以在更新之前存储成员表的先前快照。例如：

INSERT INTO member_history SELECT NULL, * FROM member WHERE id = ?
UPDATE member SET [...] WHERE id = ?

member_history 的架构几乎相同，只是您将member.id 存储为member_id 并且每个历史记录条目都有一个单独的主键。 （注意：我稍微掩盖了语法，NULL, * 部分可能不起作用，在这种情况下，您可能需要显式命名所有字段。还没有花时间检查它）。

CREATE TABLE member (
    id INTEGER AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('M', 'F') DEFAULT NULL,
    mailing_address_id INTEGER DEFAULT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (mailing_address_id) REFERENCES mailing_address (id),
);

CREATE TABLE member_history (
    id INTEGER AUTO_INCREMENT,
    member_id INTEGER NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('M', 'F') DEFAULT NULL,
    mailing_address_id INTEGER DEFAULT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (member_id) REFERENCES member (id),
);

请注意，我删除了member 表中的superseded_by 字段以及member_history 表中mailing_address 的外键。您不应该再需要 superseded_by 并且将外键保留在 member_history 表中并不是真正必要的，除非您担心历史记录中存在悬空引用。

Answer 2

好的，对此有几点想法：

superseded_by 正在引用同一个表上的 id，并且通常等于后者 - 不是在您能够的情况下不过，要识别一个骗局，在这种情况下，它将指向另一个已经存在的成员的 id。

鉴于我们可以安全地假设没有 superseded_by 字段会损害外键约束。

我进一步假设尚未识别的欺骗者的 id 和 superseded_by 字段是相等的。

因此，如果上述所有情况均成立，您可以将其他相关表的外键弯曲为引用 superseded_by 而不是 id。通过这种方式，您可以将对重复项所做的更改级联到其他表，并且仍然具有与以前完全相同的约束。

你认为呢？我错过了什么吗？

请注意，只有当您使用 InnoDB 而不是 MyISAM 时，才可以选择此选项。

问候，
埃夫克斯

Answer 3

mysql中的触发器和存储函数都有局限性，我们不能在这两者中使用动态sql。我希望这有帮助。