34 SQL Prepare

优质

小牛编辑

133浏览

2023-12-01

1. 定义

慕课解释：SQL预处理（Prepare），是一种特殊的 SQL 处理方式；预处理不会直接执行 SQL 语句，而是先将 SQL 语句编译，生成执行计划，然后通过 Execute 命令携带 SQL 参数执行 SQL 语句。

2. 前言

本小节，我们将一起学习 SQL Prepare。

在生产环境中，我们会多次执行一条 SQL 语句，如果每次都处理该 SQL 语句，生成执行计划，必然会浪费一定的时间。SQL 预处理是一种特殊的 SQL 处理方式，它会预先根据 SQL 语句模板来生成对应的执行计划，而后只需携带 SQL 参数便能直接执行，提升了 SQL 执行的性能，是一种典型的空间换时间的算法优化。

本小节测试数据如下，请先在数据库中执行：

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. 语法

不同数据库对于 Prepare 的支持差异较大，本小节我们将分别介绍 MySQL 和 PostgreSQL 的预处理语法及案例。

3.1 MySQL

MySQL 预处理是一组 SQL 操作的集合，它没有固定的语法格式，但多数情况下会按照如下 3 个步骤使用。

使用PREPARE指令预定义 SQL 语句模板；
使用SET指令定义 SQL 参数；
使用EXECUTE指令携带参数执行 SQL 模板。

我们以通过id查询用户为例来详细说明 Prepare 的使用。

1、按照上述的步骤，我们应先使用 Prepare 来预定义通过“id查询用户”的 SQL 模板，如下：

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

Prepare 指令后面便是 SQL 语句模板的名称，此处我们将模板的名称定义为finduserbyidstm。定义名称后，应该指定该名称来源的 SQL 模板，即 FROM 指令后的 SQL 语句就是 finduserbyidstm 对应的 SQL 语句模板。

注意： 既然是模板，那么必然会有参数的占位符，如 MySQL 的占位符是 ?，而 PostgreSQL 的占位符则不同，它会根据参数的序列来依次定义，如第一个参数的占位符是$1，第二个参数的占位符则是$2。

2、定义好预处理 SQL 模板后，我们还需定义 SQL 参数，如下：

SET @id = 1;

SQL 定义变量，需以@来开头，如 @id，表示变量名为 id，变量值为 1。

3、通过 EXECUTE 携带参数来真正地执行 SQL：

EXECUTE finduserbyidstm USING @id;

EXECUTE 后面是已经定义好的模板名称 finduserbyidstm，且使用 USING 指令来指定使用到的变量参数。

执行成功后，结果如下：

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

3.2 PostgreSQL

PostgreSQL 预处理也是一组 SQL 操作的集合，不过它只需要两个步骤即可完成。

使用PREPARE指令预定义 SQL 语句模板；
使用EXECUTE指令携带参数执行 SQL 模板。

我们还是以通过id查询用户为例来详细说明 Prepare 的使用。

1、使用 PREPARE 来预定义模板：

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

PostgreSQL 的模板定义更为严格，不仅需要指定模板名称，还需指定参数类型，如 finduserbyidstm 模板共有一个参数，且类型为 int。模板名称与语句之间不再使用 FROM 连接，而是使用AS，且后面直接接上 SQL 语句，不需要 ‘’ 来包裹成字符串。占位符为$1，若有第二个占位符，则应该为$2。

2、使用 EXECUTE 执行：

EXECUTE finduserbyidstm(1);

PostgreSQL 执行较为简单，不要定义变量再使用，直接在模板名称中指定参数值即可，即 1。

执行后的结果如下：

 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;

结果如下：

+----+----------+-----+
| 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. 小结

Prepare 的使用其实十分广泛，绝大多数 ORM 框架都有 API 支持。
Prepare 既可以提升 SQL 执行性能，还能防止 SQL 注入引发的安全问题。
Prepare 虽然在每个数据库中的语法差异很大，但是一般情况下我们都不会手写 SQL，而是使用 ORM 框架来做。