Mysql –prepare statement

Prepared Statements

file:///Volumes/SamSung%20SD/Material/Mysql/refman-5.6-en.html-chapter/sql-syntax.html#sql-syntax-prepared-statements

MySQL 5.6 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol available since MySQL 4.1. Using prepared statements with placeholders(占位符) for parameter values has the following benefits:

1.减少每次执行解析语句的开销。通常，数据库应用会执行大量的几乎一样的语句，仅仅是where条件中的字面或变量值不同，或是update中的set值不同，又或是insert中的values值不同

2.防止sql注入攻击

Prepared Statements in Application Programs

You can use server-side prepared statements through client programming interfaces, including the MySQL C API client library or MySQL Connector/C 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 Section 23.8.8, “C API Prepared Statements”. 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.

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:

在sql级别就可以直接使用prepare语句
1.你可以在没有编程接口时使用它
2.你可以再任何可以发送sql语句的程序中使用它，比如 mysql 客户端程序(就是命令行那个mysql)
3.客户端版本低也不惧，只要server版本大于4.1

prepare语句用于如下情况:

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

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

To interactively troubleshoot application issues with prepared statements.

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

不知所云。。

PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements

prepare语句基于下面三个sql语句:

PRREPARE prepares a statement for execution

PREPARE stmt_name FROM preparable_stmt –stmt_name不区分大小写

EXECUTE executes a prepared statement

EXECUTE stmt_name

DEALLOCATE PREPARE releases a prepared statement

{DEALLOCATE | DROP} PREPARE stmt_name

下面的第一个例子展示了使用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;

第二个例子是类似的，但是使用会话变量接收sql文本

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;

这个例子我觉得不太好，当我改变@table值后执行execute stmt3查询的结构仍是t1表的数据。说明变量改变需要重新prepare？占位符的?不需要重新prepare
我尝试
mysql> SET @s = CONCAT(‘SELECT * FROM ‘, ?);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?)’ at line 1
貌似表名不可以用?传递

mysql> SET @table = 'city';
Query OK, 0 rows affected (0.00 sec)

mysql> set @s= CONCAT('SELECT * FROM ',@table);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt3 from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt3 ; +---------+---------+------------+---------------------+
| city_id | city | country_id | last_update | +---------+---------+------------+---------------------+
|       1 | beijing |          1 | 2016-08-01 07:17:16 |
| 2 | london | 3 | 2016-08-01 07:19:25 | +---------+---------+------------+---------------------+
2 rows in set (0.00 sec)

改变@table值
mysql> SET @table = 'incr';
Query OK, 0 rows affected (0.00 sec)

查询@s，发现没有改变
mysql> select @s; +--------------------+
| @s | +--------------------+
| SELECT * FROM city | +--------------------+
1 row in set (0.00 sec)

重新set @s值
mysql>  set @s= CONCAT('SELECT * FROM ',@table);
Query OK, 0 rows affected (0.00 sec)

mysql> select @s; +--------------------+
| @s | +--------------------+
| SELECT * FROM incr | +--------------------+
1 row in set (0.00 sec)

执行，还是查的city表
mysql> execute stmt3 ; +---------+---------+------------+---------------------+
| city_id | city | country_id | last_update | +---------+---------+------------+---------------------+
|       1 | beijing |          1 | 2016-08-01 07:17:16 |
| 2 | london | 3 | 2016-08-01 07:19:25 | +---------+---------+------------+---------------------+
2 rows in set (0.00 sec)

重新prepare
mysql> prepare stmt3 from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt3 ; +----+
| id | +----+
|  1 |
|  2 |
|  3 |
| 4 | +----+
4 rows in set (0.00 sec)

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只在创建它的会话中有效。如果会话终端前没有释放prepared statement，server会自动释放它

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.不懂

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.
max_prepared_stmt_count参数为可以prepare的statement数量

允许在prepare statement中使用的语句

The following SQL statements can be used as prepared statements:

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

通常在sql语句中不被允许使用的，也不允许在stored programs中使用

