如何通過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>

三、注意事項

確保使用的是同一個數據庫連接<br> 如果插入和查詢使用了不同的連接， insert_id將無法正確反映插入的ID
表必須包含AUTO_INCREMENT字段<br> 否則即使插入成功， insert_id也不會返回有效值
insert_id是語句對象的屬性，不是連接對象的<br> 使用prepare後的$stmt才能獲取$stmt->insert_i d ，否則應使用$mysqli->insert_i d

四、應用場景

在插入訂單、用戶、文章等記錄後獲取主鍵ID，用於建立一對多或多對多關聯。
實現用戶提交數據後的自動跳轉或反饋操作。
日誌或審計系統中記錄操作結果的唯一標識。

五、總結

通過mysqli_stmt::$insert_id ，我們可以非常方便地獲取最後插入的記錄ID，從而實現更複雜的數據操作邏輯。在實際開發中，這種方式有助於保證數據的準確性與一致性，也是使用MySQLi 進行安全數據庫操作的推薦做法。

相關標籤:
mysqli_stmt