MySQL Prepared语句

一、MySQL Prepared语句介绍

在MySQL4.1 版本之前，每个查询都以文本格式发送到 MySQL 服务器，并使用文本协议将数据返回给客户端。在将结果返回给客户端之前，MySQL 已经完全解析了查询并将结果集转换为字符串。在这里，解析意味着提交的查询在语法和语义上都经过了验证，并且权限也经过了验证。

将数据返回给客户端的文本协议存在严重的性能问题。为了克服这个问题，MySQL 从 4.1 版本开始提供了一个新的特性，称为准备好的语句。

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

mysql> SELECT * FROM student WHERE studentId = ?;

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

Prepared的语句包含占位符（？），这有助于避免许多SQL注入变体并使我们的应用程序更安全。

二、MySQL Prepared语句优点

以下是 MySQL 中Prepared语句的优点：

我们可以重复多次执行Prepared语句。
每次执行时，都会评估绑定变量的当前值并将其发送到服务器。该语句不再被解析。报表模板不会再次传输到服务器。

三、MySQL Prepared语句工作流程

Prepared语句的基本工作流程主要包括两个阶段。但是，它有一个可选阶段，总结如下：

PREPARE
EXECUTE
DEALLOCATE （可选）

1）PREPARE 准备阶段

在PREPARE 准备阶段，一个语句模板被发送到数据库服务器。服务器执行语法检查并初始化内部服务器资源以供以后使用。简而言之，它为执行准备了一条语句。

PREPARE stmt_name FROM preparable_stmt;

2）EXECUTE 执行阶段

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

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

3）DEALLOCATE 释放阶段

它是最后一个可选阶段，用于释放Prepared语句。

{DEALLOCATE | DROP} PREPARE stmt_name;

四、MySQL Prepared语句要点

在一个会话中创建的Prepared语句不适用于其他会话。这意味着Prepared语句是特定于会话的。
当会话结束时，无论是正常结束还是异常结束，其Prepared语句都不再存在于内存中。
在存储程序中创建的prepared statement在程序执行完毕后继续存在，以后可以在程序外执行。

五、MySQL Prepared语句示例

让我们通过一些例子来了解如何使用 MySQL Prepared Statement。

在这里，我们将在 PREPARE 语句的帮助下直接创建一个语句，如下所示：

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

接下来，我们将这两个值分配给两个可用于占位符 (?) 的变量：

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

现在，我们可以在 EXECUTE 语句的帮助下执行SQL：

mysql> EXECUTE stmt1 USING @a, @b;

执行后，我们将得到sum的结果。请参阅下图以了解结果：

以下示例将使用示例数据库中包含以下数据的employee员工表。

首先，我们将准备一个语句，返回由员工id指定的员工姓名和职务：

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

接下来，我们需要声明一个名为 id 的变量并将其值设置为“1”：

mysql> SET @id = 1;

现在，我们可以在 EXECUTE 语句的帮助下执行准备好的语句：

mysql> EXECUTE stmt1 USING @id;

执行后，我们将得到包含员工姓名和职务的结果。请参阅下图以了解SQL执行：

同样，我们将为变量 id 分配另一个值：

mysql> SET @id = 3;

现在，使用新员工 ID 执行Prepared语句。我们将看到如下输出：

最后，我们可以手动释放Prepared语句。但是，当会话关闭时，它们将被自动删除。

mysql> DEALLOCATE PREPARE stmt1;

如果我们在执行完上面的查询后尝试执行prepared statement，我们会得到如下错误：

六、我们如何在存储过程中使用Prepared语句？

我们可以通过将Prepared语句写入BEGIN和END块中来在存储过程中使用它。我们可以通过创建一个示例来理解它，该示例通过将表的名称作为存储过程的参数传递来返回表中的所有记录。

创建存储过程如下：

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> CALL tbl_detail('employee');

它将显示该表的所有记录。见下图：

七、Statement 和Prepared Statement 区别

以下是 MySQL 中的 statement 和prepared statement 的主要区别：

Statement	Prepared Statement
当我们只想执行一次 SQL 查询时使用它。	当我们想要多次执行 SQL 查询时使用它。
Statement用于 DDL 语句。	Prepared Statement可以用于任何 SQL 查询。
Statement不能用于读写二进制数据。	Prepared Statement可以用来读写二进制数据。
Statement是静态的，这意味着我们不能在运行时传递参数。	Prepared Statement是动态的，这意味着我们可以在运行时传递参数。
执行的性能很慢。	执行速度很快。
Statement不会阻止 SQL 注入。	Prepared Statement有助于防止 SQL 注入攻击。
Statement使用文本协议进行通信。	Prepared Statement使用二进制协议进行通信。

八、存储过程与Prepared语句区别

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

MySQL 获取行数 MySQL 字面量

MySQL Prepared语句

一、MySQL Prepared语句 介绍

二、MySQL Prepared语句 优点

三、MySQL Prepared语句 工作流程