被prepare statement引用的表或试图的元数据改变会被侦测到，并出发自动repreparation
例子:

PREPARE s1 FROM 'SELECT * FROM t1';

面对select *，mysql会在内部解析出所有的列名，如果通过alter table修改了表的列信息，server又没有侦测到此情况，那么就会导致错误的结果返回

占位符可以用在limt子句中

mysql> set @s='select * from incr limit ?';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a; +----+
| id | +----+
| 1 | +----+
1 row in set (0.00 sec)

mysql> drop prepare stmt;
Query OK, 0 rows affected (0.00 sec)

在prepare的call语句中，可以为in和out参数使用占位符

mysql> set @s='call signal1(?)';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> select @a; +------+
| @a | +------+
| 1 | +------+
1 row in set (0.00 sec)

mysql> execute stmt using @a;
ERROR 1644 (99001): Employee must be 16 years or older
mysql> set @a=18;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a; +-------------------------+
| u can hire this persion | +-------------------------+
| u can hire this persion | +-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

prepare语句本身不能被prepare，同理还有execute和deallocate prepare

不可以使用mysql_stmt_prepare() C API function来prepare PREPARE, EXECUTE, or DEALLOCATE PREPARE

sql syntax的prepare语句可以用于procedure但是不能用于function或trigger。游标不能使用prepared statement，因为游标需要在创建时check

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 syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by “;” characters). 不用翻译了吧

Prepared statements use the query cache under the conditions described in Section 8.10.3.1, “How the Query Cache Operates”.

PREPARE Syntax

PREPARE stmt_name FROM preparable_stmt

prepare语句prepare一个sql语句并给他一个名字，stmt_name，以便之后引用
prepare语句通过execute语句执行，通过delloacte释放

stmt_name不区分大小写。preparable_stmt可要么是一串字符串,要么是包含sql文本的会话变量。文本只能包含一条sql语句，不支持多个语句。在语句中，使用?占位，指示出后续的using 变量被绑定到哪里。？不应被引号包括，即使你想让他绑定字符串。占位符只能被用于using变量值应该出现的地方，不能再关键字，identifiers
Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists.看不懂，有歧义

The scope of a prepared statement is the session within which it is created, which as several implications: 作用域
1.一个prepared statement仅在当前会话有效
2.当一个会话结束，无论正常还是异常结束，它的prepared statement不复存在。如果启用了auto-reconnect，那么重链接终端不会被客户端感知。因此，你可能希望禁用此参数
3.即便stored program执行结束，其中的prepared statement将继续存在，并且可以在程序外执行

A prepared statement created within a stored program continues to exist after the program finishes executing and can be executed outside the program later.

delimiter $$
drop procedure if exists pr_prepare$$ create procedure pr_prepare(in p_int int) begin set @s='select * from dept limit ?';
    prepare stmt from @s;
    set @lm=p_int;
    execute stmt using @lm;
    drop prepare stmt;
end $$
delimiter ;

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE
经过我的实验发现，第3条中，如果在程序中没有deallocate或drop stmt那么程序结束后可以继续在程序外执行，但是如果deallocate或drop了，则不可以

4.A statement prepared in stored program context cannot refer to stored procedure or function parameters or local variables because they go out of scope when the program ends and would be unavailable were the statement to be executed later outside the program. As a workaround, refer instead to user-defined variables, which also have session scope; see Section 9.4, “User-Defined Variables”.

EXECUTE Syntax

EXECUTE stmt_name
[USING @var_name [, @var_name] …]

用execute执行，如果prepared statement包含占位符，需要用using自己来绑定占位的变量
可以多次执行prepared statement，并使用不同的变量值

DEALLOCATE PREPARE Syntax

{DEALLOCATE | DROP} PREPARE stmt_name

使用 DEALLOCATE PREPARE语句来是否prepared statement，如果既没有使用上述语句释放prepared statement又没有结束会话，可能会导致prepared statement数量超出max_prepared_stmt_count参数值