MySQL 预编译语句

MySQL 预编译语句

在MySQL版本4.1之前,每个查询都以文本格式发送到MySQL服务器,并使用文本协议返回数据给客户端。在将结果返回给客户端之前,MySQL会对查询进行完全解析,并将结果集转换为一个字符串。这里的解析意味着提交的查询被句法和语义验证,并且权限也被验证。

返回数据给客户端的文本协议存在严重性能问题。为了解决这个问题,MySQL从4.1版本开始提供了一个叫做预编译语句的新功能。

预编译语句或参数化语句用于以高效率重复执行相同的语句 。它利用了客户端/服务器二进制协议。预编译语句将包含 占位符(?) 的查询传递给MySQL服务器。请参考以下示例:

mysql> SELECT * FROM student WHERE studentId = ?; 

当MySQL使用不同的 studentId 的值执行上述语句时,它无法完全解析该语句。结果是,尤其是在执行相同查询多次时,MySQL将更快地执行该语句。

预编译语句含有占位符(?),它有助于避免许多SQL注入的变体,使我们的应用程序更安全。

预编译语句的优势

MySQL中预编译语句的优势如下:

  • 我们可以多次重复执行预编译语句。
  • 在每次执行中,绑定变量的当前值被计算并发送到服务器。语句不会被重新解析,语句模板也不会再次传输到服务器。

预编译语句的基本工作流程

预编译语句的基本工作流程主要包括两个阶段。不过,它还有一个可选阶段,总结如下:

  1. 准备
  2. 执行
  3. 释放(可选)

准备阶段

在准备阶段,将 语句模板发送到数据库服务器 。服务器执行语法检查并初始化内部服务器资源以供后续使用。简而言之,它为执行预编译语句做准备。

语法

以下是准备阶段的语法:

PREPARE stmt_name FROM preparable_stmt;

执行阶段

在执行阶段,客户端绑定参数值并将其发送到服务器。服务器使用语句模板和绑定的值创建一个语句,使用之前创建的内部资源执行它。简而言之,一旦准备好了查询,我们就可以执行该查询了。

语法

以下是执行准备好的语句的语法:

EXECUTE stmt_name [USING @var_name [, @var_name]....]

释放/删除阶段

这是最后一个可选的阶段,用于释放准备好的语句。

语法

以下是释放准备好的语句的语法:

{DEALLOCATE | DROP} PREPARE stmt_name;

与预编译语句相关的关键要点

  • 在一个会话中创建的预编译语句对其他会话不可用。这意味着预编译语句是针对会话的。
  • 当一个会话结束时,无论是正常结束还是异常结束,其预编译语句将不再存在于内存中。
  • 在存储程序中创建的预编译语句在程序执行完成后仍然存在,并且可以在程序外部执行。

MySQL预编译语句示例

让我们通过一些例子来了解如何使用MySQL预编译语句。

在这里,我们将直接使用PREPARE语句创建一个语句,如下所示:

mysql> PREPARE stmt1 FROM 'SELECT ?+? AS SUM';

接下来,我们将把这两个值分配给两个可以用作占位符(?)的变量:

mysql> SET @a = 20;
mysql> SET @b = 30;

现在,我们可以借助EXECUTE语句来执行查询操作:

mysql> EXECUTE stmt1 USING @a, @b;

执行后,我们将得到一个结果,即 。请参考下面的图片了解结果:

MySQL 预编译语句

下面的示例将使用样本数据库中的 员工 表,该表包含以下数据。

MySQL 预编译语句

首先,我们将准备一个返回员工 姓名职位 的语句,该职位由员工 编号 指定:

mysql> PREPARE stmt1 FROM 
'SELECT Name, Designation FROM employee 
WHERE Emp_id = ?';

接下来,我们需要声明一个名为id的变量,并将其值设置为’1′:

mysql> SET @id = 1;

现在,我们可以通过EXECUTE语句执行准备好的语句:

mysql> EXECUTE stmt1 USING @id;

执行后,我们将获得一个包含员工姓名和职位的结果。请参考下面的图像以了解查询的执行情况:

MySQL 预编译语句

再次,我们将为变量id指定另一个值:

mysql> SET @id = 3;

现在,使用新的员工ID执行准备好的语句。我们将会看到以下输出:

MySQL 预编译语句

最后,我们可以手动释放准备的语句。然而,在会话关闭时它们会被自动移除。

mysql> DEALLOCATE PREPARE stmt1;

如果我们在执行以上查询后尝试执行准备的语句,将会得到以下错误:

MySQL 预编译语句

如何在存储过程中使用预编译语句?

我们可以通过在 BEGINEND 块中编写预编译语句来在存储过程中使用它。我们可以通过创建一个例子来理解,该例子通过将表名作为存储过程的参数来返回表中的所有记录。

创建存储过程如下:

DELIMITER CREATE PROCEDURE tbl_detail(tab_name Varchar(40))  
BEGIN  
SET @A:= CONCAT('Select * from',' ',tab_name);
Prepare stmt FROM @A;
EXECUTE stmt;
END  
DELIMITER ;  

查看下面的图像来执行存储过程:

MySQL 预编译语句

创建成功后,我们可以通过将表名指定为其参数来调用此过程。

mysql> CALL tbl_detail('employee');

这将显示表中的所有记录。请参见下方图片:

MySQL 预编译语句

语句 vs. 预处理语句

以下是MySQL中语句和预处理语句之间的主要区别:

声明(Statement) 准备声明(Prepared Statement)
当我们只想执行SQL查询一次时使用。 当我们想多次执行SQL查询时使用。
用于DDL语句。 可用于任何SQL查询。
不能用于读写二进制数据。 可用于读写二进制数据。
是静态的,意味着我们无法在运行时传递参数。 是动态的,意味着我们可以在运行时传递参数。
执行性能较慢。 执行性能较快。
无法防止SQL注入。 有助于防止SQL注入攻击。
使用文本协议进行通信。 使用二进制协议进行通信。

存储过程 vs. 预编译语句

以下是MySQL中存储过程和预编译语句的主要区别:

存储过程 预编译语句
存储过程是一系列访问关系数据库管理系统的SQL语句。 预编译语句是包含占位符而非实际值的查询。
它可以存储在数据库服务器中。 它不能存储在数据库中。

Camera课程

Python教程

Java教程

Web教程

数据库教程

图形图像教程

办公软件教程

Linux教程

计算机教程

大数据教程

开发工具教程