MySQL 支持一种称为服务器端预处理语句（server-side prepared statements）的工具，它提供了一种独立于 API 的方法，可以高效、安全地预处理 SQL 语句，以便重复执行。从存储编程的角度来看，预处理语句非常有趣，因为它允许我们创建动态调用 SQL。语法如下：
（1）使用 PREPARE 语句创建准备语句：
PREPARE statement_name FROM sql_text
说明：
statement_name 表示准备语句的名称
sql_text 表示准备语句的 SQL 语句，该 SQL 语句可能包含执行时必须提供的数据值占位符，这些占位符用 ? 字符表示。例如：
PREPARE insert_user FROM "INSERT INTO USER(NAME)VALUES(?)"
（2）通过 EXECUTE 语句执行创建好的准备语句：
EXECUTE statement_name [USING variable [,variable...]]
说明：
statement_name 表示准备语句的名称，即通过 PREPARE FROM 语句创建的准备语句。
variable 表示准备语句需要的变量值，必须和占位符个数相等。注意：USING 子句用于为 PREPARE 语句中指定的占位符指定值。这些值必须以用户变量（前缀为 @ 字符）的形式提供。
（3）最后，我们可以使用 DEALLOCATE 语句丢弃准备好的语句：
DEALLOCATE PREPARE statement_name
下面直接在 MySQL 命令行客户端中使用预处理来展示如何使用它。如下：
-- 定义一个准备语句，插入用户信息 mysql> PREPARE insert_user FROM "INSERT INTO USER(NAME)VALUES(?)"; Query OK, 0 rows affected (0.01 sec) Statement prepared -- 设置一个变量 @name mysql> SET @name='张三'; Query OK, 0 rows affected (0.00 sec) -- 执行准备语句，插入一条数据到用户表 mysql> EXECUTE insert_user USING @name; Query OK, 1 row affected (0.04 sec) -- 设置一个变量 @name mysql> SET @name='李四'; Query OK, 0 rows affected (0.00 sec) -- 执行准备语句，插入一条数据到用户表 mysql> EXECUTE insert_user USING @name; Query OK, 1 row affected (0.01 sec) -- 查看用户表，ID为 5 和 6 的记录就是刚刚插入的 mysql> select * from user; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Helen | | 3 | Jemo | | 4 | Bill | | 5 | 张三 | | 6 | 李四 | +----+-------+ 6 rows in set (0.06 sec)
预处理语句在存储程序中还是很有用的，因为它们允许你在存储过程中执行动态 SQL（但不能在触发器或函数中执行）。
如果 SQL 语句是在运行时构造的，那么它就是动态的。
一般来说，只有在编译时没有完成语句所需的全部信息时，才会使用动态 SQL。这通常是因为你需要来自用户或其他数据源的输入。
例如：下面示例演示了如何将动态 SQL 作为准备语句运行；
CREATE DEFINER=`root`@`localhost` PROCEDURE `execute_immediate`(IN `in_sql` varchar(4000)) BEGIN -- 设置变量 SET @tmp_sql=in_sql; -- 创建准备语句 PREPARE s1 FROM @tmp_sql; -- 执行准备语句 EXECUTE s1; -- 清理准备语句 DEALLOCATE PREPARE s1; END
调用存储过程，输出如下：
mysql> call execute_immediate('select * from user');
+----+-------+
| id | name |
+----+-------+
| 1 | Tom |
| 2 | Helen |
| 3 | Jemo |
| 4 | Bill |
| 5 | 张三 |
| 6 | 李四 |
+----+-------+
6 rows in set (0.05 sec)
Query OK, 0 rows affected (0.00 sec)注意：
（1）EXECUTE 语句不支持 INTO 子句，也不能从准备语句中定义游标。因此，准备语句的任何结果都将返回给调用程序，而不会被存储过程捕获。要捕获动态 SQL 调用返回的记录，请将它们存储到临时表中。
（2）与静态 SQL 相比，动态 SQL 更为复杂，效率也更低。
下面存储过程接受一个表名、列名、WHERE 子句和值；存储过程使用这些参数建立一个 UPDATE 语句，可以更新任何表列的值。
CREATE DEFINER=`root`@`localhost` PROCEDURE `set_col_value`(
IN `table_name` varchar(200), -- 表名
IN `column_name` varchar(200), -- 列名
IN `new_value` varchar(4000), -- 新值
IN `where_condition` varchar(1000) -- where 条件
)
BEGIN
-- 声明变量临时存放 SQL 语句
DECLARE l_sql VARCHAR(4000);
-- 设置 SQL 语句的值
SET l_sql=CONCAT_ws(' ',
' UPDATE ', table_name,
' SET ', column_name, '=', new_value,
' WHERE ', where_condition);
-- 输出 SQL 语句，便于调试
select l_sql;
-- 创建准备语句
SET @sql=l_sql;
PREPARE s1 FROM @sql;
-- 执行准备语句
EXECUTE s1;
-- 清除准备语句
DEALLOCATE PREPARE s1;
END通过调用上面存储过程，将 id=1 的用户名改为 “王五”：
-- 执行前
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | Tom |
| 2 | Helen |
| 3 | Jemo |
| 4 | Bill |
| 5 | 张三 |
| 6 | 李四 |
+----+-------+
6 rows in set (0.06 sec)
-- 调用存储过程
mysql> call set_col_value('user', 'name', '\'王五\'', 'id=1');
+------------------------------------------------+
| l_sql |
+------------------------------------------------+
| UPDATE user SET name = '王五' WHERE id=1 |
+------------------------------------------------+
1 row in set (0.09 sec)
Query OK, 0 rows affected (0.00 sec)
-- 执行后
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | 王五 |
| 2 | Helen |
| 3 | Jemo |
| 4 | Bill |
| 5 | 张三 |
| 6 | 李四 |
+----+-------+
6 rows in set (0.09 sec)动态 SQL 的另一个常见应用是建立有条件的 WHERE 子句。通常，我们在构建用户界面时，用户可以指定多个搜索条件。如果不使用动态 SQL 来处理 “缺失” 条件，就会导致 SQL 复杂而笨拙，MySQL 也很难对其进行优化。
例如：该存储过程允许用户指定姓名、年龄、邮件和电话号码的任意组合来查询用户信息。
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_query`( IN `in_name` varchar(50), IN `in_age` int, IN `in_email` varchar(255), IN `in_phone` varchar(50) ) BEGIN -- 根据用户条件查询用户信息 SELECT * FROM user WHERE (name LIKE in_name OR in_name IS NULL) AND (age=in_age OR in_age IS NULL) AND (email like in_email OR in_email IS NULL) AND (phone LIKE in_phone OR in_phone IS NULL); END
调用存储过程，输出如下：
mysql> call user_query('%e%', NULL, '%.com%', '158%');
+----+-------+-----+-----+-------------------+-------------+
| id | name | age | sex | email | phone |
+----+-------+-----+-----+-------------------+-------------+
| 2 | Helen | 26 | 女 | helen@outlook.com | 15814402560 |
+----+-------+-----+-----+-------------------+-------------+
1 row in set (0.07 sec)
Query OK, 0 rows affected (0.00 sec)上面示例的 SQL 语句还没有复杂到难以忍受的地步，但随着候选搜索条件的增加，该语句的可维护性将迅速降低。因此，我们可以根据用户提供的搜索条件动态构造 WHERE 子句，并使用准备语句动态调用 SQL。例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_query`(IN `in_name` varchar(50),IN `in_age` int,IN `in_email` varchar(255),`in_phone` varchar(50))
BEGIN
-- 声明变量，用来存储查询条件
DECLARE l_where_clause VARCHAR(1000) DEFAULT 'WHERE';
-- 用户查询条件
IF in_name IS NOT NULL THEN
SET l_where_clause=CONCAT(l_where_clause, ' name like "',in_name,'"');
END IF;
-- 年龄查询条件
IF in_age IS NOT NULL THEN
IF l_where_clause<>'WHERE' THEN
SET l_where_clause=CONCAT(l_where_clause,' AND ');
END IF;
SET l_where_clause=CONCAT(l_where_clause, ' age=',in_age,'');
END IF;
-- 邮件查询条件
IF in_email IS NOT NULL THEN
IF l_where_clause<>'WHERE' THEN
SET l_where_clause=CONCAT(l_where_clause,' AND ');
END IF;
SET l_where_clause=CONCAT(l_where_clause, ' email like "',in_email,'"');
END IF;
-- 电话查询条件
IF in_phone IS NOT NULL THEN
IF l_where_clause<>'WHERE' THEN
SET l_where_clause=CONCAT(l_where_clause,' AND ');
END IF;
SET l_where_clause=CONCAT(l_where_clause, ' phone like "',in_phone,'"');
END IF;
-- 拼接最终 SQL 语句
SET @sql=CONCAT('SELECT * FROM user ', l_where_clause);
-- 创建准备语句
PREPARE s1 FROM @sql;
-- 执行准备语句
EXECUTE s1;
-- 清除准备语句
DEALLOCATE PREPARE s1;
END调用存储过程，输出如下：
mysql> call user_query('%e%', NULL, '%.com%', '158%');
+----+-------+-----+-----+-------------------+-------------+
| id | name | age | sex | email | phone |
+----+-------+-----+-----+-------------------+-------------+
| 2 | Helen | 26 | 女 | helen@outlook.com | 15814402560 |
+----+-------+-----+-----+-------------------+-------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.00 sec)上面示例比前面示例代码更长、更复杂，但它的执行速度可能更快，因为我们从最终执行的 SQL 中删除了多余的 WHERE 子句。这样，我们就为 MySQL 提供了更好的数据，使其可以根据这些数据来决定索引和其他优化。
注意：虽然动态 SQL 能够完成非常复杂的工作，但是，当你根据用户输入构建 SQL 时，可能会出现 SQL 注入安全漏洞。
川公网安备51010802032098
