预处理语句

导学

同一条 SQL 反复执行，只有参数不同——比如批量插入不同用户、查询不同 ID 的订单。每次都把完整 SQL 发给服务器，既浪费网络带宽，又增加解析开销。预处理语句（Prepared Statement）让 SQL 模板只编译一次，后续只需发送参数，还能天然防止 SQL 注入。

定义

预处理语句：将 SQL 语句模板预先编译并缓存，执行时只需传入参数值。MySQL 支持 PREPARE、EXECUTE、DEALLOCATE PREPARE 语法，也支持客户端驱动（如 JDBC PreparedStatement）的预处理协议。

核心语法

-- 1. 定义预处理语句（使用 ? 作为占位符）
PREPARE stmt_name FROM 'SQL语句模板';

-- 2. 设置参数变量
SET @var1 = value1, @var2 = value2;

-- 3. 执行预处理语句
EXECUTE stmt_name USING @var1, @var2;

-- 4. 释放预处理语句
DEALLOCATE PREPARE stmt_name;
-- 或：DROP PREPARE stmt_name;
特性说明
占位符只能用 ?，不能用变量名直接替换
适用语句SELECT、INSERT、UPDATE、DELETE 等
不支持占位符不能用于表名、列名、SQL 关键字（如 LIMIT ? 在 5.7 中不支持）
生命周期会话级，连接断开自动释放

SQL 示例

场景一：基础预处理查询

演示数据准备：

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表中的完整数据如下：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行语句：

-- 步骤 1：准备语句模板
PREPARE emp_query FROM 'SELECT emp_name, dept, score FROM employees WHERE emp_id = ?';

-- 步骤 2：设置参数并执行
SET @target_id = 1;
EXECUTE emp_query USING @target_id;

操作后结果：

emp_namedeptscore
大翔技术部100

再次执行（换参数）：

SET @target_id = 2;
EXECUTE emp_query USING @target_id;
emp_namedeptscore
白歌技术部NULL

释放预处理语句：

DEALLOCATE PREPARE emp_query;

结果解读：

  • PREPARE 将 SQL 模板编译并缓存，后续 EXECUTE 只需传入参数
  • 两次执行复用了同一个编译结果，减少了重复解析和优化的开销
  • ? 是参数占位符，执行时用 USING @变量 替换

场景二：预处理 INSERT（批量插入不同参数）

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- 准备插入模板
PREPARE emp_insert FROM 'INSERT INTO employees (emp_name, dept, score) VALUES (?, ?, ?)';

-- 第一次执行
SET @name = '孔蓝', @dept = '产品部', @score = 88;
EXECUTE emp_insert USING @name, @dept, @score;

-- 第二次执行
SET @name = '赵鸣', @dept = '运营部', @score = 76;
EXECUTE emp_insert USING @name, @dept, @score;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL
3孔蓝产品部88
4赵鸣运营部76

释放：

DEALLOCATE PREPARE emp_insert;

结果解读：

  • 批量插入场景下，预处理语句避免了每次发送完整 SQL 文本的网络开销
  • 参数值在服务器端绑定，不会参与 SQL 解析，天然防止 SQL 注入
  • 对于高频执行的同类操作（如订单插入、日志写入），预处理性能提升明显

场景三：预处理 UPDATE（动态条件）

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- 准备更新模板
PREPARE emp_update FROM 'UPDATE employees SET score = ? WHERE emp_id = ?';

-- 给 emp_id = 2 的员工设置分数
SET @new_score = 85, @target_id = 2;
EXECUTE emp_update USING @new_score, @target_id;

-- 查看结果
SELECT emp_id, emp_name, score FROM employees WHERE emp_id = 2;

操作后结果：

emp_idemp_namescore
2白歌85

释放：

DEALLOCATE PREPARE emp_update;

结果解读：

  • 更新操作同样适合预处理，特别是参数频繁变化的场景
  • 注意占位符的顺序必须与 USING 中变量的顺序一致
  • 预处理语句的编译结果包含执行计划，如果表结构或索引发生重大变化，可能需要重新 PREPARE

场景四：占位符的限制

当前数据状态：见上文 employees 表完整数据。

执行语句（错误示例）：

-- 错误：不能用占位符代替表名
PREPARE bad_stmt FROM 'SELECT * FROM ? WHERE emp_id = 1';
-- ERROR 1064: You have an error in your SQL syntax

-- 错误：MySQL 5.7 不支持 LIMIT 用占位符
PREPARE bad_limit FROM 'SELECT * FROM employees LIMIT ?';
-- 5.7 中执行会报错，8.0 才支持

