SQL Prepare
1. 定义
编程之家解释:预处理
(Prepare),是一种特殊的 处理方式;预处理不会直接执行 语句,而是先将 语句编译,执行计划,然后通过 Execute 命令携带 参数执行 语句。
2. 前言
本小节,我们将一起学习 Prepare
。
在生产环境中,我们会多次执行一条 语句,如果每次都处理该 语句,执行计划,必然会浪费一定的时间。 预处理是一种特殊的 处理方式,它会预先根据 语句模板来对应的执行计划,而后只需携带 参数便能直接执行,提升了 执行的,是一种典型的空间换时算法优化。
本小节测试数据如下,请先在中执行:
DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), age int);INSERT INTO imooc_user(id,username,age)VALUES (,'peter',),(,'pedro',),(,'jerry',),(,'mike',),(,'tom',);
3. 语法
不同对于 Prepare 的差异较大,本小节我们将分别介绍 和 Postgre 的预处理语法及案例。
预处理是一组 操作的集合,它没有固定的语法格式,但多数情况下会按照如下 3 个步骤使用。
使用PREPARE
指令预定义 语句模板;
使用SET
指令定义 参数;
使用EXECUTE
指令携带参数执行 模板。
我们以通过id
为例来详细说明 Prepare 的使用。
1、按照上述的步骤,我们应先使用 Prepare 来预定义通过“id”的 模板,如下:
PREPARE finduserbyidstm FROM 'SELECT * FROM imooc_user WHERE id = ?';
Prepare 指令后面便是 语句模板的,此处我们将模板的定义为finduserbyidstm
。定义后,应该指定该来源的 模板,即 FROM 指令后的 语句就是 finduserbyidstm 对应的 语句模板。
注意: 既然是模板,那么必然会有参数的占位符,如 的占位符是 ?
,而 Postgre 的占位符则不同,它会根据参数的序列来依次定义,如第参数的占位符是$1
,第二个参数的占位符则是$2
。
2、定义好预处理 模板后,我们还需定义 参数,如下:
SET @id = ;
定义变量,需以@
来开头,如 @id,表示变量名为 id,变量值为 1。
3、通过 EXECUTE 携带参数来真正地执行 :
EXECUTE finduserbyidstm USING @id;
EXECUTE 后面是已经定义好的模板 finduserbyidstm,且使用 USING 指令来指定使用到的变量参数。
执行成功后,结果如下:
+----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | peter | 18 | +----+----------+-----+
Postgre 预处理也是一组 操作的集合,不过它只需要两个步骤即可完成。
使用PREPARE
指令预定义 语句模板;
使用EXECUTE
指令携带参数执行 模板。
我们还是以通过id
为例来详细说明 Prepare 的使用。
1、 使用 PREPARE 来预定义模板:
PREPARE finduserbyidstm(int) AS SELECT * FROM imooc_user WHERE id = $;
Postgre 的模板定义更为严格,不仅需要指定模板,还需指定参数类型,如 finduserbyidstm 模板共有参数,且类型为 int。模板与语句之间不再使用 FROM 连接,而是使用AS
,且后面直接接上 语句,不需要 ‘’ 来包裹成字符串。占位符为$1
,若有第二个占位符,则应该为$2
。
2、 使用 EXECUTE 执行:
EXECUTE finduserbyidstm();
Postgre 执行较为简单,不要定义变量再使用,直接在模板中指定参数值即可,即 1。
执行后的结果如下:
id | username | age ----+----------+----- 1 | peter | 18
4. 实践
预处理的语法和步骤比较复杂,接下来以实例来巩固一下。
请书写 语句,使用预处理的方式插入名为lucy
的,该年龄为17
。
分析:
按照上面流程和语法,依次完成即可。
语句:
整理可得语句如下:
PREPARE insertuserstm FROM 'INSERT INTO imooc_user(id,username,age) VALUES(?,?,?)';SET @id = ,@username='lucy',@age=;EXECUTE insertuserstm USING @id,@username,@age;
结果如下:
+----+----------+-----+ | id | username | age | +----+----------+-----+ | 6 | lucy | 17 | +----+----------+-----+
如果使用 Postgre,则语句如下:
PREPARE insertuserstm(int,varchar,int) AS INSERT INTO imooc_user(id,username,age) VALUES($,$,$);EXECUTE insertuserstm(,'lucy',);
5. 小结
Prepare 的使用其实十分广泛,绝大多数 ORM 框架都有 API 。
Prepare 既可以提升 执行,还能防止 注入引发的安全问题。
Prepare 虽然在每个中的语法差异很大,但是一般情况下我们都不会手写 ,而是使用 ORM 框架来做。