萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 執行 SQL 檔案遷移腳本:支援多語句、註解過濾、表前綴與事務

核心類別實現

<?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`);

生產環境建議

  1. 使用成熟遷移工具:Phinx、Doctrine Migrations、Laravel 自帶 Migrations
  2. 版本控管:每個遷移檔命名 `YYYY_MM_DD_HHMMSS_description.sql`
  3. 冪等性:支援重複執行不報錯 (IF NOT EXISTS、ON DUPLICATE KEY)
  4. 回滾機制:提供 `down` 方法或反向 SQL
  5. 鎖機制:防止多實例同時遷移
copyright © 萬盛學電腦網 all rights reserved