Tutorial on the use of prepare, execute and deallocate prepared statements in MySQL

This article mainly introduces the tutorial on the use of prepared statements prepare, execute and deallocate in MySQL. Friends in need can refer to it

MySQL officially refers to prepare, execute and deallocate as PREPARE STATEMENT.
I am used to calling it [preprocessing statement].

Its usage is very simple,

##

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 the syntax analysis of each SQL execution.

For example, if it is used 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. Parameter values ​​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)

Each execution When completing EXECUTE, 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.

Prepared 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.

Summarize

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