遇到 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