本文目录导读：
我来为你提供一个完整的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';
}
关键特性说明
- 安全性：支持试运行模式，实际执行前可预览所有操作
- 灵活性：支持自定义主键、自定义比较规则
- 完整性：支持新增、更新、删除三种修复操作
- 可追溯：完整记录所有操作日志
- 可视化：生成HTML报告便于分析和审批
- 容错性：支持事务处理，失败时自动回滚
使用建议
- 建议在实际执行前始终使用试运行模式验证
- 对于生产环境，建议先备份数据
- 根据具体业务需求调整比较逻辑
- 可以扩展支持更多数据源（如API、Excel文件等）
这个解决方案可以广泛应用于数据库同步、系统间数据迁移、主数据管理等场景。
版权声明：NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
