13.5 Prepared SQL Statement Syntax

最新推荐文章于 2022-05-26 07:15:00 发布

肥飞又变身啦

最新推荐文章于 2022-05-26 07:15:00 发布

阅读量406

点赞数

分类专栏： mysql 文章标签： mysql Prepared statement

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

原文：https://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html
MySQL 5.5提供了服务器端的prepared statements功能。该功能使用了更高效的客户端/服务端二进制协议，并提供了相应的编程接口。这些接口包含 MySQL C API client library（为C程序使用），MySQL Connector/J (for Java programs), and MySQL Connector/Net.比如，C API中提供了很多prepared statement相关的函数，详细信息请看23.8.8章节“C API Prepared Statements”。其它语言的驱动是通过调用C client library实现的。以mysqlli扩展为例，其可以在PHP 5.0及以后的版本中使用。

通过接口实现prepared statements实现只是方式中的一种。接口的方式只能在二进制协议中使用，但是在不能使用编程的环境中我们还能直接使用SQL语法实现prepared statements功能：

你可以不使用任何编程接口。
你可以通过任何编程方式向服务器发送sql语句，比如使用mysql客户端程序。
你可以在不支持prepared statements功能的客户端中使用该方式，仅需要你能连接到server并且能够支持prepared statements语法即可。

prepared statements语法适用于以下情况：

你可在编码之前使用SQL语法了解prepared statements是如果工作的。
如果项目在执行prepared statements时产生问题，你可以使用SQL语法定位问题。
你可以创建一个测试用例以描述你发现的prepared statements问题，方便你写bug报告。
你可以在项目的API不支持prepared statements时使用该功能。

prepared statements语法包含以下三个语句：

PREPARE 预编译一个语句（详情查看13.5.1章节“PREPARE Syntax”
EXECUTE 执行一个预编译好的语句（详情查看13.5.1章节“EXECUTE Syntax”
DEALLOCATE PREPARE 释放一个预编译的语句（详情查看13.5.1章节“DEALLOCATE PREPARE Syntax”

下面两个例子给出了两种使用prepared statements方式，用于在给出两个边长的情况下，计算直角三角形弦长。

第一个例子展示了使用字符串构建预编译语句的方式：

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;

附加一个demo，将表名存储在变量中，在运行期间动态选择表执行查询：

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;

一个预编译语句在创建时会生成相应的session，如果session结束而没用调用DEALLOCATE PREPARE释放预编译语句，服务器会自动释放该语句。

一个预编译的语句在session中是全局可用的。也就是说，如果你在stored routine（存储过程/函数）中创建了一个预编译语句，当这个stored routine结束时它并不会被释放。

可以通过设置max_prepared_stmt_count变量的值，防止同时创建大量的预编译语句。设置为0，将不能使用prepared statements功能。

下列的SQL语句可以使用prepared statements功能：

ALTER TABLE
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
Other statements are not supported in MySQL 5.5.

以及其它MySQL 5.5不支持的语句。

一般情况下，不允许在prepared statements中使用的statements也不允许在stored programs（存储过程/函数）中使用。特殊情况参见C.1, “Restrictions on Stored Programs”。

如果表或视图的元数据变更，prepared statements会自动发现并在下次执行时自动修正。更多信息13.5.4, “Automatic Prepared Statement Repreparation”.

LIMIT语句的参数可以使用占位符，参见13.2.9, “SELECT Syntax”。

从MySQL 5.5开始，使用了PREPARE和EXECUTE的存储过程，占位符支持OUT和INOUT参数。参见Section 13.2.1, “CALL Syntax”，其中列举了以前的版本是如何使用的。无论怎样，所有的版本都占位符都可以使用IN参数。

prepared statements不支持嵌套的调用方式，因为PREPARE语句不能包含REPARE, EXECUTE, or DEALLOCATE PREPARE语句。

SQL形式的预编译语句不同于通过API调用的方式，比如，你可以通过调用C API的mysql_stmt_prepare()函数完成PREPARE, EXECUTE, or DEALLOCATE PREPARE语句。

SQL形式的预编译可以使用到存储过程中，但是不能在stored functions和trigger中使用。然而，游标也不能使用动态的预编译语句，因为游标在创建时就会进行检查。

prepared statements的SQL语法也不支持多条语句（也就是说能够使用”;”分割的语句）

Prepared statements只有在某些条件下才能使用缓存，具体参见8.10.3.1, “How the Query Cache Operates”.

C语言调用包含了预编译的存储过程时，CLIENT_MULTI_RESULTS标识必须是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会处于enabled状态。设置CLIENT_MULTI_STATEMENTS会隐式打开CLIENT_MULTI_RESULTS。更多信息请看13.2.1, “CALL Syntax”.