如何通过 mysqli_stmt::$insert_id 获取最后插入记录的 ID 并进行后续查询操作？

gitbox 2025-08-04

在使用 PHP 进行数据库操作时，尤其是在使用 MySQLi 的预处理语句（mysqli_stmt）插入数据后，我们经常需要获取刚刚插入的记录的自增 ID，以便进行后续的查询、更新或关联操作。本文将详细介绍如何通过 mysqli_stmt::$insert_id 获取最后插入的 ID，并演示如何使用该 ID 进行后续查询。

一、什么是 mysqli_stmt::$insert_id

mysqli_stmt::$insert_id 是 mysqli_stmt 对象的一个属性，用于获取通过该预处理语句插入的记录的自增主键 ID。需要注意的是，这个属性只有在执行的是 INSERT 操作并且数据表中设置了 AUTO_INCREMENT 字段时才会返回有效的 ID。

二、使用示例

下面是一个完整的示例，演示如何插入一条记录并获取插入后的 ID，然后使用这个 ID 查询刚刚插入的数据。

<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">"localhost"</span></span><span>, </span><span><span class="hljs-string">"username"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"test_db"</span></span><span>);

</span><span><span class="hljs-comment">// 检查连接</span></span><span>
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_errno) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">"连接失败: "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error);
}

</span><span><span class="hljs-comment">// 插入数据的 SQL</span></span><span>
</span><span><span class="hljs-variable">$insert_sql</span></span><span> = </span><span><span class="hljs-string">"INSERT INTO users (username, email) VALUES (?, ?)"</span></span><span>;
</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">$insert_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">die</span></span><span>(</span><span><span class="hljs-string">"预处理失败: "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;error);
}

</span><span><span class="hljs-comment">// 绑定参数并执行插入</span></span><span>
</span><span><span class="hljs-variable">$username</span></span><span> = </span><span><span class="hljs-string">'john_doe'</span></span><span>;
</span><span><span class="hljs-variable">$email</span></span><span> = </span><span><span class="hljs-string">'[email protected]'</span></span><span>;
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"ss"</span></span><span>, </span><span><span class="hljs-variable">$username</span></span><span>, </span><span><span class="hljs-variable">$email</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-comment">// 获取插入后的 ID</span></span><span>
</span><span><span class="hljs-variable">$inserted_id</span></span><span> = </span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;insert_id;
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"插入成功，ID 为: "</span></span><span> . </span><span><span class="hljs-variable">$inserted_id</span></span><span> . </span><span><span class="hljs-string">"\n"</span></span><span>;

</span><span><span class="hljs-comment">// 关闭插入语句</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();

</span><span><span class="hljs-comment">// 使用插入的 ID 进行查询</span></span><span>
</span><span><span class="hljs-variable">$select_sql</span></span><span> = </span><span><span class="hljs-string">"SELECT * FROM users WHERE id = ?"</span></span><span>;
</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">$select_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">die</span></span><span>(</span><span><span class="hljs-string">"查询语句预处理失败: "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;error);
}

</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$inserted_id</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-variable">$result</span></span><span> = </span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">get_result</span></span><span>();

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$row</span></span><span> = </span><span><span class="hljs-variable">$result</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">fetch_assoc</span></span><span>()) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"查询结果:\n"</span></span><span>;
    </span><span><span class="hljs-title function_ invoke__">print_r</span></span><span>(</span><span><span class="hljs-variable">$row</span></span><span>);
} </span><span><span class="hljs-keyword">else</span></span><span> {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"未找到对应记录。\n"</span></span><span>;
}

</span><span><span class="hljs-comment">// 关闭资源</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span><span><span class="hljs-meta">?&gt;</span></span><span>
</span></span>

三、注意事项

确保使用的是同一个数据库连接
如果插入和查询使用了不同的连接，insert_id 将无法正确反映插入的 ID。
表必须包含 AUTO_INCREMENT 字段
否则即使插入成功，insert_id 也不会返回有效值。
insert_id 是语句对象的属性，不是连接对象的
使用 prepare 后的 $stmt 才能获取 $stmt->insert_id，否则应使用 $mysqli->insert_id。

四、应用场景

在插入订单、用户、文章等记录后获取主键 ID，用于建立一对多或多对多关联。
实现用户提交数据后的自动跳转或反馈操作。
日志或审计系统中记录操作结果的唯一标识。

五、总结

通过 mysqli_stmt::$insert_id，我们可以非常方便地获取最后插入的记录 ID，从而实现更复杂的数据操作逻辑。在实际开发中，这种方式有助于保证数据的准确性与一致性，也是使用 MySQLi 进行安全数据库操作的推荐做法。

相关标签:
mysqli_stmt