企业官网建设流程全解析

PHP数据版本控制与审计追踪

数据版本控制记录数据的每一次变更，支持回溯到任意历史版本。今天说说PHP中数据版本控制和审计追踪的实现。

数据版本控制的核心是保存数据变更的历史记录。

```php
class VersionedEntity
{
private PDO $pdo;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
$this->initSchema();
}

private function initSchema(): void
{
$this->pdo->exec("
CREATE TABLE IF NOT EXISTS entity_versions (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
entity_type VARCHAR(100) NOT NULL,
entity_id INT NOT NULL,
version INT NOT NULL,
data JSON NOT NULL,
changed_by INT,
change_reason VARCHAR(500),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_entity_version (entity_type, entity_id, version),
INDEX idx_entity (entity_type, entity_id)
)
");
}

public function saveVersion(string $entityType, int $entityId, array $data, int $changedBy = 0, string $reason = ''): void
{
$nextVersion = $this->getNextVersion($entityType, $entityId);

$stmt = $this->pdo->prepare("
INSERT INTO entity_versions (entity_type, entity_id, version, data, changed_by, change_reason)
VALUES (?, ?, ?, ?, ?, ?)
");
$stmt->execute([$entityType, $entityId, $nextVersion, json_encode($data), $changedBy, $reason]);
}

public function getVersion(string $entityType, int $entityId, int $version): ?array
{
$stmt = $this->pdo->prepare("
SELECT * FROM entity_versions
WHERE entity_type = ? AND entity_id = ? AND version = ?
");
$stmt->execute([$entityType, $entityId, $version]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row ? $row : null;
}

public function getHistory(string $entityType, int $entityId): array
{
$stmt = $this->pdo->prepare("
SELECT version, changed_by, change_reason, created_at
FROM entity_versions
WHERE entity_type = ? AND entity_id = ?
ORDER BY version DESC
");
$stmt->execute([$entityType, $entityId]);
return $stmt->fetchAll();
}

public function rollback(string $entityType, int $entityId, int $version): ?array
{
$versionData = $this->getVersion($entityType, $entityId, $version);
if ($versionData === null) return null;

return json_decode($versionData['data'], true);
}

public function diff(string $entityType, int $entityId, int $version1, int $version2): array
{
$v1 = $this->getVersion($entityType, $entityId, $version1);
$v2 = $this->getVersion($entityType, $entityId, $version2);

if (!$v1 || !$v2) return [];

$data1 = json_decode($v1['data'], true);
$data2 = json_decode($v2['data'], true);

$diff = [];
foreach ($data2 as $key => $value) {
if (!isset($data1[$key]) || $data1[$key] !== $value) {
$diff[$key] = ['old' => $data1[$key] ?? null, 'new' => $value];
}
}

return $diff;
}

private function getNextVersion(string $entityType, int $entityId): int
{
$stmt = $this->pdo->prepare("
SELECT COALESCE(MAX(version), 0) + 1
FROM entity_versions
WHERE entity_type = ? AND entity_id = ?
");
$stmt->execute([$entityType, $entityId]);
return (int)$stmt->fetchColumn();
}
}

class VersionedUserService
{
private PDO $pdo;
private VersionedEntity $versions;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
$this->versions = new VersionedEntity($pdo);
}

public function createUser(array $data, int $operatorId = 0): int
{
$stmt = $this->pdo->prepare("INSERT INTO users (name, email, role) VALUES (?, ?, ?)");
$stmt->execute([$data['name'], $data['email'], $data['role'] ?? 'user']);
$id = (int)$this->pdo->lastInsertId();

$this->versions->saveVersion('user', $id, $data, $operatorId, '创建用户');
return $id;
}

public function updateUser(int $id, array $data, int $operatorId = 0): void
{
$oldStmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
$oldStmt->execute([$id]);
$oldData = $oldStmt->fetch(PDO::FETCH_ASSOC);

$sets = [];
$params = [];
foreach ($data as $key => $value) {
$sets[] = "{$key} = ?";
$params[] = $value;
}
$params[] = $id;

$this->pdo->prepare("UPDATE users SET " . implode(', ', $sets) . " WHERE id = ?")->execute($params);

$this->versions->saveVersion('user', $id, array_merge($oldData, $data), $operatorId, '更新用户');
}

public function getUserHistory(int $id): array
{
return $this->versions->getHistory('user', $id);
}
}

$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$userService = new VersionedUserService($pdo);

$userId = $userService->createUser(['name' => '张三', 'email' => 'test@test.com'], 1);
echo "用户已创建 ID: {$userId}\n";

$userService->updateUser($userId, ['name' => '张三丰'], 1);
echo "用户已更新\n";

$history = $userService->getUserHistory($userId);
echo "变更历史:\n";
foreach ($history as $h) {
echo " 版本 {$h['version']}: {$h['change_reason']} ({$h['created_at']})\n";
}
?>

数据版本控制是审计和数据恢复的基础。每次数据变更都保存完整快照，可以回溯到任意历史版本。变更记录包括修改人、修改时间和修改原因，便于审计追踪。这种模式特别适合对数据完整性要求高的业务场景。