Home | 简体中文 | 繁体中文 | 杂文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Email

4.11. 内容版本控制

主表

CREATE TABLE `article` (
	`article_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`cat_id` SMALLINT(5) NOT NULL DEFAULT '0',
	`title` VARCHAR(150) NOT NULL DEFAULT '',
	`content` LONGTEXT NOT NULL,
	`author` VARCHAR(30) NOT NULL DEFAULT '',
	`keywords` VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY (`article_id`),
	INDEX `cat_id` (`cat_id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1
		

本版控制表,用于记录每次变动

CREATE TABLE `article_history` (
	`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`article_id` MEDIUMINT(8) UNSIGNED NOT NULL,
	`cat_id` SMALLINT(5) NOT NULL DEFAULT '0',
	`title` VARCHAR(150) NOT NULL DEFAULT '',
	`content` LONGTEXT NOT NULL,
	`author` VARCHAR(30) NOT NULL DEFAULT '',
	`keywords` VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
	INDEX `article_id` (`article_id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1
		

版本控制触发器

DROP TRIGGER article_history;

DELIMITER //
CREATE TRIGGER article_history BEFORE update ON article FOR EACH ROW
BEGIN
	INSERT INTO article_history SELECT * FROM article WHERE article_id = OLD.article_id;
END; //
DELIMITER;