13.5 Prepared Statements

MySQL 8.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol. Using prepared statements with placeholders for parameter values has the following benefits:

MySQL 8.0支持服务器端prepared statements。这种支持利用了高效的客户机/服务器二进制协议。使用带有占位符的预置语句作为参数值有以下好处：

• Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.

• 每次执行语句时解析语句的开销更少。通常，数据库应用程序处理大量几乎相同的语句，只需更改子句中的文字或变量值，例如查询和删除的WHERE、更新的SET和插入的值。

• Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

• 防止SQL注入攻击。参数值可以包含未转换的SQL引号和分隔符。

Prepared Statements in Application Programs

You can use server-side prepared statements through client programming interfaces, including the MySQL C API client library for C programs, MySQL Connector/J for Java programs, and MySQL Connector/NET for programs using .NET technologies. For example, the C API provides a set of function calls that make up its prepared statement API. See C API Prepared Statement Interface. Other language interfaces can provide support for prepared statements that use the binary protocol by linking in the C client library, one example being the mysqli extension, available in PHP 5.0 and higher.

您可以通过客户端编程接口使用服务器端prepared statements，包括用于C程序的MySQL C API client library、用于Java程序的MySQL Connector/J，以及用于使用的程序的MySQL Connector/NET的NET技术。例如，C API提供了一组函数调用，这些函数调用构成了其 prepared statement API。参见C API Prepared Statement Interface。其他语言接口可以通过在C语言的客户端库中的链接，为使用二进制协议的prepared statements提供支持，例如PHP 5.0及更高版本中提供的mysqli extension。

Prepared Statements in SQL Scripts

An alternative SQL interface to prepared statements is available. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level:

prepared statements 还有另一个可用SQL接口。该接口的效率不如使用二进制协议的prepared statements  API，但不需要编程，因为它可以直接在SQL级别使用：

• You can use it when no programming interface is available to you.

• 当没有可用的编程接口时，可以使用它。

• You can use it from any program that can send SQL statements to the server to be executed, such as the mysql client program.

• 您可以从任何可以向服务器发送SQL语句的程序中使用它，例如mysql客户端程序。

• You can use it even if the client is using an old version of the client library.

• 即使客户端使用的是旧版本的客户端库，也可以使用它。

SQL syntax for prepared statements is intended to be used for situations such as these:

prepared statements的SQL语法旨在用于以下情况：

• To test how prepared statements work in your application before coding it.

• 在编写应用程序之前，测试prepared statements在应用程序中的怎样执行。

• To use prepared statements when you do not have access to a programming API that supports them.

• 在无法访问支持预处理语句的程序API时使用预处理语句。

• To interactively troubleshoot application issues with prepared statements.

• 使用prepared statements以交互方式解决应用程序问题。

• To create a test case that reproduces a problem with prepared statements, so that you can file a bug report.

• 创建一个测试用例，用prepared statements重现一个问题，以便您可以记录一个错误报告。

PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements

SQL syntax for prepared statements is based on three SQL statements:

预处理语句的SQL语法基于三条SQL语句：

•  PREPARE prepares a statement for execution (see Section 13.5.1, “PREPARE Statement”).

• EXECUTE executes a prepared statement (see Section 13.5.2, “EXECUTE Statement”).

• DEALLOCATE PREPARE releases a prepared statement (see Section 13.5.3, “DEALLOCATE PREPARE Statement”).

The following examples show two equivalent ways of preparing a statement that computes the hypotenuse of a triangle given the lengths of the two sides.

The first example shows how to create a prepared statement by using a string literal to supply the text of the statement:

下面的例子展示了两种编写语句的等效方法，该语句根据三角形两边的长度计算三角形的斜边。

第一个示例演示了如何使用字符串文字来提供语句的文本，从而创建一个prepared statement：

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;

The second example is similar, but supplies the text of the statement as a user variable:

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

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;

Here is an additional example that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable:

下面是另一个示例，演示如何通过将表的名称存储为用户变量来选择在运行时执行查询的表：

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;

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.

prepared statement特定于创建它的会话。如果终止session时没有取消分配先前prepared statement，服务器会自动取消它。

