SQL Syntax of MySQL prepare statements

MySQL prepare Syntax:
PREPARE statement_name FROM preparable_ SQL _statement;/* definition */
EXECUTE statement_name [USING @ var_name [, @ var_name]...];/* EXECUTE the preprocessing statement */
{DEALLOCATE | DROP} PREPARE statement_name/* delete definition */;

The PREPARE statement is used to PREPARE a statement and specify the name statement_name. This statement will be referenced later. The statement name is case insensitive. Preparable_stmt can be a text string or a user variable containing the statement text. The text must be expressed as a single SQL statement, rather than multiple statements. In this statement ,'? The 'character can be used to identify a parameter. When executed, it indicates that the data value is bound to the query. '? 'Characters should not be enclosed in quotation marks, even if you want to combine them with string values. Parameter tags can only be used where data values should appear, rather than SQL keywords, identifiers, and so on.
If a pre-statement already exists, it is implicitly deleted before the new pre-statement is defined.

Every time I read others, I write them down today, so I don't need to read them anymore.
Syntax
PREPARE statement_name FROM SQL _text/* definition */
EXECUTE statement_name [USING variable [, variable...]/* EXECUTE preprocessing statement */
Deallocate prepare statement_name/* delete definition */

Example
Copy codeThe Code is 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 @;
+ ---------------------------------- +
| 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)
Bytes --------------------------------------------------------------------------------------
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)
MySQL 5.0 and later versions support a new SQL Syntax:
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @ var_name [, @ var_name]...];
{DEALLOCATE | DROP} PREPARE stmt_name;
Through it, we can implement sp_executesql similar to ms SQL to execute dynamic SQL statements!
It can also prevent injection attacks!
To have a perceptual knowledge,
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 later, you can use it in the LIMIT clause, for example: mysql> SET @ a = 1; mysql> prepare stmt from "SELECT * FROM tbl LIMIT? "; Mysql> execute stmt using @;
Mysql> SET @ skip = 1; SET @ numrows = 5; phperz.com
Mysql> prepare stmt from "SELECT * FROM tbl LIMIT ?, ? ";

Mysql> execute stmt using @ skip, @ numrows; Notes for using prepare:
A: PREPARE stmt_name FROM preparable_stmt;

Predefines a statement and assigns it to stmt_name, Which is case insensitive.
B: Even in the preparable_stmt statement? It represents a string, and you do not need? Enclosed in quotation marks.
C: If the new PREPARE statement uses an existing stmt_name, the original one will be released immediately! Even if the new PREPARE statement cannot be correctly executed due to an error.
D: The scope of PREPARE stmt_name is visible to the current client connection session.
E: to release resources of a predefined statement, use the deallocate prepare syntax.
F: In the EXECUTE stmt_name syntax, if stmt_name does not exist, an error is thrown.
G: If the deallocate prepare syntax is not explicitly called to release the resource when terminating the client connection session, the server will manually release the resource.
H: In predefined statements, create table, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW syntaxes are supported.
G: The PREPARE statement cannot be used in stored procedures. It is a custom function! However, MySQL 5.0.13 and later versions can be used in Stored Procedures and cannot be used in functions! Example: create procedure 'p1' (IN id int unsigned, IN name VARCHAR (11) 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'); drop procedure 'p1 ';