【官方】Mysql5.6中执行动态SQL

【官方】执行预处理SQL语句

说明：本文未经作者书面授权，禁止转载、复制等。

MySQL 5.6为服务器端预处理语句提供支持。 此支持利用了高效的客户端/服务器二进制协议。 将带有占位符的预准备语句用于参数值具有以下好处：

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

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

应用程序中的预处理语句

您可以通过客户端编程接口使用服务器端预处理语句，包括MySQL C API客户端库或用于C程序的MySQL Connector / C，用于Java程序的MySQL Connector / J和用于使用.NET技术的程序的MySQL Connector / NET。 例如，C API提供了一组函数调用，这些函数调用组成了它准备好的语句API。 请参见第23.8.8节“C API准备语句”。 其他语言接口可以通过在C客户端库中链接来提供对使用二进制协议的预准备语句的支持，其中一个示例是mysqli扩展，可在PHP 5.0及更高版本中使用。

SQL脚本中的预处理语句

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

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

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

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

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

• 在编写之前测试准备语句在应用程序中的工作方式。

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

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

• 创建一个用预处理语句重现问题的测试用例，以便您可以提交错误报告。

PREPARE, EXECUTE, 与 DEALLOCATE 预处理语句

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

1. PREPARE准备一个执行语句。
2. EXECUTE执行预准备语句。
3. DEALLOCATE PREPARE发布预准备语句。

以下示例显示了两种等效的方法来准备一个语句，该语句根据双方的长度计算三角形的斜边。

第一个示例显示如何使用字符串文字来提供语句字符串来创建预准备语句：

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;

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

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 (as of MySQL 5.6.8)
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} INDEX
{CREATE | RENAME | 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
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {AUTHORS | CONTRIBUTORS | 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.6不支持其他语句。

通常，SQL预处理语句中不允许的语句也不允许存储在程序中。

检测到由预准备语句引用的表或视图的元数据更改，并在下次执行时自动表示语句。

使用预准备语句时，占位符可用于LIMIT子句的参数。

在与PREPARE和EXECUTE一起使用的准备好的CALL语句中，从MySQL 5.6开始提供对OUT和INOUT参数的占位符支持。无论版本如何，占位符都可用于IN参数。

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

预准备语句的SQL语法与使用预准备语句API调用不同。例如，您不能使用themysql_stmt_prepare（） API函数来准备PREPARE，EXECUTE或DEALLOCATE PREPARE语句。

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

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

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

调用mysql_real_connect（）时可以启用CLIENT_MULTI_RESULTS，可以通过传递CLIENT_MULTI_RESULTS标志本身显式调用，也可以通过传递CLIENT_MULTI_STATEMENTS（也启用CLIENT_MULTI_RESULTS）隐式调用。