ThinkPHP 框架本身未內建數據庫備份導出功能。本文提供一個基於 PDO 的 SqlAction 控制器方法,可導出指定數據庫的結構與數據為標準 SQL 文件,支援字符集、排序規則、觸發器/視圖/存儲過程(可選),並提供下載響應。
<?php
namespace app\admin\controller;
use think\Controller;
use PDO;
use PDOException;
class Sql extends Controller {
// 導出入口
public function outsql() {
header("Content-Type: text/html; charset=utf-8");
// 1. 讀取數據庫配置
$host = config('database.hostname');
$dbname = config('database.database');
$user = config('database.username');
$password = config('database.password');
$prefix = config('database.prefix') ?? '';
// 2. 執行導出
$sql = $this->outputSql($host, $dbname, $user, $password, $prefix, 1);
// 3. 下載響應
$filename = $dbname . '_' . date('Ymd_His') . '.sql';
header("Content-Disposition: attachment; filename=\"{$filename}\"");
header('Content-Type: application/sql; charset=utf-8');
echo $sql;
exit;
}
/**
* 導出數據庫 SQL
* @param string $host
* @param string $dbname
* @param string $user
* @param string $password
* @param string $prefix 表前綴(可選,僅導出該前綴表)
* @param int $flag 0=僅結構, 1=結構+數據
* @return string SQL 內容
*/
private function outputSql($host, $dbname, $user, $password, $prefix = '', $flag = 1) {
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $user, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, // 大結果集緩衝
]);
} catch (PDOException $e) {
return "-- 連接失敗: " . $e->getMessage() . "\n";
}
$sql = "";
// 0. 設置字符集
$sql .= "SET NAMES utf8mb4;\n";
$sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
// 1. 獲取數據庫創建語句(含字符集、排序規則)
$stmt = $pdo->query("SHOW CREATE DATABASE `$dbname`");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row && isset($row['Create Database'])) {
// 提取字符集和排序規則
preg_match('/DEFAULT CHARACTER SET\s+(\S+)/i', $row['Create Database'], $mCharset);
preg_match('/COLLATE\s+(\S+)/i', $row['Create Database'], $mCollate);
$charset = $mCharset[1] ?? 'utf8mb4';
$collate = $mCollate[1] ?? 'utf8mb4_general_ci';
$sql .= "DROP DATABASE IF EXISTS `$dbname`;\n";
$sql .= "CREATE DATABASE `$dbname` DEFAULT CHARACTER SET $charset COLLATE $collate;\n";
$sql .= "USE `$dbname`;\n\n";
}
// 2. 獲取所有表名(可按前綴過濾)
$tables = [];
$stmt = $pdo->query("SHOW TABLES");
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$table = $row[0];
if ($prefix === '' || strpos($table, $prefix) === 0) {
$tables[] = $table;
}
}
// 3. 遍歷每張表導出結構和數據
foreach ($tables as $table) {
// 3.1 表結構
$stmt = $pdo->query("SHOW CREATE TABLE `$table`");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$sql .= "DROP TABLE IF EXISTS `$table`;\n";
$sql .= $row['Create Table'] . ";\n\n";
// 3.2 表數據
if ($flag == 1) {
$stmt = $pdo->query("SELECT * FROM `$table`");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$firstRow = true;
$columns = [];
$batchSize = 500; // 批量 INSERT 行數
$batchRows = [];
while ($row = $stmt->fetch()) {
if ($firstRow) {
$columns = array_keys($row);
$firstRow = false;
}
// 轉義值
$values = array_map(function($v) use ($pdo) {
if ($v === null) return 'NULL';
if (is_int($v) || is_float($v)) return $v;
return $pdo->quote($v); // 含引號轉義
}, array_values($row));
$batchRows[] = '(' . implode(',', $values) . ')';
if (count($batchRows) >= $batchSize) {
$sql .= "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) VALUES\n";
$sql .= implode(",\n", $batchRows) . ";\n\n";
$batchRows = [];
}
}
// 最後一批
if ($batchRows) {
$sql .= "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) VALUES\n";
$sql .= implode(",\n", $batchRows) . ";\n\n";
}
}
}
$sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
return $sql;
}
}
// route.php
Route::get('admin/sql/outsql', 'admin/Sql/outsql');
後台管理頁面放一個鏈接:<a href="/admin/sql/outsql">備份數據庫 (SQL)</a>,點擊直接下載 dbname_20240115_143000.sql。
PDO::quote() 正確處理字符串、NULL、數字。gzencode($sql, 9) 輸出 .sql.gz,節省 70%+ 帶寬。SHOW CREATE VIEW/TRIGGER/PROCEDURE。PDO::quote() 依賴當前連接字符集,確保連接時已 SET NAMES utf8mb4。max_execution_time,需 set_time_limit(0) 或異步化。此功能填補了 ThinkPHP 官方缺失的數據庫備份能力,代碼精簡、依賴少,可直接集成至現有後台管理模塊。