IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 差异数据自动修复

wen PHP项目 3

本文目录导读：

PHP 差异数据自动修复

  1. 核心差异比较和修复类
  2. 使用示例
  3. 增强版：支持文件对比修复
  4. 数据分析报告生成器
  5. 完整使用示例
  6. 关键特性说明
  7. 使用建议

我来为你提供一个完整的PHP差异数据自动修复方案，这个方案可以对比两个数据集,并自动生成和执行修复操作。

核心差异比较和修复类

<?php
class DataDiffRepairer
{
    private $sourceData;
    private $targetData;
    private $primaryKeys;
    private $repairLog = [];
    private $dryRun = true;
    private $dbConnection;
    /**
     * 构造函数
     * @param array $sourceData 源数据（参考数据）
     * @param array $targetData 目标数据（需要修复的数据）
     * @param array $primaryKeys 主键字段
     */
    public function __construct($sourceData, $targetData, $primaryKeys)
    {
        $this->sourceData = $sourceData;
        $this->targetData = $targetData;
        $this->primaryKeys = $primaryKeys;
    }
    /**
     * 设置数据库连接
     * @param PDO $connection
     */
    public function setDatabaseConnection($connection)
    {
        $this->dbConnection = $connection;
    }
    /**
     * 设置是否进行试运行
     * @param bool $dryRun
     */
    public function setDryRun($dryRun)
    {
        $this->dryRun = $dryRun;
    }
    /**
     * 获取修复日志
     */
    public function getRepairLog()
    {
        return $this->repairLog;
    }
    /**
     * 核心修复方法：分析并生成修复操作
     */
    public function analyzeAndRepair()
    {
        $operations = [];
        // 1. 找出需要新增的记录
        $operations['insert'] = $this->findRecordsToInsert();
        // 2. 找出需要更新的记录
        $operations['update'] = $this->findRecordsToUpdate();
        // 3. 找出需要删除的记录
        $operations['delete'] = $this->findRecordsToDelete();
        return $operations;
    }
    /**
     * 找出需要新增的记录
     */
    private function findRecordsToInsert()
    {
        $inserts = [];
        foreach ($this->sourceData as $sourceRecord) {
            $found = $this->findRecordByPrimaryKeys($sourceRecord, $this->targetData);
            if (!$found) {
                $inserts[] = $sourceRecord;
                $this->logAction('INSERT', $sourceRecord, '记录在目标数据中不存在');
            }
        }
        return $inserts;
    }
    /**
     * 找出需要更新的记录
     */
    private function findRecordsToUpdate()
    {
        $updates = [];
        foreach ($this->sourceData as $sourceRecord) {
            $targetRecord = $this->findRecordByPrimaryKeys($sourceRecord, $this->targetData);
            if ($targetRecord) {
                $diff = $this->compareRecords($sourceRecord, $targetRecord);
                if (!empty($diff)) {
                    $updates[] = [
                        'where' => $this->extractPrimaryKeyValues($sourceRecord),
                        'changes' => $diff,
                        'source' => $sourceRecord,
                        'target' => $targetRecord
                    ];
                    $this->logAction('UPDATE', $sourceRecord, '发现字段差异: ' . implode(', ', array_keys($diff)));
                }
            }
        }
        return $updates;
    }
    /**
     * 找出需要删除的记录
     */
    private function findRecordsToDelete()
    {
        $deletes = [];
        foreach ($this->targetData as $targetRecord) {
            $found = $this->findRecordByPrimaryKeys($targetRecord, $this->sourceData);
            if (!$found) {
                $deletes[] = $targetRecord;
                $this->logAction('DELETE', $targetRecord, '记录在源数据中不存在');
            }
        }
        return $deletes;
    }
    /**
     * 根据主键查找记录
     */
    private function findRecordByPrimaryKeys($record, $dataArray)
    {
        foreach ($dataArray as $dataRecord) {
            $isMatch = true;
            foreach ($this->primaryKeys as $key) {
                if (!isset($record[$key]) || !isset($dataRecord[$key]) || 
                    $record[$key] != $dataRecord[$key]) {
                    $isMatch = false;
                    break;
                }
            }
            if ($isMatch) {
                return $dataRecord;
            }
        }
        return null;
    }
    /**
     * 比较两条记录的差异
     */
    private function compareRecords($source, $target)
    {
        $diff = [];
        foreach ($source as $key => $value) {
            if (!in_array($key, $this->primaryKeys)) {
                // 跳过主键字段
                if (isset($target[$key])) {
                    if ($this->normalizeValue($value) !== $this->normalizeValue($target[$key])) {
                        $diff[$key] = [
                            'old' => $target[$key],
                            'new' => $value
                        ];
                    }
                } else {
                    $diff[$key] = [
                        'old' => null,
                        'new' => $value
                    ];
                }
            }
        }
        return $diff;
    }
    /**
     * 值标准化比较
     */
    private function normalizeValue($value)
    {
        if ($value === null) return 'NULL';
        if (is_numeric($value)) return (string)$value;
        return trim((string)$value);
    }
    /**
     * 提取主键值
     */
    private function extractPrimaryKeyValues($record)
    {
        $primaryKeyValues = [];
        foreach ($this->primaryKeys as $key) {
            $primaryKeyValues[$key] = $record[$key];
        }
        return $primaryKeyValues;
    }
    /**
     * 执行修复操作
     * @param string $tableName 数据库表名
     * @return bool 执行结果
     */
    public function executeRepair($tableName)
    {
        if (!$this->dbConnection) {
            throw new Exception('数据库连接未设置');
        }
        if ($this->dryRun) {
            $this->logAction('INFO', [], '试运行模式：仅生成修复计划，不实际执行');
            return true;
        }
        try {
            // 开始事务
            $this->dbConnection->beginTransaction();
            // 执行新增操作
            $operations = $this->analyzeAndRepair();
            // 1. 插入操作
            foreach ($operations['insert'] as $insertRecord) {
                $this->performInsert($tableName, $insertRecord);
            }
            // 2. 更新操作
            foreach ($operations['update'] as $updateOperation) {
                $this->performUpdate($tableName, $updateOperation);
            }
            // 3. 删除操作
            foreach ($operations['delete'] as $deleteRecord) {
                $this->performDelete($tableName, $deleteRecord);
            }
            // 提交事务
            $this->dbConnection->commit();
            $this->logAction('INFO', [], '修复操作成功完成');
            return true;
        } catch (Exception $e) {
            $this->dbConnection->rollBack();
            $this->logAction('ERROR', [], '修复失败: ' . $e->getMessage());
            throw $e;
        }
    }
    /**
     * 执行插入操作
     */
    private function performInsert($tableName, $record)
    {
        $columns = array_keys($record);
        $placeholders = array_map(function($col) {
            return ':' . $col;
        }, $columns);
        $sql = "INSERT INTO " . $tableName . 
               " (" . implode(', ', $columns) . ") VALUES (" . 
               implode(', ', $placeholders) . ")";
        $stmt = $this->dbConnection->prepare($sql);
        foreach ($record as $key => $value) {
            $stmt->bindValue(':' . $key, $value);
        }
        $stmt->execute();
        $this->logAction('EXECUTE_INSERT', $record, '成功插入记录');
    }
    /**
     * 执行更新操作
     */
    private function performUpdate($tableName, $updateOperation)
    {
        $setClauses = [];
        foreach ($updateOperation['changes'] as $field => $change) {
            $setClauses[] = $field . " = :set_" . $field;
        }
        $whereClauses = [];
        foreach ($updateOperation['where'] as $field => $value) {
            $whereClauses[] = $field . " = :where_" . $field;
        }
        $sql = "UPDATE " . $tableName . " SET " . 
               implode(', ', $setClauses) . " WHERE " . 
               implode(' AND ', $whereClauses);
        $stmt = $this->dbConnection->prepare($sql);
        foreach ($updateOperation['changes'] as $field => $change) {
            $stmt->bindValue(':set_' . $field, $change['new']);
        }
        foreach ($updateOperation['where'] as $field => $value) {
            $stmt->bindValue(':where_' . $field, $value);
        }
        $stmt->execute();
        $this->logAction('EXECUTE_UPDATE', $updateOperation, '成功更新记录');
    }
    /**
     * 执行删除操作
     */
    private function performDelete($tableName, $record)
    {
        $whereClauses = [];
        foreach ($this->primaryKeys as $key) {
            $whereClauses[] = $key . " = :" . $key;
        }
        $sql = "DELETE FROM " . $tableName . " WHERE " . implode(' AND ', $whereClauses);
        $stmt = $this->dbConnection->prepare($sql);
        foreach ($this->primaryKeys as $key) {
            $stmt->bindValue(':' . $key, $record[$key]);
        }
        $stmt->execute();
        $this->logAction('EXECUTE_DELETE', $record, '成功删除记录');
    }
    /**
     * 记录日志
     */
    private function logAction($action, $data, $description)
    {
        $this->repairLog[] = [
            'timestamp' => date('Y-m-d H:i:s'),
            'action' => $action,
            'data' => $data,
            'description' => $description
        ];
    }
}

