PHP数据库迁移与版本管理

PHP数据库迁移与版本管理数据库迁移是管理数据库结构变更的标准化方式。每次修改数据库结构都记录在迁移文件中团队成员可以按顺序执行迁移保持数据库结构一致。先实现一个简单的迁移系统理解迁移的工作原理。phpclass 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:hostlocalhost;dbnametest, root, );$runner new MigrationRunner($pdo, __DIR__ . /migrations);$runner-migrate();?迁移文件示例// migrations/2024_01_01_000001_create_users_table.phpclass 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.phpclass 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);}}?好的迁移系统让数据库版本管理变得简单。每个迁移文件都是小型的、可测试的、可回滚的。团队协作时不会出现数据库结构冲突部署时也能自动更新数据库结构。