CakePHP 透過 ConnectionManager 管理資料庫連線,每個 DataSource 內建查詢日誌功能。本文提供一段可直接植入 Controller/Component/Shell 的代碼片段,印出當前請求執行的所有 SQL 語句,含耗時、影響行數、參數綁定,極大提升除錯效率。
<?php
// 可放在 Controller action、Component、Shell、View 任意位置
$sources = ConnectionManager::sourceList();
if (!isset($logs)) {
$logs = [];
foreach ($sources as $source) {
$db =& ConnectionManager::getDataSource($source);
if (!$db->isInterfaceSupported('getLog')) {
continue;
}
$logs[$source] = $db->getLog();
}
}
// 輸出格式化
echo '<pre>';
foreach ($logs as $sourceName => $log) {
echo "<strong>DataSource: $sourceName</strong>\n";
echo "總查詢數: " . count($log['log']) . "\n";
echo "總耗時: " . array_sum(array_column($log['log'], 'took')) . " ms\n\n";
foreach ($log['log'] as $i => $query) {
echo "--- Query #" . ($i + 1) . " ---\n";
echo "SQL: " . $query['query'] . "\n";
if (!empty($query['params'])) {
echo "Params: " . json_encode($query['params'], JSON_UNESCAPED_UNICODE) . "\n";
}
echo "Took: {$query['took']} ms\n";
echo "Rows: {$query['numRows']}\n\n";
}
}
echo '</pre>';
?>
DataSource: default
總查詢數: 3
總耗時: 12.4 ms
--- Query #1 ---
SQL: SELECT * FROM users WHERE email = ? AND status = 1
Params: ["admin@example.com"]
Took: 4.2 ms
Rows: 1
--- Query #2 ---
SQL: SELECT * FROM roles WHERE id IN (?,?)
Params: [1,2]
Took: 3.1 ms
Rows: 2
--- Query #3 ---
SQL: UPDATE users SET last_login = NOW() WHERE id = ?
Params: [42]
Took: 5.1 ms
Rows: 1
<?php
namespace App\Utility;
trait DebugSqlTrait {
public function dumpSqlLogs(bool $exit = false): void {
$sources = ConnectionManager::sourceList();
$output = '';
foreach ($sources as $source) {
$db = ConnectionManager::getDataSource($source);
if (!$db->isInterfaceSupported('getLog')) continue;
$log = $db->getLog();
$output .= "\n=== DataSource: $source ===\n";
$output .= "Queries: " . count($log['log']) . " | Total Time: " . array_sum(array_column($log['log'], 'took')) . " ms\n";
foreach ($log['log'] as $i => $q) {
$output .= "\n#" . ($i+1) . " [{$q['took']} ms] {$q['query']}\n";
if ($q['params']) $output .= " Params: " . json_encode($q['params'], JSON_UNESCAPED_UNICODE) . "\n";
}
}
if (php_sapi_name() === 'cli') {
echo $output;
} else {
echo '<pre style="background:#1e1e1e;color:#d4d4d4;padding:10px;font-size:12px;line-height:1.4">' . htmlspecialchars($output) . '</pre>';
}
if ($exit) exit;
}
}
// 在 Controller 使用
class ArticlesController extends AppController {
use DebugSqlTrait;
public function index() {
$this->paginate();
$this->dumpSqlLogs(); // 輸出本請求所有 SQL
}
}
?>
ConnectionManager::get()、$db->logQueries(true) 啟用日誌ConnectionManager::get() 回傳 ConnectionInterface,使用 $conn->getLog()getLog() 取得結構化陣列if (Configure::read('debug')) { $this->dumpSqlLogs(); }善用內建查詢日誌,能快速定位 N+1 問題、慢查詢、參數綁定錯誤,是 CakePHP 開發者的必備技能。