遇到mysqli_stmt::$error_list 中出現多重錯誤時，應該如何有效處理？

gitbox 2025-08-22

在使用PHP 的MySQLi 擴展執行預處理語句（prepared statements）時， mysqli_stmt::$error_list是一個極其重要的調試工具。它提供了一個包含所有錯誤信息的數組，而不是像$stmt->error那樣只返回最後一個錯誤。當一次執行或準備過程中同時觸發多個錯誤時，這個屬性就顯得尤為關鍵。

然而，開發者在面對$stmt->error_list返回的多個錯誤時，常常陷入迷惑：到底應該如何有效提取、處理和展示這些錯誤信息，才能既準確定位問題，又不會讓錯誤處理邏輯變得混亂？

本文將從以下幾個方面，探討多重錯誤處理的策略：

一、理解error_list的結構

$stmt->error_list返回的是一個數組，每個元素是一個關聯數組，包含以下鍵：

errno ：錯誤碼（整數）
sqlstate ：SQLSTATE 錯誤標識符（字符串）
error ：錯誤信息（字符串）

一個典型的結構如下：

 <span><span>[
    [
        </span><span><span class="hljs-string">'errno'</span></span><span> =&gt; </span><span><span class="hljs-number">1064</span></span><span>,
        </span><span><span class="hljs-string">'sqlstate'</span></span><span> =&gt; </span><span><span class="hljs-string">'42000'</span></span><span>,
        </span><span><span class="hljs-string">'error'</span></span><span> =&gt; </span><span><span class="hljs-string">'You have an error in your SQL syntax; check the manual...'</span></span><span>
    ],
    [
        </span><span><span class="hljs-string">'errno'</span></span><span> =&gt; </span><span><span class="hljs-number">1048</span></span><span>,
        </span><span><span class="hljs-string">'sqlstate'</span></span><span> =&gt; </span><span><span class="hljs-string">'23000'</span></span><span>,
        </span><span><span class="hljs-string">'error'</span></span><span> =&gt; </span><span><span class="hljs-string">'Column \'name\' cannot be null'</span></span><span>
    ]
]
</span></span>

這意味著你需要對每一個錯誤分別判斷和處理，而不是假設只有一個錯誤。

二、統一封裝錯誤輸出邏輯

為了避免在每個SQL 執行處都重複處理邏輯，建議將錯誤處理封裝成一個函數，例如：

 <span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">handle_stmt_errors</span></span><span>(</span><span><span class="hljs-params">mysqli_stmt <span class="hljs-variable">$stmt</span></span></span><span>) {
    </span><span><span class="hljs-keyword">foreach</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;error_list </span><span><span class="hljs-keyword">as</span></span><span> </span><span><span class="hljs-variable">$error</span></span><span>) {
        </span><span><span class="hljs-title function_ invoke__">error_log</span></span><span>(</span><span><span class="hljs-string">"[SQL Error] Code: <span class="hljs-subst">{$error['errno']}</span></span></span><span> | SQLSTATE: </span><span><span class="hljs-subst">{$error['sqlstate']}</span></span><span> | Message: </span><span><span class="hljs-subst">{$error['error']}</span></span><span>");
    }
}
</span></span>

這樣你可以在每次execute()或prepare()失敗時，簡單地調用：

 <span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">execute</span></span><span>()) {
    </span><span><span class="hljs-title function_ invoke__">handle_stmt_errors</span></span><span>(</span><span><span class="hljs-variable">$stmt</span></span><span>);
}
</span></span>

三、按嚴重性進行錯誤分類

不是所有錯誤都同等重要。建議根據errno分類，比如：

邏輯錯誤（如數據為空、唯一鍵衝突） ：可向用戶反饋，或記錄為業務異常。
語法錯誤或數據庫故障（如SQL 拼寫錯誤、連接丟失） ：應立即報警或中止請求。

