PHP 网站数据更新指南

数据更新是网站维护中的重要环节，需要确保数据完整性、安全性和一致性。以下是 PHP 网站数据更新的全面方法：

1. 数据库更新策略

使用迁移脚本 (Migrations)

php

<?php
// 创建迁移类
class Migration {
    private $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    public function up() {
        // 执行更新操作
        $sql = "ALTER TABLE users ADD COLUMN last_login DATETIME NULL AFTER email";
        $this->pdo->exec($sql);
        
        // 记录迁移版本
        $this->pdo->exec("INSERT INTO migrations (version, applied_at) 
                         VALUES ('', NOW())");
    }
    
    public function down() {
        // 回滚操作
        $this->pdo->exec("ALTER TABLE users DROP COLUMN last_login");
        $this->pdo->exec("DELETE FROM migrations WHERE version = '20231115_add_last_login'");
    }
}

// 使用示例
try {
    $pdo = new PDO('mysql:host=localhost;dbname=yourdb', 'username', 'password');
    $migration = new($pdo);
    $migration->up();
    echo "数据库更新成功";
} catch (PDOException $e) {
    die("数据库更新失败: " . $e->getMessage());
}
?>

批量数据更新脚本

php

<?php
// 安全的大批量数据更新
function batchUpdate(PDO $pdo, $table, $updates, $batchSize = 1000) {
    $pdo->beginTransaction();
    
    try {
        $count = 0;
        foreach ($updates as $id => $data) {
            $setParts = [];
            $params = [];
            
            foreach ($data as $key => $value) {
                $setParts[] = "$key = :$key";
                $params[":$key"] = $value;
            }
            
            $params[':id'] = $id;
            $sql = "UPDATE $table SET " . implode(', ', $setParts) . " WHERE id = :id";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            
            $count++;
            if ($count % $batchSize === 0) {
                $pdo->commit();
                $pdo->beginTransaction();
                echo "已处理 $count 条记录...\n";
            }
        }
        
        $pdo->commit();
        echo "更新完成，共处理 $count 条记录\n";
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// 使用示例
$updates = [
    1 => ['name' => '新名称1', 'status' => 1],
    2 => ['name' => '新名称2', 'status' => 0],
    // ...更多更新数据
];

$pdo = new PDO('mysql:host=localhost;dbname=yourdb', 'username', 'password');
batchUpdate($pdo, 'products', $updates);
?>

2. 数据验证与清理

更新前数据验证

php

<?php
function validateDataBeforeUpdate(array $data, array $rules): array {
    $errors = [];
    
    foreach ($rules as $field => $rule) {
        if (!isset($data[$field])) {
            if ($rule['required'] ?? false) {
                $errors[$field] = "字段 {$field} 是必填的";
            }
            continue;
        }
        
        $value = $data[$field];
        
        // 类型检查
        if (isset($rule['type'])) {
            switch ($rule['type']) {
                case 'integer':
                    if (!filter_var($value, FILTER_VALIDATE_INT)) {
                        $errors[$field] = "{$field} 必须是整数";
                    }
                    break;
                case 'email':
                    if (!filter_var($value, FILTER_VALIDATE_EMAIL)) {
                        $errors[$field] = "{$field} 必须是有效的邮箱地址";
                    }
                    break;
                // 其他类型检查...
            }
        }
        
        // 长度检查
        if (isset($rule['min_length']) && strlen($value) < $rule['min_length']) {
            $errors[$field] = "{$field} 长度不能少于 {$rule['min_length']} 个字符";
        }
        
        if (isset($rule['max_length']) && strlen($value) > $rule['max_length']) {
            $errors[$field] = "{$field} 长度不能超过 {$rule['max_length']} 个字符";
        }
        
        // 自定义验证函数
        if (isset($rule['validator']) && is_callable($rule['validator'])) {
            $result = $rule['validator']($value);
            if ($result !== true) {
                $errors[$field] = $result;
            }
        }
    }
    
    return $errors;
}

// 使用示例
$data = [
    'username' => 'newuser',
    'email' => 'invalid-email',
    'age' => 'twenty'
];

$rules = [
    'username' => [
        'required' => true,
        'min_length' => 3,
        'max_length' => 20
    ],
    'email' => [
        'required' => true,
        'type' => 'email'
    ],
    'age' => [
        'type' => 'integer',
        'validator' => function($value) {
            return $value >= 18 ? true : "年龄必须大于18岁";
        }
    ]
];

$errors = validateDataBeforeUpdate($data, $rules);
if ($errors) {
    print_r($errors);
    // 不执行更新
} else {
    // 执行更新
}
?>

3. 安全更新实践

预处理语句防止SQL注入

php

<?php
function safeUpdate(PDO $pdo, $table, $id, array $data) {
    $setParts = [];
    $params = [];
    
    foreach ($data as $key => $value) {
        $setParts[] = "$key = :$key";
        $params[":$key"] = $value;
    }
    
    $params[':id'] = $id;
    
    $sql = "UPDATE $table SET " . implode(', ', $setParts) . " WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    
    return $stmt->execute($params);
}

// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=yourdb', 'username', 'password');
$updateData = [
    'name' => '更新后的名称',
    'price' => 99.99,
    'updated_at' => date('Y-m-d H:i:s')
];

safeUpdate($pdo, 'products', 123, $updateData);
?>

事务处理确保数据一致性

php

<?php
function updateWithTransaction(PDO $pdo, array $operations) {
    try {
        $pdo->beginTransaction();
        
        foreach ($operations as $op) {
            $stmt = $pdo->prepare($op['query']);
            $stmt->execute($op['params']);
            
            // 检查是否需要获取插入ID
            if (isset($op['lastInsertId']) && $op['lastInsertId']) {
                $op['lastInsertId'] = $pdo->lastInsertId();
            }
        }
        
        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        error_log("事务更新失败: " . $e->getMessage());
        return false;
    }
}

// 使用示例 - 同时更新多个表
$operations = [
    [
        'query' => "UPDATE accounts SET balance = balance - :amount WHERE id = :account_id",
        'params' => [':amount' => 100, ':account_id' => 1]
    ],
    [
        'query' => "UPDATE accounts SET balance = balance + :amount WHERE id = :account_id",
        'params' => [':amount' => 100, ':account_id' => 2