MySQL 8.4 提供了对服务器端预处理语句的支持。此支持利用了高效的客户端/服务器二进制协议。使用带有参数值占位符的预处理语句具有以下优点：MySQL 8.4 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:

以下各节概述了预处理语句的特性：The following sections provide an overview of the characteristics of prepared statements:

应用程序中的预处理语句Prepared Statements in Application Programs

您可以通过客户端编程接口使用服务器端预处理语句，包括用于 C 程序的 MySQL C API 客户端库、用于 Java 程序的 MySQL Connector/J 以及用于使用 .NET 技术的程序的 MySQL Connector/NET。例如，C API 提供了一组函数调用，构成其预处理语句 API。参见 C API 预处理语句接口。通过链接 C 客户端库，其他语言接口也可以提供对使用二进制协议的预处理语句的支持，其中一个例子是 mysqli 扩展，在 PHP 5.0 及更高版本中可用。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 later.

SQL 脚本中的预处理语句Prepared Statements in SQL Scripts

提供了一种替代 SQL 接口来使用预处理语句。该接口不如通过预处理语句 API 使用二进制协议高效，但无需编程，因为它可以直接在 SQL 层面使用：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:

预处理语句的 SQL 语法适用于以下情况：SQL syntax for prepared statements is intended to be used for situations such as these:

PREPARE、EXECUTE 和 DEALLOCATE PREPARE 语句PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements

预处理语句的 SQL 语法基于三条 SQL 语句：SQL syntax for prepared statements is based on three SQL statements:

下面的例子展示了两种等效的方法来准备一个语句，该语句用已知两条边的长度计算三角形的斜边。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:

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.

预处理语句对整个会话也是全局的。如果在存储例程中创建预处理语句，存储例程结束时它不会被释放。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.

为了防止同时创建过多预处理语句，请设置 max_prepared_stmt_count 系统变量。要禁止使用预处理语句，将其值设置为 0。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.

预处理语句中允许的 SQL 语法SQL Syntax Permitted in Prepared Statements

以下 SQL 语句可以用作预处理语句：The following SQL statements can be used as prepared statements:

ALTER {INSTANCE | TABLE | USER}
ANALYZE
CALL
CHANGE {REPLICATION SOURCE TO | REPLICATION FILTER}
CHECKSUM
COMMIT
{CREATE | DROP} INDEX
{CREATE | DROP | RENAME} DATABASE
{CREATE | DROP | RENAME} TABLE
{CREATE | DROP | RENAME} USER
DEALLOCATE PREPARE
DROP VIEW
DELETE
DO
EXECUTE
FLUSH
GRANT {ROLE}
INSERT
INSTALL PLUGIN
KILL
OPTIMIZE
PREPARE
REPAIR TABLE
REPLACE
REPLICA {START | STOP}
RESET
REVOKE {ALL | ROLE}
SELECT
SET ROLE
SHOW {BINLOG EVENTS | BINARY LOGS | BINARY LOG STATUS | CHARACTER SETS | COLLATIONS | DATABASES | ENGINES |
      ERRORS | EVENTS | FIELDS | FUNCTION CODE | FUNCTION STATUS | GRANTS | KEYS | OPEN TABLES |
      PLUGINS | PRIVILEGES | PROCEDURE CODE | PROCEDURE STATUS | PROCESSLIST | PROFILE | PROFILES | 
      RELAYLOG EVENTS | REPLICAS | REPLICA STATUS | STATUS | PROCEDURE STATUS | TABLE STATUS | TABLES | 
      TRIGGERS | VARIABLES | WARNINGS}
SHOW CREATE { DATABASE | EVENT | FUNCTION | PROCEDURE | TABLE | TRIGGER | USER | VIEW}
TRUNCATE
UNINSTALL PLUGIN
UPDATE

其他语句不被支持。Other statements are not supported.

为了遵守 SQL 标准，该标准规定诊断语句不可预处理，因此 MySQL 不支持以下语句作为预处理语句：For compliance with the SQL standard, which states that diagnostics statements are not preparable, MySQL does not support the following as prepared statements:

通常，SQL 预备语句中不允许的语句也同样不允许存储程序中出现。异常情况可见于 第 27.8 节“Restrictions on Stored Programs”中Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. Exceptions are noted in Section 27.8, “Restrictions on Stored Programs”.

。预备语句引用的表或视图的元数据变更会被检测到，并在下一次执行时自动重新准备该语句。更多信息请参见 第 10.10.3 节，“缓存已准备好的语句和存储程序”。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 10.10.3, “Caching of Prepared Statements and Stored Programs”.

在使用预备语句时，可以使用占位符表示 LIMIT 子句的参数。参见 第 15.2.13 节，“SELECT 语句”。Placeholders can be used for the arguments of the LIMIT clause when using prepared statements. See Section 15.2.13, “SELECT Statement”.

在 prepared CALL 语句中，使用 PREPARE 和 EXECUTE，从 MySQL 8.4 起开始支持 OUT 和 INOUT 参数。参见 第 15.2.1 节，“CALL Statement”，作为示例和早期版本的变通方法。无论版本如何，IN符。In prepared CALL statements used with PREPARE and EXECUTE, placeholder support for OUT and INOUT parameters is available beginning with MySQL 8.4. See Section 15.2.1, “CALL Statement”, for an example and a workaround for earlier versions. Placeholders can be used for IN parameters regardless of version.

预备语句的 SQL 语法不能嵌套使用。也就是说，传递给 PREPARE本身不能是 PREPARE， EXECUTE，或 DEALLOCATE PREPARE语句。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 语法不同于使用预备语句 API 调用。例如，你不能使用 mysql_stmt_prepare() C API 函数来准备 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 语法可用于存储过程，但不能用于存储函数或触发器。然而，光标不能用于以 PREPARE和 EXECUTE光标的语句在创建光标时检查，因此语句不能动态。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 语法不支持多语句（即单个字符串内多个语句，且间隔为; character）。SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

要编写使用CALL SQL 语句执行包含预处理语句的存储过程的 C 程序，必须启用CLIENT_MULTI_RESULTS标志。这是因为每个CALL会返回一个结果以指示调用状态，以及存储过程内执行的语句可能返回的任何结果集。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.

在调用mysql_real_connect()时，可以启用CLIENT_MULTI_RESULTS，既可以通过显式传递CLIENT_MULTI_RESULTS标志本身，也可以通过传递CLIENT_MULTI_STATEMENTS隐式启用（这也会启用CLIENT_MULTI_RESULTS）。有关更多信息，请参见第 15.2.1 节，“CALL 语句”。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 15.2.1, “CALL Statement”.