使用示例

<?php
// 使用示例
try {
    // 1. 准备数据（示例数据）
    $sourceData = [
        ['id' => 1, 'name' => '张三', 'age' => 28, 'email' => 'zhangsan@example.com'],
        ['id' => 2, 'name' => '李四', 'age' => 32, 'email' => 'lisi@example.com'],
        ['id' => 3, 'name' => '王五', 'age' => 25, 'email' => 'wangwu@example.com'],
        ['id' => 4, 'name' => '赵六', 'age' => 30, 'email' => 'zhaoliu@example.com']
    ];
    $targetData = [
        ['id' => 1, 'name' => '张三', 'age' => 27, 'email' => 'zhangsan@example.com'],
        ['id' => 2, 'name' => '李四', 'age' => 32, 'email' => 'lisi_old@example.com'],
        ['id' => 3, 'name' => '王五', 'age' => 25, 'email' => 'wangwu@example.com'],
        ['id' => 5, 'name' => '冗余数据', 'age' => 50, 'email' => 'redundant@example.com']
    ];
    // 2. 创建修复器实例
    $primaryKeys = ['id']; // 主键字段
    $repairer = new DataDiffRepairer($sourceData, $targetData, $primaryKeys);
    // 3. 设置数据库连接（可选）
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
    $repairer->setDatabaseConnection($pdo);
    // 4. 设置为试运行模式（默认）
    $repairer->setDryRun(true);
    // 5. 分析差异并生成修复计划
    $operations = $repairer->analyzeAndRepair();
    echo "=== 差异分析结果 ===\n";
    echo "需要新增的记录数: " . count($operations['insert']) . "\n";
    echo "需要更新的记录数: " . count($operations['update']) . "\n";
    echo "需要删除的记录数: " . count($operations['delete']) . "\n\n";
    // 打印修复计划
    echo "=== 修复计划 ===\n";
    echo "\n【新增记录】\n";
    foreach ($operations['insert'] as $insert) {
        echo "  - ID: " . $insert['id'] . ", 姓名: " . $insert['name'] . "\n";
    }
    echo "\n【更新记录】\n";
    foreach ($operations['update'] as $update) {
        $changes = [];
        foreach ($update['changes'] as $field => $change) {
            $changes[] = "$field: {$change['old']} => {$change['new']}";
        }
        echo "  - ID: " . $update['where']['id'] . ", 变更: " . implode(', ', $changes) . "\n";
    }
    echo "\n【删除记录】\n";
    foreach ($operations['delete'] as $delete) {
        echo "  - ID: " . $delete['id'] . ", 姓名: " . $delete['name'] . "\n";
    }
    // 6. 执行实际修复（去掉注释）
    // $repairer->setDryRun(false);
    // $repairer->executeRepair('users');
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}