你可以用一個簡單的映射表判斷嚴重性：

 <span><span><span class="hljs-variable">$criticalErrors</span></span><span> = [</span><span><span class="hljs-number">1064</span></span><span>, </span><span><span class="hljs-number">2006</span></span><span>, </span><span><span class="hljs-number">2013</span></span><span>];

</span><span><span class="hljs-keyword">foreach</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;error_list </span><span><span class="hljs-keyword">as</span></span><span> </span><span><span class="hljs-variable">$error</span></span><span>) {
    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-title function_ invoke__">in_array</span></span><span>(</span><span><span class="hljs-variable">$error</span></span><span>[</span><span><span class="hljs-string">'errno'</span></span><span>], </span><span><span class="hljs-variable">$criticalErrors</span></span><span>)) {
        </span><span><span class="hljs-title function_ invoke__">trigger_error</span></span><span>(</span><span><span class="hljs-string">"嚴重數據庫錯誤: <span class="hljs-subst">{$error['error']}</span></span></span><span>", E_USER_ERROR);
    } </span><span><span class="hljs-keyword">else</span></span><span> {
        </span><span><span class="hljs-title function_ invoke__">error_log</span></span><span>(</span><span><span class="hljs-string">"普通錯誤: <span class="hljs-subst">{$error['error']}</span></span></span><span>");
    }
}
</span></span>

四、避免漏檢prepare() 錯誤

開發者容易忽略的一點是， prepare()失敗同樣可以產生多個錯誤，不能只靠返回值為false 來判斷，需要同樣調用error_list ：

 <span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-variable">$sql</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$stmt</span></span><span>) {
    </span><span><span class="hljs-keyword">foreach</span></span><span> (</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;error_list </span><span><span class="hljs-keyword">as</span></span><span> </span><span><span class="hljs-variable">$error</span></span><span>) {
        </span><span><span class="hljs-title function_ invoke__">error_log</span></span><span>(</span><span><span class="hljs-string">"Prepare Error: <span class="hljs-subst">{$error['error']}</span></span></span><span>");
    }
}
</span></span>

五、在開發環境中啟用詳細日誌

在開發階段建議將錯誤日誌輸出到瀏覽器或開發控制台，而在生產環境中應只記錄日誌而不顯示：

 <span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">log_stmt_errors</span></span><span>(</span><span><span class="hljs-params">mysqli_stmt <span class="hljs-variable">$stmt</span></span></span><span>, </span><span><span class="hljs-variable">$display</span></span><span> = </span><span><span class="hljs-literal">false</span></span><span>) {
    </span><span><span class="hljs-keyword">foreach</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;error_list </span><span><span class="hljs-keyword">as</span></span><span> </span><span><span class="hljs-variable">$error</span></span><span>) {
        </span><span><span class="hljs-variable">$message</span></span><span> = </span><span><span class="hljs-string">"[Error <span class="hljs-subst">{$error['errno']}</span></span></span><span>] </span><span><span class="hljs-subst">{$error['error']}</span></span><span> (SQLSTATE: </span><span><span class="hljs-subst">{$error['sqlstate']}</span></span><span>)";
        </span><span><span class="hljs-title function_ invoke__">error_log</span></span><span>(</span><span><span class="hljs-variable">$message</span></span><span>);
        </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$display</span></span><span>) {
            </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"&lt;div class=\"sql-error\"&gt;<span class="hljs-subst">$message</span></span></span><span>&lt;/div&gt;";
        }
    }
}
</span></span>

六、結語

mysqli_stmt::$error_list是一個低調但功能強大的錯誤排查工具。當你在進行複雜的SQL 操作、綁定大量參數，或連接多個數據表時，錯誤往往不止一個。正確地解析並分類處理這些錯誤，不僅有助於快速修復bug，也能提升系統的健壯性。

別再只看$stmt->error了——完整地掌握error_list ，你會發現調試SQL 的效率能提高不止一個層級。

相關標籤:
mysqli_stmt

mysqli_stmt::$error_list