13.5 预备 Statements

• 13 .5.1 PREPARE 语句

• 13 .5.2 EXECUTE 语句

• 13 .5.3 DEALLOCATE PREPARE 语句

MySQL 5.7 支持服务器端预处理语句。这种支持利用了有效的 Client 端/服务器二进制协议。对参数值使用带占位符的预处理语句具有以下好处：

• 每次执行语句时解析语句的开销都较小。通常，数据库应用程序处理大量几乎相同的语句，仅对子句中的 Literals 或变量值进行更改，例如对查询和删除使用WHERE，对更新使用SET以及对插入使用VALUES。

• 防止 SQL 注入攻击。参数值可以包含未转义的 SQL 引号和定界符。

应用程序中的准备好的语句

您可以通过 Client 端编程接口使用服务器端准备好的语句，包括MySQL C APIClient 端库table 示 C 程序，MySQL Connector/Jtable 示 Java 程序，MySQL Connector/NETtable 示使用.NET 技术的程序。例如，C API 提供了一组构成其准备好的语句 API 的函数调用。参见第 27.7.7 节“ C API 准备语句”。其他语言接口可以通过在 CClient 端库中进行链接来为使用二进制协议的准备好的语句提供支持，一个示例是mysqli extension，在 PHP 5.0 及更高版本中可用。

SQL 脚本中的预备语句

提供了准备好的语句的替代 SQL 接口。此接口的效率不如通过预准备的语句 API 使用二进制协议有效，但不需要编程，因为它可以直接在 SQL 级别使用：

• 如果没有可用的编程接口，则可以使用它。

• 您可以从任何可以将 SQL 语句发送到要执行的服务器的程序中使用它，例如mysqlClient 端程序。

• 即使 Client 端使用旧版本的 Client 端库，也可以使用它，只要您连接到运行 MySQL 4.1 或更高版本的服务器即可。

准备语句的 SQL 语法旨在用于以下情况：

• 在对预编写语句进行编码之前，要测试它们在您的应用程序中如何工作。

• 在无权访问支持它们的编程 API 时使用准备好的语句。

• 使用准备好的语句以交互方式解决应用程序问题。

• 创建一个测试案例，该测试案例用准备好的语句重现问题，以便您可以提交错误报告。

PREPARE，EXECUTE 和 DEALLOCATE PREPARE 语句

预准备语句的 SQL 语法基于以下三个 SQL 语句：

• PREPARE准备执行语句(请参阅第 13.5.1 节“ PREPARE 语句”)。

• EXECUTE执行一条准备好的语句(请参见第 13.5.2 节“ EXECUTE 语句”)。

• DEALLOCATE PREPARE发布准备好的语句(请参见第 13.5.3 节“ DEALLOCATE PREPARE 语句”)。

下面的示例显示两种等效的方式来准备一条语句，该语句在给定两侧的长度的情况下计算三角形的斜边。

第一个示例显示了如何通过使用字符串 Literals 来提供语句文本来创建准备好的语句：

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

第二个示例类似，但是将语句的文本作为用户变量提供：

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

这是另一个示例，该示例通过将 table 的名称存储为用户变量来演示如何选择要在运行时在其上执行查询的 table：

mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a  |
+----+
|  4 |
|  8 |
| 11 |
| 32 |
| 80 |
+----+

mysql> DEALLOCATE PREPARE stmt3;

准备好的语句特定于创建它的会话。如果在不取消分配先前准备好的语句的情况下终止了会话，则服务器会自动取消分配它。

准备好的声明对于会议也是全局的。如果在存储的例程中创建一个准备好的语句，则在存储的例程结束时不会释放该语句。

为了防止同时创建太多准备好的语句，请设置max_prepared_stmt_count系统变量。为了防止使用准备好的语句，请将其值设置为 0.

预准备语句中允许的 SQL 语法

以下 SQL 语句可以用作准备好的语句：

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

从 MySQL 5.7.2 开始，为了符合 SQL 标准，该标准规定诊断语句不可准备，MySQL 不支持以下已准备好的语句：

• SHOW WARNINGS , SHOW COUNT(*) WARNINGS

• SHOW ERRORS , SHOW COUNT(*) ERRORS

• 包含对warning_count或error_count系统变量的任何引用的语句。

MySQL 5.7 不支持其他语句。

通常，SQL 预准备语句中不允许的语句在存储程序中也不允许。 第 23.8 节“对存储程序的限制”中记录了异常。

检测到已准备好的语句所引用的 table 或视图的元数据更改，并在下一次执行该语句时导致该语句的自动重新准备。有关更多信息，请参见第 8.10.4 节“准备好的语句和存储程序的缓存”。

使用准备好的语句时，可以将占位符用作LIMIT子句的参数。参见第 13.2.9 节“ SELECT 语句”。

在与PREPARE和EXECUTE一起使用的已准备好的CALL语句中，从 MySQL 5.7 开始提供对OUT和INOUT参数的占位符支持。请参见第 13.2.1 节“ CALL 语句”，以获取示例和早期版本的解决方法。不论版本如何，都可以将占位符用于IN参数。

预准备语句的 SQL 语法不能以嵌套方式使用。也就是说，传递给PREPARE的语句本身不能是PREPARE，EXECUTE或DEALLOCATE PREPARE语句。

准备语句的 SQL 语法与使用准备语句 API 调用不同。例如，您不能使用mysql_stmt_prepare() C API 函数来准备PREPARE，EXECUTE或DEALLOCATE PREPARE语句。

准备语句的 SQL 语法可以在存储过程中使用，但不能在存储函数或触发器中使用。但是，游标不能用于使用PREPARE和EXECUTE准备和执行的动态语句。在创建游标时会检查游标的语句，因此该语句不能是动态的。

预准备语句的 SQL 语法不支持多语句(即，单个字符串中用;个字符分隔的多个语句)。

准备好的语句在第 8.10.3.1 节，“查询缓存的操作方式”中描述的条件下使用查询缓存。

要编写使用CALL SQL 语句执行包含准备好的语句的存储过程的 C 程序，必须启用CLIENT_MULTI_RESULTS标志。这是因为，除了过程中执行的语句可能返回的任何结果集之外，每个CALL还会返回指示呼叫状态的结果。

可以在调用mysql_real_connect()时启用CLIENT_MULTI_RESULTS，或者通过传递CLIENT_MULTI_RESULTS标志本身来显式启用，或者通过CLIENT_MULTI_STATEMENTS隐式传递(也可以启用CLIENT_MULTI_RESULTS)来启用CLIENT_MULTI_RESULTS。有关更多信息，请参见第 13.2.1 节“ CALL 语句”。