php - MySQL 与 InnoDB 版本控制行

Question

我想要一种控制/跟踪行修订的方法。我正在努力寻找解决这个问题的最佳方案。

首先想到的是有一个带有 id 的表来标识行和修订号的 id。组合的 id 将成为主键。因此示例数据可能如下所示:

1, 0, "原始帖子"1, 1, "修改后的帖子"1、2、“再次修改帖子”

如何创建具有这种行为的表？或者有更好的解决方案吗？

我喜欢 InnoDB，因为它支持 MySQL 5.6+ 中的事务、外键和全文。

我知道可以通过插入数据的方式来“强制”这种行为，但我想知道是否有办法让表自动执行此操作。

Answer 1

考虑表结构:

TABLE posts
  post_id INT AUTO_INCREMENT PK
  cur_rev_id INT FK(revisions.rev_id)

TABLE revisions
  rev_id INT AUTO_INCREMENT PK
  orig_post INT FK(posts.post_id)
  post_text VARCHAR

其中 posts 表跟踪有关帖子及其当前修订的非版本信息，而 revisions 则跟踪帖子文本的每个版本，并带有返回父级的链接邮政。由于循环 FK 约束，您需要将新的帖子插入包含在事务中。

有了这个，您应该能够轻松添加、删除、跟踪、回滚和预览对帖子的修订。

编辑:

是的，包含在事务中并不会完全有帮助，因为键被设置为 AUTO_INCRMENT，因此您需要使用 LAST_INSERT_ID() 重新回到 PHP 并一些临时 NULL 索引。

CREATE TABLE `posts` (
    `post_id` INT(10) NOT NULL AUTO_INCREMENT,
    `cur_rev_id` INT(10) NULL DEFAULT NULL,
    `post_title` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`post_id`),
    INDEX `FK_posts_revisions` (`cur_rev_id`),
) ENGINE=InnoDB

CREATE TABLE `revisions` (
    `rev_id` INT(10) NOT NULL AUTO_INCREMENT,
    `orig_post` INT(10) NULL DEFAULT NULL,
    `post_text` VARCHAR(32000) NULL DEFAULT NULL,
    PRIMARY KEY (`rev_id`),
    INDEX `FK_revisions_posts` (`orig_post`),
) ENGINE=InnoDB

ALTER TABLE `posts`
    ADD CONSTRAINT `FK_posts_revisions` FOREIGN KEY (`cur_rev_id`) REFERENCES `revisions` (`rev_id`);
ALTER TABLE `revisions`
    ADD CONSTRAINT `FK_revisions_posts` FOREIGN KEY (`orig_post`) REFERENCES `posts` (`post_id`);

然后:

$db_engine->query("INSERT INTO posts (cur_rev_id, post_title) VALUES (NULL, 'My post Title!')");
$post_id = $db_engine->last_insert_id();

$db_engine->query("INSERT INTO revisions (orig_post, post_text) VALUES($post_id, 'yadda yadda')");
$rev_id = $db_engine->last_insert_id();

$db_engine->query("UPDATE posts SET cur_rev_id = $rev_id WHERE post_id = $post_id");