增强版：支持文件对比修复

<?php
class FileDataDiffRepairer extends DataDiffRepairer
{
    private $sourceFile;
    private $targetFile;
    private $backupDir;
    public function __construct($sourceFile, $targetFile, $primaryKeys)
    {
        $this->sourceFile = $sourceFile;
        $this->targetFile = $targetFile;
        // 读取文件数据
        $sourceData = $this->readDataFromFile($sourceFile);
        $targetData = $this->readDataFromFile($targetFile);
        parent::__construct($sourceData, $targetData, $primaryKeys);
    }
    /**
     * 从文件读取数据
     */
    private function readDataFromFile($file)
    {
        $data = [];
        if (pathinfo($file, PATHINFO_EXTENSION) == 'csv') {
            // CSV文件处理
            if (($handle = fopen($file, "r")) !== FALSE) {
                $headers = fgetcsv($handle);
                while (($row = fgetcsv($handle)) !== FALSE) {
                    $record = array_combine($headers, $row);
                    $data[] = $record;
                }
                fclose($handle);
            }
        } elseif (pathinfo($file, PATHINFO_EXTENSION) == 'json') {
            // JSON文件处理
            $jsonString = file_get_contents($file);
            $data = json_decode($jsonString, true);
        }
        return $data;
    }
    /**
     * 导出到文件
     */
    public function exportToFile($file)
    {
        // 对数据进行整合
        $allData = array_merge($this->targetData, $this->analyzeAndRepair()['insert']);
        // 应用更新
        $operations = $this->analyzeAndRepair();
        foreach ($operations['update'] as $update) {
            foreach ($allData as &$record) {
                if ($this->isPrimaryKeyMatch($record, $update['where'])) {
                    foreach ($update['changes'] as $field => $change) {
                        $record[$field] = $change['new'];
                    }
                }
            }
            unset($record);
        }
        // 应用删除
        foreach ($operations['delete'] as $delete) {
            foreach ($allData as $key => $record) {
                if ($this->isPrimaryKeyMatch($record, $delete)) {
                    unset($allData[$key]);
                }
            }
        }
        // 重新索引并导出
        $allData = array_values($allData);
        if (pathinfo($file, PATHINFO_EXTENSION) == 'csv') {
            $this->writeToCsv($file, $allData);
        } elseif (pathinfo($file, PATHINFO_EXTENSION) == 'json') {
            file_put_contents($file, json_encode($allData, JSON_PRETTY_PRINT));
        }
        return $allData;
    }
    private function isPrimaryKeyMatch($record1, $record2)
    {
        foreach ($this->primaryKeys as $key) {
            if ($record1[$key] != $record2[$key]) {
                return false;
            }
        }
        return true;
    }
    private function writeToCsv($file, $data)
    {
        if (empty($data)) return;
        $handle = fopen($file, 'w');
        fputcsv($handle, array_keys($data[0]));
        foreach ($data as $row) {
            fputcsv($handle, $row);
        }
        fclose($handle);
    }
    /**
     * 备份当前目标文件
     */
    public function createBackup()
    {
        if (!$this->backupDir) {
            $this->backupDir = dirname($this->targetFile) . '/backup_' . date('Ymd_His');
        }
        if (!file_exists($this->backupDir)) {
            mkdir($this->backupDir, 0755, true);
        }
        $backupFile = $this->backupDir . '/' . basename($this->targetFile);
        copy($this->targetFile, $backupFile);
        return $backupFile;
    }
}

