我的实现可能有点复杂。
首先,您只有一个表来处理所有事情,以便仅在一个点上保持模型设计和数据完整性。
这是基本思想,您可以扩展设计created_by
& updated_by
如果需要的话。
在 MySQL 上实现
下面的实现是为了MySQL,但这个想法也可以在其他类型的 SQL 数据库上实现。
Table
DROP TABLE IF EXISTS `myTable`;
CREATE TABLE `myTable` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`version` int(11) NOT NULL DEFAULT 0 COMMENT 'Version',
`title` varchar(32) NOT NULL COMMENT 'Title',
`description` varchar(1024) DEFAULT NULL COMMENT 'Description',
`deleted_at` datetime DEFAULT NULL COMMENT 'Record deleted at',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record created at'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `myTable`
ADD PRIMARY KEY (`id`, `version`) USING BTREE,
ADD KEY `i_title` (`title`);
- The 记录ID定义为
id
& version
.
- With
deleted_at
,该模型支持软删除特征。
Views
获取当前版本
获取当前记录版本:
CREATE OR REPLACE VIEW vMyTableCurrentVersion AS
SELECT
`id`
, MAX(`version`) AS `version`
, MIN(`created_at`) AS `created_at`
FROM `myTable`
GROUP BY `id`;
获取所有记录(包括已删除的记录)
获取所有记录,包括软删除记录:
CREATE OR REPLACE VIEW vMyTableAll AS
SELECT
T.id
, T.version
, T.title
, T.description
, T.deleted_at
, _T.created_at
, T.created_at AS `updated_at`
FROM
`myTable` AS T
INNER JOIN vMyTableCurrentVersion AS _T ON
T.id = _T.id
AND T.version = _T.version;
获取记录
获取记录,删除软删除结果记录。
CREATE OR REPLACE VIEW vMyTable AS
SELECT *
FROM `vMyTableAll`
WHERE `deleted_at` IS NULL;
触发器和验证
对于这个例子,我将实现一个unique title
验证:
DROP PROCEDURE IF EXISTS myTable_uk_title;
DROP TRIGGER IF EXISTS myTable_insert_uk_title;
DROP TRIGGER IF EXISTS myTable_update_uk_title;
DELIMITER //
CREATE PROCEDURE myTable_uk_title(id INT, title VARCHAR(32)) BEGIN
IF (
SELECT COUNT(*)
FROM vMyTable AS T
WHERE
T.id <> id
AND T.title = title
) > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Duplicated "title"', MYSQL_ERRNO = 1000;
END IF;
END //
CREATE TRIGGER myTable_insert_uk_title BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
CALL myTable_uk_title(NEW.id, NEW.title);
END //
CREATE TRIGGER myTable_update_uk_title BEFORE UPDATE ON myTable
FOR EACH ROW
BEGIN
CALL myTable_uk_title(NEW.id, NEW.title);
END //
DELIMITER ;
使用示例
Select
SELECT * FROM `vMyTable`;
选择已删除的记录
SELECT * FROM `vMyTableAll`;
插入/添加/新建
INSERT INTO myTable (`title`) VALUES ('Test 1');
更新/编辑
更新操作应使用以下代码完成,而不是UPDATE ...
:
INSERT INTO myTable (`id`, `version`, `title`, `description`)
SELECT
`id`
, `version` + 1 as `version` -- New version
, `title`
, 'New description' AS `description`
FROM `vMyTable`
WHERE id = 1;
软删除
The 软删除行动是历史上的其他点:
INSERT INTO myTable (`id`, `version`, `title`, `description`, `deleted_at`)
SELECT
`id`
, `version` + 1 as `version` -- New version
, `title`
, `description`
, NOW() AS `deleted_at`
FROM `vMyTable`
WHERE id = 1;
恢复软删除的记录
INSERT INTO myTable (`id`, `version`, `title`, `description`, `deleted_at`)
SELECT
`id`
, `version` + 1 as `version` -- New version
, `title`
, `description`
, null AS `deleted_at`
FROM `vMyTableAll` -- Get with deleted
WHERE id = 1;
删除记录和历史记录
To delete相关历史记录:
DELETE FROM `myTable` WHERE id = 1;
记录历史
SELECT *
FROM `myTable`
WHERE id = 1
ORDER BY `version` DESC;
Cons
-
唯一键约束不可能,但您可以创建一个Trigger来处理它。
-
Update同时有许多记录(
UPDATE ...
)如果你想保存历史记录是不可能的。
-
Delete同时有许多记录(
DELETE ...
)如果你想保存历史记录是不可能的。
Refs
- https://gist.github.com/reduardo7/e5846b17eba288006579fc35269f3e96 https://gist.github.com/reduardo7/e5846b17eba288006579fc35269f3e96