MySQL：Prepared Statement 预处理语句

预处理语句（Prepared Statement） 是一种在数据库管理系统中使用的编程概念，用于执行对数据库进行操作的 SQL 语句。

使用预处理语句的具体方式和语法依赖于所用的编程语言和数据库管理系统。常见的编程语言如 Java、PHP、Python 和 C# 都提供了对预处理语句的支持。

在MySQL版本4.1之前，查询以文本格式发送到MySQL服务器。反过来，MySQL使用 文本协议（text protocol） 将数据返回给客户端。MySQL必须完全解析查询并将结果集转换为字符串，然后再将其返回给客户端。

文本协议具有严重的性能影响。为解决此问题，自4.1版本后，MySQL添加了一项新特性—— prepared statement

prepared statement 利用 client/server binary protocol ，它将包含占位符（?）的查询语句发送到MySQL服务器：

SELECT column_list FROM products
WHERE productcode = ?;
1
2

当 MySQL 使用不同的 productcode 值 执行此查询时，它不必完全解析查询。这有助于MySQL更快地执行查询，提升程序的性能，特别是当MySQL多次执行相同的查询时。

由于预处理语句使用占位符(?)，这有助于避免许多SQL注入的变体，使应用程序更安全。

预处理语句的基本思想是将 SQL 语句和参数分离开来。在执行之前，数据库管理系统会先解析并编译预处理语句，然后将其保存在预处理语句的执行计划中。

当应用程序需要执行相同或类似的 SQL 语句时，只需要对参数进行修改，而不必重新解析和编译整个 SQL 语句，从而提高执行效率。

使用步骤

使用预处理语句通常需要以下几个步骤：

1. 准备预处理语句：应用程序先将 SQL 语句发送给数据库管理系统，并告知数据库要使用预处理语句的方式进行处理。数据库会进行语法分析和执行计划的生成，生成一个可执行的预处理语句对象。

2. 绑定参数：应用程序将要执行的 SQL 语句中的参数值绑定到预处理语句中。这样可以防止 SQL 注入攻击，并且可以使数据库系统充分利用参数值进行查询优化。

3. 执行预处理语句：应用程序将绑定了参数的预处理语句发送给数据库进行执行。数据库会根据预处理语句中的执行计划执行相应的操作，并返回结果给应用程序。

用法

使用 预处理语句 需要如下三个语句：

1. PREPARE：为执行准备一条语句

2. EXECUTE：执行PREPARE语句准备好的语句

3. DEALLOCATE PREPARE：发布准备好的声明

-- 示例
'''step 1'''
PREPARE stmt1 FROM 
	'SELECT 
   	    productCode, 
            productName 
	FROM products
        WHERE productCode = ?';

'''step 2 
   声明一个变量 pc，代表productcode并为其赋值'''
SET @pc = 'S10_1678';

'''step 3
   EXECUTE'''
EXECUTE stmt1 USING @pc;

'''step 4
   为变量 pc 分配另一个product code'''
SET @pc = 'S12_1099';

'''step 5
   EXECUTE with the new product code'''
   EXECUTE stmt1 USING @pc;

'''step 6
   release the prepared statement'''
   DEALLOCATE PREPARE stmt1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

优点

• 提高性能：预处理语句的执行计划可以被多次重复使用，避免了重复解析和编译 SQL 语句的开销，提高了执行效率。

• 预防 SQL 注入攻击：通过参数绑定，预处理语句可以防止恶意用户通过输入特殊字符来修改 SQL 逻辑，提升了应用程序的安全性。

• 方便维护：将 SQL 逻辑和参数分开，使代码清晰易读，易于维护和修改。