萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

CakePHP 印出 SQL 語句除錯技巧:ConnectionManager 獲取查詢日誌

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

進階封裝:可重用的 DebugSql Trait

<?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
    }
}
?>

CakePHP 3.x/4.x 版本差異提醒

  • 3.x:ConnectionManager::get()、$db->logQueries(true) 啟用日誌
  • 4.x:ConnectionManager::get() 回傳 ConnectionInterface,使用 $conn->getLog()
  • 所有版本核心概念一致:DataSource 內部收集查詢,getLog() 取得結構化陣列

生產環境安全注意

  1. 僅開發環境啟用:if (Configure::read('debug')) { $this->dumpSqlLogs(); }
  2. 敏感資料遮罩:密碼、Token 等參數在輸出前脫敏
  3. 效能影響:日誌收集有微小開銷,生產環境務必關閉

善用內建查詢日誌,能快速定位 N+1 問題、慢查詢、參數綁定錯誤,是 CakePHP 開發者的必備技能。

copyright © 萬盛學電腦網 all rights reserved