正确做法：

-- 表名必须用字符串拼接（注意 SQL 注入风险）
SET @table_name = 'employees';
SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE emp_id = ?');
PREPARE dynamic_stmt FROM @sql;
SET @id = 1;
EXECUTE dynamic_stmt USING @id;
DEALLOCATE PREPARE dynamic_stmt;

结果解读：

  • ? 占位符只能替换值（如 WHERE id = ?），不能替换标识符（表名、列名、数据库名）
  • MySQL 5.7 中 LIMIT ? 不支持预处理占位符（8.0 支持）
  • 需要动态表名/列名时，只能用字符串拼接，但必须严格校验输入，防止 SQL 注入

场景五：查看当前会话的预处理语句

执行语句：

-- 准备两个语句
PREPARE stmt1 FROM 'SELECT * FROM employees WHERE emp_id = ?';
PREPARE stmt2 FROM 'SELECT * FROM employees WHERE dept = ?';

-- 查看预处理语句列表（通过 performance_schema）
SELECT * FROM performance_schema.prepared_statements_instances\G

释放所有：

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;

结果解读：

  • performance_schema.prepared_statements_instances 记录当前服务器的预处理语句信息（5.7+）
  • 预处理语句是会话级的，断开连接后自动释放，但显式 DEALLOCATE 是良好习惯
  • 过多未释放的预处理语句会占用服务器内存

常见误区

误区正解
"预处理语句只在存储过程中有用"不是。客户端程序（Java/JDBC、Python/MySQLdb）广泛使用预处理协议，比服务器端 PREPARE 更常见。
"预处理语句一定比普通 SQL 快"不一定。只执行一次时，预处理反而多了一次网络往返（PREPARE + EXECUTE）。只有重复执行同一模板时才快。
"? 占位符可以代替任何内容"不能。只能代替值，不能代替表名、列名、关键字。动态标识符需要字符串拼接并严格校验。
"预处理语句完全防止 SQL 注入"服务器端 PREPARE ... USING 确实防止注入（参数不参与解析）。但如果是客户端拼接 SQL 后发送，仍然可能注入。
"DEALLOCATE 和 DROP PREPARE 不一样"一样。DEALLOCATE PREPARE 和 DROP PREPARE 完全等价。
"预处理语句可以跨会话使用"不能。预处理语句是会话级的，连接断开后消失，新连接需要重新 PREPARE。

面试考点

Q：预处理语句的好处？

  1. 性能：SQL 模板只编译一次，后续执行复用执行计划，减少解析开销；2. 安全：参数值不参与 SQL 解析，天然防止 SQL 注入；3. 网络：重复执行时只需发送参数，减少传输量。

Q：MySQL 预处理语句和 JDBC PreparedStatement 的区别？

MySQL 服务器端预处理用 PREPARE/EXECUTE/DEALLOCATE 语法；JDBC PreparedStatement 是客户端 API，底层可能使用服务器端预处理协议（useServerPrepStmts=true）或客户端模拟预处理（默认）。服务器端预处理更安全、性能更好。

Q：预处理语句的占位符有什么限制？

? 只能替换值（如 WHERE id = ?），不能替换表名、列名、SQL 关键字。MySQL 5.7 中 LIMIT ? 也不支持占位符（8.0 支持）。需要动态标识符时，必须用字符串拼接并严格校验输入。

Q：什么时候不适合用预处理语句？

  1. 只执行一次的 SQL（多了一次 PREPARE 开销）；2. 需要动态表名/列名的场景（占位符不支持）；3. 查询条件变化极大、无法模板化的场景（如动态拼接多个 AND/OR 条件）。

Q：预处理语句和存储过程的区别？

预处理语句是临时的 SQL 模板，无控制逻辑，会话级生命周期；存储过程是持久化的程序单元，包含流程控制（IF、LOOP 等），数据库级存储，可复用性更强。预处理适合简单重复执行，存储过程适合复杂业务逻辑封装。

小结

  • 预处理语句通过 PREPARE 编译模板、EXECUTE 传入参数、DEALLOCATE 释放资源
  • 重复执行时减少解析开销和网络传输，提升性能
  • 参数绑定天然防止 SQL 注入，是安全编程的基础
  • ? 只能替换值，不能替换表名/列名/关键字
  • 客户端驱动（JDBC、Python）的预处理更常用，服务器端语法适合脚本和存储过程内部

下一章引子：预处理语句让参数化查询更高效安全，而 SELECT ... INTO 则是将查询结果直接导出到变量或文件的另一类实用语法。