您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP 執行 SQL 檔案完整實作：解析、去註釋、表前綴替換與批次匯入

編輯：PHP 相關知識

需求背景

專案部署或資料庫遷移時，常需將 .sql 腳本（如 phpMyAdmin 匯出檔）直接在 PHP 中執行。本文提供一個健壯的 DBManager 類別，支援：

讀取大型 SQL 檔案（二進位模式避免編碼問題）
按分號 ; 分割多條 SQL 語句
自動移除單行註釋（#、--）與空白行
支援表名稱加前綴（適用於多租戶或測試環境）
使用 mysql 擴展逐條執行（舊版專案；新專案建議改用 PDO/MySQLi）

核心類別程式碼

<?php
class DBManager {
    private $dbHost, $dbUser, $dbPassword, $dbSchema;

    public function __construct($host, $user, $password, $schema) {
        $this->dbHost     = $host;
        $this->dbUser     = $user;
        $this->dbPassword = $password;
        $this->dbSchema   = $schema;
    }

    /**
     * 從 SQL 檔案建立資料庫結構與資料
     * @param string $sqlPath      SQL 檔案路徑
     * @param string $delimiter    語句分隔符正則（預設針對 ;\n ;\r\n ;\r）
     * @param string $prefix       表名稱前綴（空字串則不加）
     * @param array  $commenters   註釋起始字元陣列
     * @return bool
     */
    public function createFromFile($sqlPath, $delimiter = '(;/n)|((;/r/n))|(;/r)', $prefix = '', $commenters = ['#', '--']) {
        if (!file_exists($sqlPath)) return false;

        $handle  = fopen($sqlPath, 'rb');
        $sqlStr  = fread($handle, filesize($sqlPath));
        fclose($handle);

        // 1. 依分號分割語句
        $segments = explode(';', trim($sqlStr));

        // 2. 去除註釋與空行
        foreach ($segments as &$stmtLines) {
            $lines = explode("\n", $stmtLines);
            $clean = [];
            foreach ($lines as $line) {
                $trimmed = trim($line);
                if ($trimmed === '') continue;
                $isComment = false;
                foreach ($commenters as $c) {
                    if (stripos($trimmed, $c) === 0) { $isComment = true; break; }
                }
                if (!$isComment) $clean[] = $trimmed;
            }
            $stmtLines = $clean;
        }

        // 3. 表名稱加前綴（僅處理 CREATE TABLE `tbl` 與 INSERT INTO `tbl`）
        if ($prefix !== '') {
            $regTable = '^[`\'"]{0,1}[_a-zA-Z]+[_a-zA-Z0-9]*[`\'"]{0,1}$';
            $regLeft  = '^[`\'"]{1}';
            $flagTree = [
                'CREATE' => ['TABLE' => [$regTable => 0]],
                'INSERT' => ['INTO'  => [$regTable => 0]],
            ];
            foreach ($segments as &$stmtLines) {
                if (empty($stmtLines)) continue;
                $tokens = preg_split('/\s+/', $stmtLines[0]);
                $tableInfo = ['name' => '', 'leftWall' => ''];
                $this->findTableName($flagTree, $tokens, 0, $tableInfo);
                if (!empty($tableInfo['name'])) {
                    $newName = empty($tableInfo['leftWall'])
                        ? $prefix . $tableInfo['name']
                        : $tableInfo['leftWall'] . $prefix . substr($tableInfo['name'], 1);
                    $stmtLines[0] = str_replace($tableInfo['name'], $newName, $stmtLines[0]);
                }
            }
        }

        // 4. 重組 SQL 語句
        $finalSql = [];
        foreach ($segments as $stmtLines) {
            if (empty($stmtLines)) continue;
            $finalSql[] = implode("\n", $stmtLines) . ';';
        }

        // 5. 批次執行
        return $this->saveByQuery($finalSql);
    }

    private function findTableName($tree, $tokens, $idx, &$tableInfo) {
        if ($idx >= count($tokens)) return false;
        $token = trim($tokens[$idx]);
        if ($token === '') return $this->findTableName($tree, $tokens, $idx + 1, $tableInfo);

        foreach ($tree as $flag => $sub) {
            if (stripos($token, $flag) === 0) {
                if (is_int($sub) && $sub === 0) { // leaf regex
                    $tableInfo['name'] = $token;
                    if (preg_match('/^[' . $regLeft . ']/', $token)) {
                        $tableInfo['leftWall'] = $token[0];
                    }
                    return true;
                } elseif (is_array($sub)) {
                    return $this->findTableName($sub, $tokens, $idx + 1, $tableInfo);
                }
            }
        }
        return false;
    }

    private function saveByQuery(array $sqlArray) {
        $conn = mysql_connect($this->dbHost, $this->dbUser, $this->dbPassword);
        if (!$conn) throw new Exception('MySQL 連線失敗：' . mysql_error());
        mysql_select_db($this->dbSchema, $conn);
        mysql_set_charset('utf8', $conn);

        foreach ($sqlArray as $sql) {
            $sql = trim($sql);
            if ($sql === '') continue;
            if (!mysql_query($sql, $conn)) {
                throw new Exception('SQL 執行失敗：' . mysql_error($conn) . "\nSQL: $sql");
            }
        }
        mysql_close($conn);
        return true;
    }
}

// 使用範例
$db = new DBManager('localhost', 'db_user', 'db_pass', 'my_database');
$db->createFromFile('data.sql', null, 'app_'); // 所有表名加上 app_ 前綴
?>

適用場景與注意事項

phpMyAdmin 匯出檔：本類別專為標準 phpMyAdmin SQL Dump 格式設計，包含 CREATE TABLE、INSERT INTO、單行註釋等。
大檔案效能：fread() 一次讀入整個檔案，超大檔（>100MB）建議改用流式逐行解析或 LOAD DATA INFILE。
編碼一致性：確保 SQL 檔案、資料庫連線、PHP 腳本皆為 UTF-8，避免中文亂碼。
擴展棄用警告：mysql_* 函數自 PHP 5.5 棄用、PHP 7.0 移除。新專案請改寫為 PDO 或 MySQLi（支援預處理、事務、更佳錯誤處理）。
分隔符限制：若 SQL 字串內含分號（如存儲過程、Trigger），簡單 explode(';') 會誤切。生產環境建議使用完整 SQL 解析器（如 php-sql-parser）。

測試用 data.sql 片段

-- phpMyAdmin SQL Dump
CREATE TABLE `category` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '分類ID',
  `name` varchar(255) NOT NULL COMMENT '分類名稱',
  PRIMARY KEY (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `category` (`cid`, `name`) VALUES
(1, '音樂'), (2, '電影'), (3, '程式設計');