核心類別實現
<?php
class SqlMigrator
{
private PDO $pdo;
private string $tablePrefix = '';
public function __construct(PDO $pdo, string $tablePrefix = '')
{
$this->pdo = $pdo;
$this->tablePrefix = $tablePrefix;
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
public function executeFile(string $sqlFile): bool
{
if (!is_file($sqlFile) || !is_readable($sqlFile)) {
throw new InvalidArgumentException("SQL file not found: $sqlFile");
}
$sql = file_get_contents($sqlFile);
$statements = $this->splitStatements($sql);
$statements = $this->stripComments($statements);
$statements = $this->applyPrefix($statements);
$this->pdo->beginTransaction();
try {
foreach ($statements as $stmt) {
$stmt = trim($stmt);
if ($stmt === '') continue;
$this->pdo->exec($stmt);
}
$this->pdo->commit();
return true;
} catch (Throwable $e) {
$this->pdo->rollBack();
throw new RuntimeException("Migration failed: {$e->getMessage()}", 0, $e);
}
}
private function splitStatements(string $sql): array
{
// 簡易分割:以 ; 結尾且不在引號內
$tokens = preg_split('/;(?=(?:[^\'"]*[\'"][^\'"]*[\'"])*[^\'"]*$)/', $sql);
return array_map('trim', $tokens);
}
private function stripComments(array $stmts): array
{
$clean = [];
foreach ($stmts as $stmt) {
$lines = explode("\n", $stmt);
$filtered = [];
foreach ($lines as $line) {
$line = trim($line);
if ($line === '' || str_starts_with($line, '--') || str_starts_with($line, '#')) continue;
$filtered[] = $line;
}
$clean[] = implode("\n", $filtered);
}
return $clean;
}
private function applyPrefix(array $stmts): array
{
if (!$this->tablePrefix) return $stmts;
$prefix = $this->tablePrefix;
return array_map(function ($sql) use ($prefix) {
// 簡易替換 CREATE TABLE `table`、INSERT INTO `table` 等
return preg_replace_callback(
'/\b(CREATE|ALTER|DROP|TRUNCATE|INSERT|UPDATE|DELETE|REPLACE)\s+(?:TABLE\s+)?(?:INTO\s+)?[`\']?([a-zA-Z0-9_]+)[`\']?/i',
fn($m) => str_replace($m[2], $prefix . $m[2], $m[0]),
$sql
);
}, $stmts);
}
}
?>
使用範例
<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4', 'user', 'pass');
$migrator = new SqlMigrator($pdo, 'app_'); // 表前綴
try {
$migrator->executeFile(__DIR__ . '/migrations/2024_01_01_init.sql');
echo "遷移成功\n";
} catch (Throwable $e) {
echo "失敗: {$e->getMessage()}\n";
}
?>
SQL 檔案範例
-- 建立用戶表
CREATE TABLE `users` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`email` VARCHAR(190) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 初始管理員
INSERT INTO `users` (`email`, `password`) VALUES
('admin@example.com', '$2y$10$...');
-- 建立索引
CREATE INDEX idx_email ON `users` (`email`);
生產環境建議
- 使用成熟遷移工具:Phinx、Doctrine Migrations、Laravel 自帶 Migrations
- 版本控管:每個遷移檔命名 `YYYY_MM_DD_HHMMSS_description.sql`
- 冪等性:支援重複執行不報錯 (IF NOT EXISTS、ON DUPLICATE KEY)
- 回滾機制:提供 `down` 方法或反向 SQL
- 鎖機制:防止多實例同時遷移