應用場景
- 跨系統數據交換 (ERP ↔ CRM、老系統遷移)
- 結構化備份 (可讀、可驗證、版本控制友好)
- 離線分析、報表生成、數據歸檔
核心類：MySQL2XML (PDO 版、支援大表分批)
<?php
declare(strict_types=1);
class MySQL2XML {
private PDO $pdo;
private string $outputDir;
private int $batchSize = 1000;
private bool $includeSchema = true;
private array $tables = [];
public function __construct(PDO $pdo, string $outputDir = __DIR__ . '/xml_backups') {
$this->pdo = $pdo;
$this->outputDir = rtrim($outputDir, '/');
if (!is_dir($this->outputDir)) mkdir($this->outputDir, 0755, true);
}
public function setTables(array $tables): self { $this->tables = $tables; return $this; }
public function setBatchSize(int $size): self { $this->batchSize = $size; return $this; }
public function setIncludeSchema(bool $flag): self { $this->includeSchema = $flag; return $this; }
/** 執行導出 */
public function export(): array {
$tables = $this->tables ?: $this->getAllTables();
$exported = [];
foreach ($tables as $table) {
$file = "{$this->outputDir}/{$table}.xml";
$this->exportTable($table, $file);
$exported[] = $file;
}
return $exported;
}
/** 導出單表 */
private function exportTable(string $table, string $filepath): void {
$xml = new XMLWriter();
$xml->openUri($filepath);
$xml->setIndent(true);
$xml->setIndentString(' ');
$xml->startDocument('1.0', 'UTF-8');
$xml->startElement('table');
$xml->writeAttribute('name', $table);
$xml->writeAttribute('exported_at', date('c'));
// 1. 結構
if ($this->includeSchema) {
$xml->startElement('structure');
$cols = $this->pdo->query("SHOW COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
foreach ($cols as $col) {
$xml->startElement('column');
foreach ($col as $k => $v) {
$xml->writeAttribute($k, (string)$v);
}
$xml->endElement(); // column
}
$xml->endElement(); // structure
}
// 2. 數據分批寫入
$xml->startElement('data');
$total = (int)$this->pdo->query("SELECT COUNT(*) FROM `{$table}`")->fetchColumn();
$colNames = array_column($cols, 'Field');
for ($offset = 0; $offset < $total; $offset += $this->batchSize) {
$rows = $this->pdo->query(
"SELECT * FROM `{$table}` LIMIT {$this->batchSize} OFFSET {$offset}"
)->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
$xml->startElement('row');
foreach ($colNames as $col) {
$xml->startElement('field');
$xml->writeAttribute('name', $col);
$value = $row[$col];
if ($value === null) {
$xml->writeAttribute('null', 'true');
} else {
$xml->text((string)$value);
}
$xml->endElement(); // field
}
$xml->endElement(); // row
}
}
$xml->endElement(); // data
$xml->endElement(); // table
$xml->flush();
}
private function getAllTables(): array {
return $this->pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
}
}
/** XML2MySQL 導入類 */
class XML2MySQL {
private PDO $pdo;
private int $batchSize = 500;
public function __construct(PDO $pdo) { $this->pdo = $pdo; }
public function setBatchSize(int $size): self { $this->batchSize = $size; return $this; }
public function import(string $xmlFile, bool $truncateFirst = true): int {
$reader = new XMLReader();
$reader->open($xmlFile);
$tableName = '';
$inData = false;
$rowCount = 0;
$batch = [];
$columns = [];
// 先解析結構獲取表名與欄位
while ($reader->read()) {
if ($reader->nodeType === XMLReader::ELEMENT && $reader->name === 'table') {
$tableName = $reader->getAttribute('name');
break;
}
}
if (!$tableName) throw new \RuntimeException('Invalid XML: no table name');
// 獲取欄位順序
$reader->close();
$reader->open($xmlFile);
while ($reader->read()) {
if ($reader->nodeType === XMLReader::ELEMENT && $reader->name === 'column') {
$columns[] = $reader->getAttribute('Field');
}
if ($reader->nodeType === XMLReader::ELEMENT && $reader->name === 'data') {
break;
}
}
if ($truncateFirst) {
$this->pdo->exec("TRUNCATE TABLE `{$tableName}`");
}
$placeholders = implode(',', array_fill(0, count($columns), '?'));
$colList = '`' . implode('`,`', $columns) . '`';
$sql = "INSERT INTO `{$tableName}` ({$colList}) VALUES ({$placeholders})";
$stmt = $this->pdo->prepare($sql);
$this->pdo->beginTransaction();
while ($reader->read()) {
if ($reader->nodeType === XMLReader::ELEMENT && $reader->name === 'row') {
$rowData = [];
// 讀取 row 內容
$reader->read(); // 移到第一個 field
while ($reader->name !== 'row' || $reader->nodeType !== XMLReader::END_ELEMENT) {
if ($reader->nodeType === XMLReader::ELEMENT && $reader->name === 'field') {
$isNull = $reader->getAttribute('null') === 'true';
$reader->read(); // 移到 text 節點
$value = $isNull ? null : $reader->value;
$rowData[] = $value;
}
$reader->read();
}
$batch[] = $rowData;
$rowCount++;
if (count($batch) >= $this->batchSize) {
foreach ($batch as $row) $stmt->execute($row);
$batch = [];
}
}
}
if ($batch) foreach ($batch as $row) $stmt->execute($row);
$this->pdo->commit();
$reader->close();
return $rowCount;
}
}
使用示例
<?php
// 導出
$pdo = new PDO('mysql:host=127.0.0.1;dbname=myapp', 'user', 'pass');
$exporter = new MySQL2XML($pdo, '/backups/xml');
$exporter->setTables(['users', 'orders', 'products'])
->setBatchSize(2000)
->export();
echo "導出完成\n";
// 導入
$importer = new XML2MySQL($pdo);
$count = $importer->import('/backups/xml/users.xml', true);
echo "導入 {$count} 筆\n";
生成 XML 結構示例
<?xml version="1.0" encoding="UTF-8"?>
<table name="users" exported_at="2024-01-15T14:30:00+08:00">
<structure>
<column Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment"/>
<column Field="name" Type="varchar(100)" Null="NO" Key="" Default="" Extra=""/>
<column Field="email" Type="varchar(150)" Null="YES" Key="UNI" Default="" Extra=""/>
</structure>
<data>
<row>
<field name="id">1</field>
<field name="name">Alice</field>
<field name="email">alice@example.com</field>
</row>
<row>
<field name="id">2</field>
<field name="name">Bob</field>
<field name="email" null="true"/>
</row>
</data>
</table>
大數據量優化策略
- 分表導出：單表單獨文件，便於並行導入、增量同步。
- 壓縮傳輸：
gzip users.xml 傳輸，導入時 gzopen 讀取。
- 並行導入：多進程分表導入，或使用
LOAD XML LOCAL INFILE (MySQL 8.0+)。
- 類型保真：XML 無原生類型，導入時需根據結構欄位類型轉換 (INT、DATE、DECIMAL 等)。
替代方案對比
