本文目录导读：
我来分享一个完整的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";
// ... 迁移逻辑
这个示例涵盖了大部分数据迁移需求,你可以根据实际情况调整使用。
