PHP数据库数据版本化实现方法

$pdo->beginTransaction();
// 写入历史
$stmt = $pdo->prepare("
INSERT INTO article_history (article_id, title, content, updated_at, updated_by, action_type)
SELECT id, title, content, updated_at, ?, 'update' FROM article WHERE id = ?
");
$stmt->execute([$userId, $articleId]);
// 更新主表
$stmt = $pdo->prepare("UPDATE article SET title = ?, content = ?, updated_at = NOW(), updated_by = ? WHERE id = ?");
$stmt->execute([$newTitle, $newContent, $userId, $articleId]);
$pdo->commit();

DELIMITER $$
CREATE TRIGGER article_update_trigger
AFTER UPDATE ON article
FOR EACH ROW
BEGIN
    INSERT INTO article_history (article_id, title, content, updated_at, updated_by, action_type)
    VALUES (OLD.id, OLD.title, OLD.content, OLD.updated_at, NEW.updated_by, 'update');
END$$
DELIMITER ;

$current = $pdo->query("SELECT * FROM document WHERE id = $id")->fetch();
$history = json_decode($current['history_log'], true) ?: [];
$history[] = [
'version' => $current['version'] + 1,
'data'    => json_decode($current['data_json'], true),
'user_id' => $userId,
'ts'      => date('Y-m-d H:i:s')
];
$newVersion = $current['version'] + 1;
$stmt = $pdo->prepare("UPDATE document SET data_json = ?, version = ?, history_log = ? WHERE id = ?");
$stmt->execute([
json_encode($newData),
$newVersion,
json_encode($history),
$id
]);

SELECT * FROM article WHERE uuid = ? AND valid_to IS NULL;