PHP数据库迁移与版本管理

PHP数据库迁移与版本管理

数据库迁移是管理数据库结构变更的标准化方式。每次修改数据库结构都记录在迁移文件中，团队成员可以按顺序执行迁移，保持数据库结构一致。

先实现一个简单的迁移系统，理解迁移的工作原理。

```php
class Migration
{
protected PDO $pdo;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}

public function up(): void {}
public function down(): void {}
}

class MigrationRunner
{
private PDO $pdo;
private string $migrationDir;
private string $table = 'migrations';

public function __construct(PDO $pdo, string $migrationDir)
{
$this->pdo = $pdo;
$this->migrationDir = rtrim($migrationDir, '/');
$this->initTable();
}

private function initTable(): void
{
$this->pdo->exec("
CREATE TABLE IF NOT EXISTS {$this->table} (
id INT AUTO_INCREMENT PRIMARY KEY,
migration VARCHAR(255) NOT NULL,
batch INT NOT NULL,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
");
}

public function migrate(): void
{
$executed = $this->getExecutedMigrations();
$files = $this->getMigrationFiles();
$batch = $this->getNextBatch();

$pending = array_diff($files, $executed);
if (empty($pending)) {
echo "所有迁移已完成\n";
return;
}

foreach ($pending as $file) {
$migrationClass = $this->loadMigration($file);
echo "执行迁移: $file\n";

try {
$this->pdo->beginTransaction();
$migrationClass->up();
$this->recordMigration($file, $batch);
$this->pdo->commit();
echo " 完成\n";
} catch (Exception $e) {
$this->pdo->rollBack();
echo " 失败: {$e->getMessage()}\n";
throw $e;
}
}
}

public function rollback(int $steps = 1): void
{
$lastBatch = $this->getLastBatch();
if ($lastBatch === 0) {
echo "没有可回滚的迁移\n";
return;
}

$migrations = $this->getBatchMigrations($lastBatch);

foreach (array_reverse($migrations) as $file) {
$migrationClass = $this->loadMigration($file);
echo "回滚迁移: $file\n";

try {
$this->pdo->beginTransaction();
$migrationClass->down();
$this->removeMigration($file);
$this->pdo->commit();
echo " 完成\n";
} catch (Exception $e) {
$this->pdo->rollBack();
echo " 失败: {$e->getMessage()}\n";
throw $e;
}
}
}

private function getExecutedMigrations(): array
{
$stmt = $this->pdo->query("SELECT migration FROM {$this->table}");
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

private function getMigrationFiles(): array
{
$files = glob($this->migrationDir . '/*.php');
sort($files);
return array_map('basename', $files);
}

private function getNextBatch(): int
{
$stmt = $this->pdo->query("SELECT COALESCE(MAX(batch), 0) FROM {$this->table}");
return (int)$stmt->fetchColumn() + 1;
}

private function getLastBatch(): int
{
$stmt = $this->pdo->query("SELECT COALESCE(MAX(batch), 0) FROM {$this->table}");
return (int)$stmt->fetchColumn();
}

private function getBatchMigrations(int $batch): array
{
$stmt = $this->pdo->prepare("SELECT migration FROM {$this->table} WHERE batch = ? ORDER BY id ASC");
$stmt->execute([$batch]);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

private function loadMigration(string $file): Migration
{
require_once $this->migrationDir . '/' . $file;
$className = pathinfo($file, PATHINFO_FILENAME);
$className = $this->formatClassName($className);
return new $className($this->pdo);
}

private function formatClassName(string $filename): string
{
preg_match('/^\d+_\d+_\d+_\d+_\d+_(.+)$/', $filename, $matches);
if (isset($matches[1])) {
return implode('', array_map('ucfirst', explode('_', $matches[1])));
}
return $filename;
}

private function recordMigration(string $file, int $batch): void
{
$stmt = $this->pdo->prepare("INSERT INTO {$this->table} (migration, batch) VALUES (?, ?)");
$stmt->execute([$file, $batch]);
}

private function removeMigration(string $file): void
{
$stmt = $this->pdo->prepare("DELETE FROM {$this->table} WHERE migration = ?");
$stmt->execute([$file]);
}
}
?>

// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$runner = new MigrationRunner($pdo, __DIR__ . '/migrations');
$runner->migrate();
?>

迁移文件示例：

// migrations/2024_01_01_000001_create_users_table.php
class CreateUsersTable extends Migration
{
public function up(): void
{
$this->pdo->exec("
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
");
}

public function down(): void
{
$this->pdo->exec("DROP TABLE IF EXISTS users");
}
}
?>

简化版的迁移文件：

// 2024_01_02_000002_add_phone_to_users.php
class AddPhoneToUsers extends Migration
{
public function up(): void
{
$this->pdo->exec("ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email");
}

public function down(): void
{
$this->pdo->exec("ALTER TABLE users DROP COLUMN phone");
}
}
?>

好的迁移系统让数据库版本管理变得简单。每个迁移文件都是小型的、可测试的、可回滚的。团队协作时不会出现数据库结构冲突，部署时也能自动更新数据库结构。