SQL Prepare

1. 定義

慕課解釋：SQL預處理（Prepare），是一種特殊的 SQL 處理方式；預處理不會直接執(zhí)行 SQL 語句，而是先將 SQL 語句編譯，生成執(zhí)行計劃，然后通過 Execute 命令攜帶 SQL 參數(shù)執(zhí)行 SQL 語句。

2. 前言

本小節(jié)，我們將一起學習 SQL Prepare。

在生產(chǎn)環(huán)境中，我們會多次執(zhí)行一條 SQL 語句，如果每次都處理該 SQL 語句，生成執(zhí)行計劃，必然會浪費一定的時間。SQL 預處理是一種特殊的 SQL 處理方式，它會預先根據(jù) SQL 語句模板來生成對應的執(zhí)行計劃，而后只需攜帶 SQL 參數(shù)便能直接執(zhí)行，提升了 SQL 執(zhí)行的性能，是一種典型的空間換時間的算法優(yōu)化。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

3. 語法

不同數(shù)據(jù)庫對于 Prepare 的支持差異較大，本小節(jié)我們將分別介紹 MySQL 和 PostgreSQL 的預處理語法及案例。

3.1 MySQL

MySQL 預處理是一組 SQL 操作的集合，它沒有固定的語法格式，但多數(shù)情況下會按照如下 3 個步驟使用。

1. 使用PREPARE指令預定義 SQL 語句模板；
2. 使用SET指令定義 SQL 參數(shù)；
3. 使用EXECUTE指令攜帶參數(shù)執(zhí)行 SQL 模板。

我們以通過id查詢用戶為例來詳細說明 Prepare 的使用。

1、按照上述的步驟，我們應先使用 Prepare 來預定義通過“id查詢用戶”的 SQL 模板，如下：

PREPARE finduserbyidstm FROM 'SELECT * FROM imooc_user WHERE id = ?';

Prepare 指令后面便是 SQL 語句模板的名稱，此處我們將模板的名稱定義為finduserbyidstm。定義名稱后，應該指定該名稱來源的 SQL 模板，即 FROM 指令后的 SQL 語句就是 finduserbyidstm 對應的 SQL 語句模板。

注意： 既然是模板，那么必然會有參數(shù)的占位符，如 MySQL 的占位符是 ?，而 PostgreSQL 的占位符則不同，它會根據(jù)參數(shù)的序列來依次定義，如第一個參數(shù)的占位符是$1，第二個參數(shù)的占位符則是$2。

2、定義好預處理 SQL 模板后，我們還需定義 SQL 參數(shù)，如下：

SET @id = 1;

SQL 定義變量，需以@來開頭，如 @id，表示變量名為 id，變量值為 1。

3、通過 EXECUTE 攜帶參數(shù)來真正地執(zhí)行 SQL：

EXECUTE finduserbyidstm USING @id;

EXECUTE 后面是已經(jīng)定義好的模板名稱 finduserbyidstm，且使用 USING 指令來指定使用到的變量參數(shù)。

執(zhí)行成功后，結(jié)果如下：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 18  |
+----+----------+-----+

3.2 PostgreSQL

PostgreSQL 預處理也是一組 SQL 操作的集合，不過它只需要兩個步驟即可完成。

1. 使用PREPARE指令預定義 SQL 語句模板；
2. 使用EXECUTE指令攜帶參數(shù)執(zhí)行 SQL 模板。

我們還是以通過id查詢用戶為例來詳細說明 Prepare 的使用。

1、 使用 PREPARE 來預定義模板：

PREPARE finduserbyidstm(int) AS SELECT * FROM imooc_user WHERE id = $1;

PostgreSQL 的模板定義更為嚴格，不僅需要指定模板名稱，還需指定參數(shù)類型，如 finduserbyidstm 模板共有一個參數(shù)，且類型為 int。模板名稱與語句之間不再使用 FROM 連接，而是使用AS，且后面直接接上 SQL 語句，不需要 ‘’ 來包裹成字符串。占位符為$1，若有第二個占位符，則應該為$2。

2、 使用 EXECUTE 執(zhí)行：

EXECUTE finduserbyidstm(1);

PostgreSQL 執(zhí)行較為簡單，不要定義變量再使用，直接在模板名稱中指定參數(shù)值即可，即 1。

執(zhí)行后的結(jié)果如下：

 id | username | age
----+----------+-----
  1 | peter    |  18

4. 實踐

預處理的語法和步驟比較復雜，接下來以一個實例來鞏固一下。

4.1 例1 預處理插入用戶

請書寫 SQL 語句，使用預處理的方式插入一個名為lucy的用戶，該用戶年齡為17。

分析：

按照上面流程和語法，依次完成即可。

語句：

整理可得語句如下：

PREPARE insertuserstm FROM 'INSERT INTO imooc_user(id,username,age) VALUES(?,?,?)';
SET @id = 6,@username='lucy',@age=17;
EXECUTE insertuserstm USING @id,@username,@age;

結(jié)果如下：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 6  | lucy     | 17  |
+----+----------+-----+

如果使用 PostgreSQL，則語句如下：

PREPARE insertuserstm(int,varchar,int) AS INSERT INTO imooc_user(id,username,age) VALUES($1,$2,$3);
EXECUTE insertuserstm(6,'lucy',17);

5. 小結(jié)

• Prepare 的使用其實十分廣泛，絕大多數(shù) ORM 框架都有 API 支持。
• Prepare 既可以提升 SQL 執(zhí)行性能，還能防止 SQL 注入引發(fā)的安全問題。
• Prepare 雖然在每個數(shù)據(jù)庫中的語法差異很大，但是一般情況下我們都不會手寫 SQL，而是使用 ORM 框架來做。