Tutorial on using prepare, execute and deallocate statements in MySQL

Preface

MySQL officially refers to prepare, execute, and deallocate as PREPARE STATEMENT. I am used to calling it [prepared statement]. Its usage is very simple. Let's take a look at the detailed introduction without further ado.

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; -- Only user variables can be passed.
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, when executing SELECT and DELETE with WHERE conditions, or UPDATE, or INSERT, you only need to modify the variable value each time.

SQL injection can also be prevented, and parameter values ​​can contain escape characters and delimiters.

Applicable in applications or SQL scripts.

More usage:

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

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

mysql> DROP PREPARE pr2; -- DROP can replace DEALLOCATE here
Query OK, 0 rows affected (0.00 sec)

Every time you complete an EXECUTE statement, develop a good habit of executing the DEALLOCATE PREPARE ... statement to release all database resources (such as cursors) used in the execution.

Moreover, 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 session of the creator and cannot be used in other sessions.

Furthermore, 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 full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.