PHP 實現 XML 與 MySQL 資料雙向轉換：MySQL2XML 類完整實作

類別設計目標

• 支援指定資料表備份為 XML 檔案
• XML 結構包含欄位定義 (型別、鍵、預設值) 與完整資料
• 可擴充支援還原 (XML → MySQL)

MySQL2XML 類別核心程式碼

<?php
class MySQL2XML {
    protected $conn;
    protected $result;
    protected $tables = [];
    protected $saveFolder = 'datas/';
    
    public function __construct($config = null) {
        if ($config && is_array($config)) {
            $this->connect($config);
        }
    }
    
    public function connect($config) {
        $this->conn = mysql_connect($config['host'], $config['username'], $config['password']);
        if ($this->conn) {
            mysql_select_db($config['database']);
            return true;
        }
        return false;
    }
    
    public function setSaveFolder($folder) {
        if (is_dir($folder)) {
            $this->saveFolder = rtrim(str_replace('\\', '/', $folder), '/');
            return true;
        }
        return false;
    }
    
    public function setTables(array $tables) {
        $this->tables = $tables;
        return true;
    }
    
    public function query($sql) {
        if (empty(trim($sql))) return false;
        $this->result = mysql_query($sql);
        return (bool)$this->result;
    }
    
    public function toXML() {
        if (empty($this->tables)) return false;
        foreach ($this->tables as $table) {
            $file = $this->saveFolder . $table . '.xml';
            $fp = @fopen($file, 'w');
            if (!$fp) exit("無法寫入檔案: $file");
            fwrite($fp, $this->tableToXML($table));
            fclose($fp);
        }
        return true;
    }
    
    public function tableToXML($table) {
        header("Content-Type: text/xml; charset=utf-8");
        $xml = '<?xml version="1.0" encoding="utf-8"?>' . "\n<datas>\n";
        
        $fields = $this->getFields($table);
        $datas = $this->getDatas($table);
        
        // 重組資料為欄位導向結構
        $cdata = [];
        foreach ($datas as $row) {
            foreach ($row as $key => $value) {
                $cdata[$key][] = $value;
            }
        }
        
        foreach ($fields as $field) {
            $xml .= "\t<fields name=\"{$field['Field']}\" "
                  . "type=\"{$field['Type']}\" "
                  . "null=\"{$field['Null']}\" "
                  . "key=\"{$field['Key']}\" "
                  . "default=\"{$field['Default']}\" "
                  . "extra=\"{$field['Extra']}\">\n";
            
            foreach ($cdata[$field['Field']] as $value) {
                $xml .= "\t\t<data><![CDATA[$value]]></data>\n";
            }
            $xml .= "\t</fields>\n";
        }
        $xml .= '</datas>';
        return $xml;
    }
    
    protected function getFields($table) {
        $this->query("SHOW FIELDS FROM `{$table}`");
        return $this->fetchAll();
    }
    
    protected function getDatas($table) {
        $this->query("SELECT * FROM `{$table}`");
        return $this->fetchAll();
    }
    
    protected function fetch() {
        return is_resource($this->result) ? mysql_fetch_assoc($this->result) : false;
    }
    
    protected function fetchAll() {
        if (!is_resource($this->result)) return false;
        $rows = [];
        while ($row = mysql_fetch_assoc($this->result)) {
            $rows[] = $row;
        }
        return $rows;
    }
}
?>

使用範例

<?php
$xml = new MySQL2XML([
    'host' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'mysql'
]);

$xml->setTables(['wp_term_relationships', 'wp_terms']);
$xml->setSaveFolder('datas/');
$xml->toXML(); // 開始備份
echo "備份完成！";
?>

生成 XML 結構範例

<?xml version="1.0" encoding="utf-8"?>
<datas>
    <fields name="term_id" type="bigint(20) unsigned" null="NO" key="PRI" default="" extra="auto_increment">
        <data><![CDATA[1]]></data>
        <data><![CDATA[2]]></data>
    </fields>
    <fields name="name" type="varchar(200)" null="NO" key="" default="" extra="">
        <data><![CDATA[分類一]]></data>
        <data><![CDATA[標籤二]]></data>
    </fields>
    ...
</datas>

擴充方向建議

1. 還原功能：解析 XML → 執行 CREATE TABLE → 批次 INSERT
2. 改用 mysqli/PDO：原 mysql_* 函數已棄用
3. 大資料分批：加入 LIMIT 分頁讀取，避免記憶體耗盡
4. 壓縮備份：輸出前用 gzencode() 壓縮為 .xml.gz
5. 異步處理：大表備份放入佇列 (Redis/RabbitMQ) 背景執行