Detailed explanation of the usage of prepare, execute and deallocate in MySQL

prepare, execute and deallocate are all prepared statements in mysql. This article mainly introduces the usage tutorial of prepared statements prepare, execute and deallocate in MySQL. The article introduces it in detail through sample code. It has certain reference learning value for everyone to learn or use mysql. Friends who need it can follow the editor to learn together.

Recommended related mysql video tutorials: "mysql tutorial"

Foreword

MySQL officially refers to prepare, execute, and deallocate as PREPARE STATEMENT. I am used to calling it [preprocessing statement]. Its usage is very simple. I won’t say much below. Let’s take a look at the detailed introduction.

Sample code

PREPARE stmt_name FROM preparable_stmt

EXECUTE stmt_name
 [USING @var_name [, @var_name] ...] -

{DEALLOCATE | DROP} PREPARE stmt_name

For example:

mysql> PREPARE pr1 FROM 'SELECT ?+?';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> 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; -- 只能使用用户变量传递。
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)

Using PAREPARE STATEMENT can reduce syntax analysis every time you execute SQL. For example, if you use it to execute SELECT and DELETE with WHERE conditions, or UPDATE, or INSERT, you only need to modify the variable value each time.

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

Applicable to applications or SQL scripts.

More usage:

Similarly PREPARE... FROM can directly connect 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 prepared

mysql> EXECUTE pr2 ;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

mysql> DROP PREPARE pr2; -- 此处DROP可以替代DEALLOCATE
Query OK, 0 rows affected (0.00 sec)

Every time EXECUTE is executed, develop a good habit and execute the DEALLOCATE PREPARE... statement, which can release all database resources (such as cursors) used during execution.

Not only that, if there are too many prepared statements in a session, the upper limit of max_prepared_stmt_count may be reached.

Preprocessed statements can only be used in the creator's session, and cannot be used in other sessions.

And when you exit the session in any way (normal or abnormal), the previously defined prepared statements will no longer exist.

If used in a stored procedure, if it is not DEALLOCATEd in the procedure, the prepared statement will still be valid after the stored procedure ends.

Related recommendations:

The use of prepared statements for getting started with MySQL

php_pdo Detailed explanation of prepared statements

php Mysqli preprocessing statement two example codes

The above is the detailed content of Detailed explanation of the usage of prepare, execute and deallocate in MySQL. For more information, please follow other related articles on the PHP Chinese website!