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

Question

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

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

因为数据已经收集了很多年并且几乎没有重复控制，所以'member'表中还有一个名为'superseded_by'的字段，其中包含取代该成员的成员的id。默认情况下，superseded_by 设置为 member_id。任何被 supperseded_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.id 存储为 member_id 并且每个历史记录条目都有一个单独的主键之外，member_history 的架构几乎相同。 (注意:我稍微掩饰了语法，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 表中并不是真正必要的，除非您担心历史记录中的悬空引用。