PHP 使用 Mysqli 的 prepare 語句有什麼好處？

---

《High Performance MySQL》 一書的 225 -228 頁。講的非常詳細，我簡單整理下：

好處：

(1) Parse the query only once

(2) Perform some query optimization steps only once

(3) Sending parameters via the binary protocol is more efficient than sending them as ASCII text

比如 DATE，對於 Prepare 之後，發送 Date 只用 3 Bytes；如果沒有 Prepare， DATE 必須以 String 的形式發送，需要資料庫方再解析，這樣需要發送 10 Bytes。

(4) Only the parameters (not the entire query text) need to be sent for each execution

(5) MySQL stores the parameteres directly into buffers on the server

(6) Also helps with security, there is no need to escape or quote values.

壞處：

(1) Local to a connection, so another connection cannot re-use

(2) Cannot use MySQL query cache (5.1 版本之前)

(3) Not always more efficient, if you use it only once

(4) Cannot use inside a stored function (Stored procedure 是可以的)

(5) May lead to "leak" if you forget to deallocate it

基本上直接打原文，部分地方稍微做了下改變，以及適當的地方加了補充。

---

1:安全方面,sql注入這類問題

2:傳輸體積優化[參數傳遞]

3:避免重複解析,

---

推薦閱讀：