PHP数据迁移脚本怎么写

wen PHP项目 2026-08-11 157

本文目录导读：

PHP数据迁移脚本怎么写

基础迁移脚本框架
迁移文件示例
数据迁移（不只是结构）
高级特性示例
PHP脚本执行方式
注意事项
生产环境使用建议

我来分享一个完整的PHP数据迁移脚本示例,包含多种场景和最佳实践。

基础迁移脚本框架

<?php
/**
 * 数据迁移脚本示例
 * 用法: php migrate.php [action] [option]
 */
class DatabaseMigration {
    private $pdo;
    private $config;
    private $migrationPath;
    private $logFile;
    public function __construct($config) {
        $this->config = $config;
        $this->migrationPath = __DIR__ . '/migrations/';
        $this->logFile = __DIR__ . '/migration.log';
        try {
            $this->pdo = new PDO(
                "mysql:host={$config['host']};dbname={$config['database']};charset=utf8mb4",
                $config['username'],
                $config['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                ]
            );
        } catch (PDOException $e) {
            die("数据库连接失败: " . $e->getMessage());
        }
    }
    /**
     * 执行迁移
     */
    public function migrate($step = null) {
        $this->createMigrationsTable();
        $appliedMigrations = $this->getAppliedMigrations();
        $migrations = $this->getMigrationFiles();
        foreach ($migrations as $migration) {
            if (in_array($migration, $appliedMigrations)) {
                continue;
            }
            $result = $this->runMigration($migration);
            if ($result['success']) {
                $this->log("迁移成功: {$migration}");
                $this->recordMigration($migration);
            } else {
                $this->log("迁移失败: {$migration} - " . $result['error']);
                break; // 停止后续迁移
            }
            if ($step !== null && --$step === 0) {
                break;
            }
        }
        echo "迁移完成！\n";
    }
    /**
     * 回滚最近的一次迁移
     */
    public function rollback($step = 1) {
        $appliedMigrations = $this->getAppliedMigrations();
        $migrations = array_reverse($appliedMigrations);
        foreach ($migrations as $migration) {
            $result = $this->rollbackMigration($migration);
            if ($result['success']) {
                $this->log("回滚成功: {$migration}");
                $this->removeMigrationRecord($migration);
            } else {
                $this->log("回滚失败: {$migration} - " . $result['error']);
                break;
            }
            if (--$step === 0) break;
        }
        echo "回滚完成！\n";
    }
    /**
     * 创建迁移记录表
     */
    private function createMigrationsTable() {
        $sql = "CREATE TABLE IF NOT EXISTS migrations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            migration_name VARCHAR(255) NOT NULL,
            batch INT NOT NULL,
            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY (migration_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $this->pdo->exec($sql);
    }
    /**
     * 获取已应用的迁移
     */
    private function getAppliedMigrations() {
        $stmt = $this->pdo->query("SELECT migration_name FROM migrations ORDER BY id");
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
    /**
     * 获取迁移文件列表
     */
    private function getMigrationFiles() {
        $files = glob($this->migrationPath . '*.php');
        sort($files);
        return array_map('basename', $files);
    }
    /**
     * 执行单个迁移
     */
    private function runMigration($migration) {
        try {
            $this->pdo->beginTransaction();
            $migrationClass = $this->loadMigration($migration);
            $migrationClass->up();
            $this->pdo->commit();
            return ['success' => true];
        } catch (Exception $e) {
            $this->pdo->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    /**
     * 回滚单个迁移
     */
    private function rollbackMigration($migration) {
        try {
            $this->pdo->beginTransaction();
            $migrationClass = $this->loadMigration($migration);
            $migrationClass->down();
            $this->pdo->commit();
            return ['success' => true];
        } catch (Exception $e) {
            $this->pdo->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    /**
     * 加载迁移文件
     */
    private function loadMigration($migration) {
        $file = $this->migrationPath . $migration;
        require_once $file;
        $className = pathinfo($migration, PATHINFO_FILENAME);
        $className = str_replace('_', ' ', $className);
        $className = ucwords($className);
        $className = str_replace(' ', '', $className);
        return new $className($this->pdo);
    }
    /**
     * 记录迁移
     */
    private function recordMigration($migration) {
        $batch = $this->getLastBatch() + 1;
        $stmt = $this->pdo->prepare("INSERT INTO migrations (migration_name, batch) VALUES (?, ?)");
        $stmt->execute([$migration, $batch]);
    }
    /**
     * 删除迁移记录
     */
    private function removeMigrationRecord($migration) {
        $stmt = $this->pdo->prepare("DELETE FROM migrations WHERE migration_name = ?");
        $stmt->execute([$migration]);
    }
    /**
     * 获取最后批次
     */
    private function getLastBatch() {
        $stmt = $this->pdo->query("SELECT COALESCE(MAX(batch), 0) as batch FROM migrations");
        $result = $stmt->fetch();
        return $result['batch'];
    }
    /**
     * 记录日志
     */
    private function log($message) {
        $date = date('Y-m-d H:i:s');
        $logMessage = "[{$date}] {$message}\n";
        file_put_contents($this->logFile, $logMessage, FILE_APPEND);
        echo $message . "\n";
    }
}
// 配置
$config = [
    'host' => 'localhost',
    'database' => 'your_database',
    'username' => 'your_username',
    'password' => 'your_password'
];
// 命令行处理
if (php_sapi_name() === 'cli') {
    $action = $argv[1] ?? 'migrate';
    $option = $argv[2] ?? null;
    $migration = new DatabaseMigration($config);
    switch ($action) {
        case 'migrate':
            $migration->migrate($option ? (int)$option : null);
            break;
        case 'rollback':
            $migration->rollback($option ? (int)$option : 1);
            break;
        case 'status':
            // 可以添加状态查看功能
            break;
        default:
            echo "未知命令\n";
    }
}

迁移文件示例

20240101_create_users_table.php

<?php
class CreateUsersTable {
    private $pdo;
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    public function up() {
        $sql = "
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                email VARCHAR(100) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ";
        $this->pdo->exec($sql);
    }
    public function down() {
        $this->pdo->exec("DROP TABLE IF EXISTS users");
    }
}

数据迁移（不只是结构）

20240102_migrate_user_data.php

<?php
class MigrateUserData {
    private $pdo;
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    public function up() {
        // 示例：从旧表迁移数据到新表
        $oldTable = 'old_users';
        $newTable = 'users';
        try {
            // 检查旧表是否存在
            $stmt = $this->pdo->query("SHOW TABLES LIKE '{$oldTable}'");
            if ($stmt->rowCount() === 0) {
                echo "旧表不存在，跳过数据迁移\n";
                return;
            }
            // 批量迁移数据
            $offset = 0;
            $limit = 1000;
            while (true) {
                $stmt = $this->pdo->prepare("SELECT * FROM {$oldTable} LIMIT {$limit} OFFSET {$offset}");
                $stmt->execute();
                $oldRecords = $stmt->fetchAll(PDO::FETCH_ASSOC);
                if (empty($oldRecords)) {
                    break; // 所有数据已迁移
                }
                foreach ($oldRecords as $record) {
                    $this->processRecord($record, $newTable);
                }
                $offset += $limit;
                echo "已迁移 {$offset} 条记录...\n";
            }
            // 可选：删除旧表
            // $this->pdo->exec("DROP TABLE IF EXISTS {$oldTable}");
        } catch (Exception $e) {
            echo "数据迁移失败: " . $e->getMessage() . "\n";
            throw $e;
        }
    }
    private function processRecord($record, $table) {
        try {
            $stmt = $this->pdo->prepare(
                "INSERT IGNORE INTO {$table} (id, username, email, password, created_at) 
                 VALUES (?, ?, ?, ?, ?)"
            );
            $stmt->execute([
                $record['id'],
                $record['username'],
                $record['email'],
                $record['password_hash'], // 字段名可能不同
                $record['created_at']
            ]);
        } catch (PDOException $e) {
            // 跳过重复数据或记录错误
            echo "插入记录失败: " . $e->getMessage() . "\n";
        }
    }
    public function down() {
        // 回滚操作（如果需要）
        echo "数据迁移回滚：无法自动回滚数据迁移\n";
    }
}

高级特性示例

<?php
class AdvancedMigration {
    private $pdo;
    private $startTime;
    public function __construct($pdo) {
        $this->pdo = $pdo;
        $this->startTime = microtime(true);
    }
    public function up() {
        // 1. 添加新列
        $this->pdo->exec(
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS 
             phone VARCHAR(20) AFTER email"
        );
        // 2. 更新现有数据
        $stmt = $this->pdo->prepare(
            "UPDATE users SET 
             email = LOWER(email) WHERE email != LOWER(email)"
        );
        $stmt->execute();
        // 3. 创建索引
        $this->pdo->exec(
            "CREATE INDEX IF NOT EXISTS idx_created_at ON users(created_at)"
        );
        // 4. 数据清洗
        $this->cleanData();
        // 输出执行时间
        $executionTime = round(microtime(true) - $this->startTime, 2);
        echo "执行时间: {$executionTime} 秒\n";
    }
    private function cleanData() {
        // 清洗无效数据
        $stmt = $this->pdo->prepare(
            "DELETE FROM users WHERE email NOT LIKE '%@%'"
        );
        $deleted = $stmt->rowCount();
        if ($deleted > 0) {
            echo "清理了 {$deleted} 条无效数据\n";
        }
    }
    public function down() {
        // 回滚操作
        $this->pdo->exec("ALTER TABLE users DROP COLUMN IF EXISTS phone");
        $this->pdo->exec("DROP INDEX IF EXISTS idx_created_at ON users");
    }
}

PHP脚本执行方式

# 执行所有迁移
php migrate.php migrate
# 执行特定数量的迁移
php migrate.php migrate 2
# 回滚最近一次的迁移
php migrate.php rollback
# 回滚特定数量的迁移
php migrate.php rollback 2

注意事项

事务处理: 重要操作使用事务，确保数据一致性
分批处理: 大数据量迁移要分批处理，避免内存不足
错误处理: 完善的错误处理和日志记录
回滚机制: 提供down()方法用于回滚
幂等性: 重复执行不会产生问题
备份: 生产环境迁移前务必备份数据
测试: 在测试环境验证后再执行生产环境迁移

生产环境使用建议

// production_migrate.php
<?php
ini_set('memory_limit', '512M');
set_time_limit(0);
// 安全检查
if (php_sapi_name() !== 'cli') {
    die("只能通过命令行执行");
}
// 解析命令行参数
$options = getopt('', ['dry-run', 'force']);
$isDryRun = isset($options['dry-run']);
if (!$isDryRun && !isset($options['force'])) {
    echo "生产环境需要确认，请添加 --force 参数\n";
    exit(1);
}
// 加载配置
$config = require 'config/database.php';
// 迁移前备份（可选）
if (!$isDryRun) {
    echo "执行数据库备份...\n";
    system("mysqldump -u{$config['username']} -p{$config['password']} {$config['database']} > backup_" . date('Ymd_His') . ".sql");
}
// 执行迁移
echo $isDryRun ? "DRY RUN 模式\n" : "实际执行模式\n";
// ... 迁移逻辑

这个示例涵盖了大部分数据迁移需求,你可以根据实际情况调整使用。

本文地址： https://www.bmjup.com/post/43903.html