MySQL preprocessing statements

The SQL syntax for a prefabricated statement is based on three SQL statements:

PREPARE stmt_name from preparable_stmt;

EXECUTE Stmt_name [USING @var_name [, @var_name] ...];

{deallocate | DROP} PREPARE Stmt_name;

The prepare statement is used to prepare a statement and give it a name of Stmt_name, which is referenced later in the statement.

Pre-compilation The lifetime of SQL is the current session, which is the current database connection. If the connection is broken, it will disappear.

Cases:

mysql> desc TB;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| ID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment |

| Name | char (20) |     NO | |                NULL | |

| Age | Tinyint (4) |     NO | |                NULL | |

+-------+------------------+------+-----+---------+----------------+

3 Rows in Set (0.08 sec)

Mysql> PREPARE prod from "INSERT into TB VALUES (?,?,?)";

Query OK, 0 rows affected (0.58 sec)

Statement prepared

Mysql> PREPARE prod from "INSERT into TB (name,age) VALUES (?,?)";

Query OK, 0 rows Affected (0.00 sec)

Statement prepared

Mysql> set @p= ' Tejevo ';

Query OK, 0 rows affected (0.06 sec)

Mysql> set @q=20;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Execute prod using @p, @q;

Query OK, 1 row affected (0.05 sec)

Mysql> SELECT * from TB;

+----+--------+-----+

| ID | Name | Age |

+----+--------+-----+

| 1 |  Tejevo | 20 |

+----+--------+-----+

1 row in Set (0.00 sec)

Mysql> deallocate Prepare prod;

Description: The From is followed by the SQL to be compiled, this value can be a literal string value, like above, can also be a variable. Like what:

Set @sql = ' SELECT * from TB ';

Prepare pstmt from @sql;

The From is followed only by literals or variables, and cannot be directly followed by a hexadecimal string.

The compiled SQL can only be a single statement and cannot be compiled with multiple statements, such as:

Prepare Mutisql from ' select 1;select 2 '; ---this is wrong.

Use the Using keyword to pass arguments to SQL when calling.

Set @value = 1;execute pstmt using @value;

Declare a variable to hold the value of the parameter, and then pass the SQL pass parameter through the subsequent using @value.
Note that the value of the parameter here can only be passed by the variable and cannot be written directly to the Execute PSTMT using 1; This is wrong.
Multiple parameters are separated by commas.

using PREPARE a few points to note:
A:prepare stmt_name from preparable_stmt; A statement is predefined and assigned to Stmt_name, and stmt_name is case insensitive.
B: Replace with the parameter. Even in the PREPARABLE_STMT statement? is represented by a string, and you do not need to be? Enclose them in quotation marks.
C: If the new PREPARE statement uses an existing stmt_name, the original will be released immediately! Even though this new PREPARE statement cannot be executed correctly because of an error.
The scope of 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 the stmt_name does not exist, an error will be raised.
G: If you do not explicitly invoke the deallocate PREPARE syntax to release resources when terminating a client connection session, the server will release it on its own.
H: There is also a limit to the class type of SQL statements that can be precompiled , and not all SQL can be compiled. In the predefined statements, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported.
I:prepare statements can not be used for stored procedures (more than 5.0 can be used), custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures and is still not supported in functions or triggers!

MySQL preprocessing statements