A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

prepared statement 在session中是全局的。如果在存储的进程中创建一条prepared statement ，那么在存储进程结束时它不会被释放。

To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count system variable. To prevent the use of prepared statements, set the value to 0.

为了防止同时创建太多repared statements，请设置max_prepared_stmt_count系统变量。要防止使用repared statements，请将该值设置为0。

SQL Syntax Permitted in Prepared Statements

The following SQL statements can be used as prepared statements:

下面这些语句将可以使用prepared statements:

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 | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
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

Other statements are not supported.

其它语句是不被支撑的。

For compliance with the SQL standard, which states that diagnostics statements are not preparable, MySQL does not support the following as prepared statements:

为了遵守SQL标准，即诊断语句是不可作为预备语句使用，MySQL不支持以下语句作为prepared statements使用：

• SHOW WARNINGS, SHOW COUNT(*) WARNINGS

• SHOW ERRORS, SHOW COUNT(*) ERRORS

• Statements containing any reference to the warning_count or error_count system variable.

Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. Exceptions are noted in Section 25.8, “Restrictions on Stored Programs”.

通常，SQL prepared statements中不允许的语句在存储过程中也不允许。例外情况见第25.8节“存储过程的限制”。

Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. For more information, see Section 8.10.3, “Caching of Prepared Statements and Stored Programs”.

检测到对已准备语句引用的表或视图的元数据更改，并在下次执行语句时自动重新准备语句。有关更多信息，请参阅第8.10.3节“预处理语句和存储程序的缓存”。

Placeholders can be used for the arguments of the LIMIT clause when using prepared statements. See Section 13.2.10, “SELECT Statement”.

当使用prepared statements时，占位符可用于LIMIT子句的参数。见第13.2.10节“选择声明”。

In prepared CALL statements used with PREPARE and EXECUTE, placeholder support for OUT and INOUT parameters is available beginning with MySQL 8.0. See Section 13.2.1, “CALL Statement”, for an example and a workaround for earlier versions. Placeholders can be used for IN parameters regardless of version

在PREPARE和EXECUTE一起使用的prepared CALL语句中，从MySQL 8.0开始就可以支持OUT和INOUT参数的占位符。持请参阅第13.2.1节“调用语句”，了解早期版本的示例和解决方法。无论版本如何，占位符都可以用于IN参数

SQL syntax for prepared statements cannot be used in nested fashion. That is, a statement passed to PREPARE cannot itself be a PREPARE, EXECUTE, or DEALLOCATE PREPARE statement.

预处理语句的SQL语法不能嵌套使用。也就是说，传递给PREPARE的语句本身不能是PREPARE、EXECUTE或DEALLOCATE PREPARE语句。

SQL syntax for prepared statements is distinct from using prepared statement API calls. For example, you cannot use the mysql_stmt_prepare() C API function to prepare a PREPARE, EXECUTE, or DEALLOCATE PREPARE statement.

预处理语句的SQL语法不同于使用预处理语句API调用。例如，您不能使用mysql_stmt_prepare（）C API函数来准备prepare、EXECUTE或DEALLOCATE PREPARE语句。

SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers. However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

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

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

预处理语句的SQL语法不支持多个语句（即，单个字符串中的多个语句由；个字符分隔）。

To write C programs that use the CALL SQL statement to execute stored procedures that contain prepared statements, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure.

要编写使用CALL SQL语句执行包含预处理语句的存储过程的C程序，必须启用CLIENT_MULTI_RESULTS标志。这是因为除了过程中执行的语句可能返回的任何结果集之外，每个调用还返回一个结果来标明调用状态。

CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect(), either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passing CLIENT_MULTI_STATEMENTS (which also enables CLIENT_MULTI_RESULTS). For additional information, see Section 13.2.1, “CALL Statement”.

调用mysql_real_connect（）时，可以显式地通过传递CLIENT_MULTI_RESULTS标志本身来启用CLIENT_MULTI_结果，或者隐式地通过传递CLIENT_MULTI_语句（这也可以启用CLIENT_MULTI_结果）。有关更多信息，请参见第13.2.1节“CALL Statement”。