mysql - 加速或自动创建历史表触发器

Question

是否有任何有用的工具或技巧可以自动(或快速)为 MySQL 中的给定表创建历史表和触发器？ I've come across this tool so far ，但是好久没更新了。如果相关，我会使用 MySQL Workbench 的“模型”工具来构建我的数据库模型，然后将它们与我的本地数据库同步。

我想要的是能够提供表的创建脚本，并让工具神奇地吐出历史表的创建脚本和触发器，以便在插入/更新/删除时插入到这些历史表中。例如，如果我的表 foo 具有复合 PK a、b、外键 c、 d 和属性 e、f，它将为历史表 foo_history 生成具有复合 PK a 的创建脚本、b、revisionNum 和 c、d、e , f，从 c 和 d 中删除关系 - 可能还有一个列指示操作时间以及它是否是 INSERT/UPDATE/删除。

如果它还不存在，我很乐意制作这样的脚本并分享它，但如果它存在，我不想重新发明轮子。如果没有，您认为其他人使用此类脚本最方便的格式是什么/您将如何将其融入您的模型开发工作流程？

Answer 1

我为自己创建了这个小怪物 php 脚本，它从一些速记 txt 文件生成 SQL DDL，现在它可以生成历史表及其 CREATE TRIGGER 语句。

http://simpleddl.coolpage.biz/

示例来源:

// ZZZ means "Create History Table + Triggers"
// you can try editing it live at the link above

!DROP
= ID id P AI

person ZZZ
  ID
  name _
  N mother_id -> person
  N father_id -> person
  !FK mother_id, father_id -> family

family ZZZ
  P female_id -> person
  P male_id   -> person

生成的 SQL DDL:

DROP   TABLE IF     EXISTS person;
CREATE TABLE IF NOT EXISTS person (
   id         INT NOT NULL AUTO_INCREMENT,
   name       VARCHAR(255) NOT NULL,
   mother_id  INT NULL,
   father_id  INT NULL,
   PRIMARY KEY ( id ),
   FOREIGN KEY ( mother_id ) REFERENCES person( id ),
   FOREIGN KEY ( father_id ) REFERENCES person( id ),
   FOREIGN KEY ( mother_id, father_id ) REFERENCES family( female_id, male_id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP   TABLE IF     EXISTS family;
CREATE TABLE IF NOT EXISTS family (
   female_id  INT NOT NULL,
   male_id    INT NOT NULL,
   PRIMARY KEY ( female_id, male_id ),
   FOREIGN KEY ( female_id ) REFERENCES person( id ),
   FOREIGN KEY ( male_id ) REFERENCES person( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP   TABLE IF     EXISTS zz_person;
CREATE TABLE IF NOT EXISTS zz_person (
   id         INT NOT NULL,
   name       VARCHAR(255) NOT NULL,
   mother_id  INT NULL,
   father_id  INT NULL,
   _zz_id     INT NOT NULL AUTO_INCREMENT,
   _zz_op     CHAR(1) NOT NULL,
   _zz_date   datetime NOT NULL,
   PRIMARY KEY ( _zz_id ),
   INDEX P ( id ),
   INDEX ( father_id, mother_id ),
   INDEX ( _zz_op ),
   INDEX ( _zz_date )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP   TABLE IF     EXISTS zz_family;
CREATE TABLE IF NOT EXISTS zz_family (
   female_id  INT NOT NULL,
   male_id    INT NOT NULL,
   _zz_id     INT NOT NULL AUTO_INCREMENT,
   _zz_op     CHAR(1) NOT NULL,
   _zz_date   datetime NOT NULL,
   PRIMARY KEY ( _zz_id ),
   INDEX P ( male_id, female_id ),
   INDEX ( _zz_op ),
   INDEX ( _zz_date )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


DROP   TRIGGER IF EXISTS INSERT_ON_person;
CREATE TRIGGER           INSERT_ON_person
                   AFTER INSERT ON person
FOR EACH ROW INSERT INTO
zz_person (     id,     name,     mother_id,     father_id, _zz_date, _zz_op )
   VALUES ( NEW.id, NEW.name, NEW.mother_id, NEW.father_id,  NOW()  ,  'i'   );

DROP   TRIGGER IF EXISTS UPDATE_ON_person;
CREATE TRIGGER           UPDATE_ON_person
                   AFTER UPDATE ON person
FOR EACH ROW INSERT INTO
zz_person (     id,     name,     mother_id,     father_id, _zz_date, _zz_op )
   VALUES ( NEW.id, NEW.name, NEW.mother_id, NEW.father_id,  NOW()  ,  'u'   );

DROP   TRIGGER IF EXISTS DELETE_ON_person;
CREATE TRIGGER           DELETE_ON_person
                   AFTER DELETE ON person
FOR EACH ROW INSERT INTO
zz_person (     id,     name,     mother_id,     father_id, _zz_date, _zz_op )
   VALUES ( OLD.id, OLD.name, OLD.mother_id, OLD.father_id,  NOW()  ,  'd'   );

DROP   TRIGGER IF EXISTS INSERT_ON_family;
CREATE TRIGGER           INSERT_ON_family
                   AFTER INSERT ON family
FOR EACH ROW INSERT INTO
zz_family (     female_id,     male_id, _zz_date, _zz_op )
   VALUES ( NEW.female_id, NEW.male_id,  NOW()  ,  'i'   );

DROP   TRIGGER IF EXISTS UPDATE_ON_family;
CREATE TRIGGER           UPDATE_ON_family
                   AFTER UPDATE ON family
FOR EACH ROW INSERT INTO
zz_family (     female_id,     male_id, _zz_date, _zz_op )
   VALUES ( NEW.female_id, NEW.male_id,  NOW()  ,  'u'   );

DROP   TRIGGER IF EXISTS DELETE_ON_family;
CREATE TRIGGER           DELETE_ON_family
                   AFTER DELETE ON family
FOR EACH ROW INSERT INTO
zz_family (     female_id,     male_id, _zz_date, _zz_op )
   VALUES ( OLD.female_id, OLD.male_id,  NOW()  ,  'd'   );

测试 fiddle :

http://sqlfiddle.com/#!2/d05e6/1