MySQL prepare usage, mysqlprepare

MySQL prepare usage, mysqlprepare

MySQL officially referred to prepare, execute, and deallocate as prepare statement, which is often called a [pre-processing STATEMENT]. The following is a detailed introduction to this STATEMENT.

Sample Code

PREPARE stmt_name FROM preparable_stmtEXECUTE stmt_name [USING @var_name [, @var_name] ...] -{DEALLOCATE | DROP} PREPARE stmt_name

 

Example:

Mysql> PREPARE pr1 FROM 'select? +? '; Query OK, 0 rows affected (0.01 sec) Statement preparedmysql> SET @ a = 1, @ B = 10; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE pr1 USING @ a, @ B; + ------ + |? +? | + ------ + | 11 | + ------ + 1 row in set (0.00 sec) mysql> EXECUTE pr1 USING 1, 2; -- can only be passed USING user variables. 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 '1, 2 'at line 1 mysql> deallocate prepare pr1; Query OK, 0 rows affected (0.00 sec)

 

Parepare statement can be used to reduce the syntax analysis for each SQL STATEMENT execution. For example, to execute SELECT and DELETE statements with WHERE conditions, UPDATE statements, or INSERT statements, you only need to modify the variable values each time.

It can also prevent SQL injection. parameter values can contain escape characters and delimiters.

Applicable to applications or SQL scripts.

More methods:

Similarly, PREPARE... FROM can be directly connected to user variables:

Mysql> create table a (a int); Query OK, 0 rows affected (0.26 sec) mysql> insert into a SELECT 1; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into a SELECT 2; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into a SELECT 3; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SET @ select_test = CONCAT ('select * from', @ table_name); Query OK, 0 rows affected (0.00 sec) mysql> SET @ table_name = 'a '; query OK, 0 rows affected (0.00 sec) mysql> PREPARE pr2 FROM @ select_test; Query OK, 0 rows affected (0.00 sec) Statement preparedmysql> EXECUTE pr2; + ------ + | a | + ------ + | 1 | 2 | 3 | + ------ + 3 rows in set (0.00 sec) mysql> drop prepare pr2; -- Here DROP can replace DEALLOCATEQuery OK, 0 rows affected (0.00 sec)

 

When EXECUTE is executed every time, you must execute deallocate prepare... Statement to release all database resources (such as cursors) used during execution ).

In addition, if a session has too many preprocessing statements, it may reach the upper limit of max_prepared_stmt_count.

The pre-processing statement can only be used in the Creator's session. Other sessions cannot be used.

In addition, when you exit the session in any way (normal or abnormal), the pre-processing Statement defined previously will no longer exist.

If DEALLOCATE is not used in the stored procedure, the pre-processing statement will still be valid after the stored procedure ends.