MySQL Prepare Syntax:
PREPARE statement_name from Preparable_sql_statement; /* Definition */
EXECUTE Statement_name [USING @var_name [, @var_name] ...]; /* Execute PREPROCESSING statement * *
{deallocate | Drop} PREPARE statement_name/* Delete definition * * *;
The prepare statement is used to prepare a statement and specify the name Statement_name to be referenced later. Statement names are insensitive to case. Preparable_stmt can be a literal string, or it can be a user variable that contains the statement text. The text must behave as a single SQL statement, not multiple statements. In this statement, '? ' Characters can be used to identify parameters, when executed, to indicate that the data value is bound to the query. ‘?' Characters should not be quoted, even if you want to combine them with string values. Parameter markers can only be used where data values should appear, not SQL keywords, identifiers, and so on.
If a predefined statement already exists, it is implicitly deleted before the new predefined statement is defined.
Every time I look at someone else, I write it myself today, I don't have to look at anyone else.
Grammar
PREPARE statement_name from Sql_text/* definition */
Execute statement_name [USING variable [, variable ...]]/* EXECUTE preprocessing statements * *
DEALLOCATE PREPARE statement_name/* Delete definition * *
Cases
Copy Code code as follows:
Mysql> PREPARE prod from "INSERT into Examlple VALUES (?,?)";
mysql> SET @p= ' 1 ';
mysql> SET @q= ' 2 ';
mysql> EXECUTE prod USING @p,@q;
mysql> SET @name = ' 3 ';
mysql> EXECUTE prod USING @p, @name;
Mysql> deallocate PREPARE prod;
For example:
Mysql> prepare optimize_tables from "optimize table temp";
Query OK, 0 rows Affected (0.00 sec)
Statement prepared
Mysql> Execute optimize_tables;
+-----------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+----------+
| Test.temp | Optimize | Status | OK |
+-----------+----------+----------+----------+
1 row in Set (0.37 sec)
Mysql> Deallocate prepare optimize_tables;
Query OK, 0 rows Affected (0.00 sec)
--------------------------------------------------------------------------
mysql> prepare md5sum from ' select MD5 (?) As Md5sum ';
Query OK, 0 rows Affected (0.00 sec)
Statement prepared
Mysql> set @a=111;
Query OK, 0 rows Affected (0.00 sec)
Mysql> set @b=222;
Query OK, 0 rows Affected (0.00 sec)
Mysql> execute md5sum using @a;
+----------------------------------+
| md5sum |
+----------------------------------+
| 698d51a19d8a121ce581499d7b701668 |
+----------------------------------+
1 row in Set (0.00 sec)
Mysql> execute md5sum using @b;
+----------------------------------+
| md5sum |
+----------------------------------+
| BCBE3365E6AC95EA2C0343A2395834DD |
+----------------------------------+
1 row in Set (0.00 sec)
mysql> drop prepare md5sum;
Query OK, 0 rows Affected (0.00 sec)
--------------------------------------------------------------------------------------
Mysql> prepare update_table from "Update users set Password=password (' AAA ') where username= ' a '";
Query OK, 0 rows Affected (0.00 sec)
Statement prepared
Mysql> Execute update_table;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Mysql> Deallocate prepare update_table;
Query OK, 0 rows Affected (0.00 sec)
Starting with MySQL 5.0, a new SQL syntax is supported:
PREPARE stmt_name from preparable_stmt;
EXECUTE Stmt_name [USING @var_name [, @var_name] ...];
{deallocate | DROP} PREPARE Stmt_name;
Through it, we can implement a dynamic SQL statement like MS SQL sp_executesql!
It can also prevent injection attacks!
In order to have a perceptual understanding,
Here are a few small examples:
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;
If your MySQL version is 5.0.7 or higher, you can also use it in the LIMIT clause, as follows:mysql> SET @a=1;mysql> PREPARE STMT from "select *" TBL LIMIT ?"; mysql> EXECUTE STMT USING @a;
mysql> SET @skip = 1; SET @numrows = 5; Phperz.com
Mysql> PREPARE STMT from "select * from TBL LIMIT?,?";
mysql> EXECUTE STMT USING @skip, @numrows; Several points of attention using PREPARE:
A:prepare stmt_name from preparable_stmt;
Predefined a statement and assigns it to Stmt_name, Stmt_name is case-insensitive.
B: Even in the PREPARABLE_STMT statement? is represented by a string, and you do not need to be? Enclosed in quotes.
C: If the new PREPARE statement uses an existing stmt_name, then the original will be released immediately! Even if the new PREPARE statement cannot be executed correctly because of an error.
The scope of the D:prepare Stmt_name is that the current client connection session is visible.
E: To release a resource for a predefined statement, you can use the deallocate PREPARE syntax.
F:execute stmt_name syntax, if Stmt_name does not exist, an error will be raised.
G: If the client connection session is terminated without explicitly invoking the deallocate PREPARE syntax to release the resource, the server side will release it itself.
H: In predefined statements, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most show syntax are supported.
G:prepare statements cannot be used for stored procedures, custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures and is still not supported in functions! Here's an example: CREATE PROCEDURE ' P1 ' (in ID INT unsigned,in name VARCHAR (one)) BEGIN lable_exit:begin SET @SqlCmd = ' SELECT * FROM TA '; IF ID is not NULL THEN SET @SqlCmd = CONCAT (@SqlCmd, ' WHERE id=? '); PREPARE stmt from @SqlCmd; SET @a = ID; EXECUTE stmt USING @a; LEAVE Lable_exit; End IF; IF name is not NULL THEN SET @SqlCmd = CONCAT (@SqlCmd, ' WHERE name like? '); PREPARE stmt from @SqlCmd; SET @a = CONCAT (name, '% '); EXECUTE stmt USING @a; LEAVE Lable_exit; End IF; End Lable_exit; End; Call ' P1 ' (1,null); Call ' P1 ' (NULL, ' QQ ');D rop PROCEDURE ' P1 ';