数据分析报告生成器

<?php
class DataRepairReportGenerator
{
    private $repairer;
    private $operations;
    public function __construct($repairer)
    {
        $this->repairer = $repairer;
        $this->operations = $repairer->analyzeAndRepair();
    }
    /**
     * 生成HTML报告
     */
    public function generateHtmlReport()
    {
        $html = "<!DOCTYPE html>\n";
        $html .= "<html>\n<head>\n";
        $html .= "<meta charset='UTF-8'>\n";
        $html .= "<title>数据差异修复报告</title>\n";
        $html .= "<style>
                    body { font-family: Arial, sans-serif; margin: 20px; }
                    .header { background-color: #f0f0f0; padding: 20px; border-radius: 5px; }
                    .summary { background-color: #e7f3fe; padding: 15px; margin: 20px 0; }
                    .section { margin: 20px 0; }
                    table { border-collapse: collapse; width: 100%; }
                    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                    th { background-color: #4CAF50; color: white; }
                    .success { color: green; }
                    .warning { color: orange; }
                    .error { color: red; }
                </style>\n";
        $html .= "</head>\n<body>\n";
        // 头部信息
        $html .= "<div class='header'>";
        $html .= "<h1>数据差异自动修复报告</h1>";
        $html .= "<p>生成时间: " . date('Y-m-d H:i:s') . "</p>";
        $html .= "<p>报告类型: " . ($this->repairer->getRepairLog()[0]['description'] ?? '标准报告') . "</p>";
        $html .= "</div>\n";
        // 统计汇总
        $html .= "<div class='summary'>";
        $html .= "<h2>修复操作统计</h2>";
        $html .= "<ul>";
        $html .= "<li>需要新增的记录: <strong>{$this->operation('insert')}</strong> 条</li>";
        $html .= "<li>需要更新的记录: <strong>{$this->operation('update')}</strong> 条</li>";
        $html .= "<li>需要删除的记录: <strong>{$this->operation('delete')}</strong> 条</li>";
        $html .= "</ul>";
        $html .= "</div>\n";
        // 详细操作列表
        if (!empty($this->operations['insert'])) {
            $html .= "<div class='section'>";
            $html .= "<h2>推荐的新增记录</h2>";
            $html .= $this->generateTable($this->operations['insert']);
            $html .= "</div>\n";
        }
        if (!empty($this->operations['update'])) {
            $html .= "<div class='section'>";
            $html .= "<h2>推荐的更新操作</h2>";
            $html .= "<table>";
            $html .= "<tr><th>主键</th><th>变更字段</th><th>旧值</th><th>新值</th></tr>";
            foreach ($this->operations['update'] as $update) {
                foreach ($update['changes'] as $field => $change) {
                    $html .= "<tr>";
                    $html .= "<td>" . json_encode($update['where']) . "</td>";
                    $html .= "<td>{$field}</td>";
                    $html .= "<td>{$change['old']}</td>";
                    $html .= "<td class='warning'>{$change['new']}</td>";
                    $html .= "</tr>";
                }
            }
            $html .= "</table>";
            $html .= "</div>\n";
        }
        // 修复日志
        if ($this->repairer->getRepairLog()) {
            $html .= "<div class='section'>";
            $html .= "<h2>修复执行日志</h2>";
            $html .= "<table>";
            $html .= "<tr><th>时间</th><th>操作</th><th>描述</th></tr>";
            foreach ($this->repairer->getRepairLog() as $log) {
                $html .= "<tr>";
                $html .= "<td>{$log['timestamp']}</td>";
                $html .= "<td>{$log['action']}</td>";
                $html .= "<td>{$log['description']}</td>";
                $html .= "</tr>";
            }
            $html .= "</table>";
            $html .= "</div>\n";
        }
        $html .= "</body>\n</html>";
        return $html;
    }
    private function operation($type)
    {
        return count($this->operations[$type]);
    }
    private function generateTable($data)
    {
        if (empty($data)) return "";
        $html = "<table>";
        // 表头
        $html .= "<tr>";
        foreach (array_keys($data[0]) as $field) {
            $html .= "<th>{$field}</th>";
        }
        $html .= "</tr>";
        // 数据行
        foreach ($data as $record) {
            $html .= "<tr>";
            foreach ($record as $value) {
                $html .= "<td>{$value}</td>";
            }
            $html .= "</tr>";
        }
        $html .= "</table>";
        return $html;
    }
}

完整使用示例

<?php
// 完整使用流程示例
try {
    // 1. 初始化数据（可以从数据库、文件或其他来源获取）
    $sourceData = fetchSourceData(); // 假设从系统A获取
    $targetData = fetchTargetData(); // 假设从系统B获取
    // 2. 创建修复器
    $primaryKeys = ['user_id']; // 主键
    $repairer = new DataDiffRepairer($sourceData, $targetData, $primaryKeys);
    // 3. 分析差异
    echo "开始分析数据差异...\n";
    $operations = $repairer->analyzeAndRepair();
    // 4. 生成报告
    $reportGenerator = new DataRepairReportGenerator($repairer);
    // 保存HTML报告
    file_put_contents('repair_report.html', $reportGenerator->generateHtmlReport());
    echo "报告已生成: repair_report.html\n";
    // 5. 设置数据库连接并执行修复（如果需要）
    if (isset($dbConfig)) {
        $pdo = new PDO(
            "mysql:host={$dbConfig['host']};dbname={$dbConfig['db']}",
            $dbConfig['user'],
            $dbConfig['pass']
        );
        $repairer->setDatabaseConnection($pdo);
        // 先试运行
        $repairer->setDryRun(true);
        echo "\n=== 试运行模式 ===\n";
        echo "即将执行的操作:\n";
        echo "新增: " . count($operations['insert']) . " 条\n";
        echo "更新: " . count($operations['update']) . " 条\n";
        echo "删除: " . count($operations['delete']) . " 条\n";
        // 确认后实际执行
        if (confirmAction("是否确认执行修复操作? (y/n): ")) {
            $repairer->setDryRun(false);
            $result = $repairer->executeRepair('users');
            echo "修复操作执行完成!\n";
            // 查看执行日志
            foreach ($repairer->getRepairLog() as $log) {
                echo "[" . $log['timestamp'] . "] " . $log['action'] . ": " . $log['description'] . "\n";
            }
        }
    }
} catch (Exception $e) {
    echo "程序错误: " . $e->getMessage() . "\n";
    exit(1);
}
function confirmAction($prompt) {
    echo $prompt;
    $input = trim(fgets(STDIN));
    return strtolower($input) === 'y' || strtolower($input) === 'yes';
}

关键特性说明

  1. 安全性：支持试运行模式，实际执行前可预览所有操作
  2. 灵活性：支持自定义主键、自定义比较规则
  3. 完整性：支持新增、更新、删除三种修复操作
  4. 可追溯：完整记录所有操作日志
  5. 可视化：生成HTML报告便于分析和审批
  6. 容错性：支持事务处理，失败时自动回滚

使用建议

  1. 建议在实际执行前始终使用试运行模式验证
  2. 对于生产环境，建议先备份数据
  3. 根据具体业务需求调整比较逻辑
  4. 可以扩展支持更多数据源（如API、Excel文件等）

这个解决方案可以广泛应用于数据库同步、系统间数据迁移、主数据管理等场景。

上一篇PHP 对账系统设计

下一篇PHP 补偿机制设计

相关文章

抱歉，评论功能暂时关闭!