Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use prepare, execute and deallocate in MySQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

Preface

MySQL officially refers to prepare, execute and deallocate as PREPARE STATEMENT. I used to call them [preprocessing statement]. Its use is very simple. Let's take a look at the detailed introduction.

Sample code

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

Take a chestnut:

Mysql > PREPARE pr1 FROM 'SELECT? +?'; Query OK, 0 rows affected (0. 01 sec) Statement preparedmysql > SET @ aq1, @ bread10; Query OK, 0 rows affected (0. 00 sec) mysql > EXECUTE pr1 USING @ a, @ bTX mysql + |? | +-+ | 11 | +-+ 1 row in set (0 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 1mysql > DEALLOCATE PREPARE pr1;Query OK, 0 rows affected (0.00 sec)

Using PAREPARE STATEMENT reduces the parsing of each SQL execution, such as SELECT and DELETE with WHERE conditions, or UPDATE, or INSERT, by changing the value of the variable each time.

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

It can be used in applications or in SQL scripts.

More usage:

The same PREPARE... FROM can directly connect with user variables:

Mysql > CREATE TABLE a (an int); Query OK, 0 rows affected (0.26 sec) mysql > INSERT INTO a SELECT 1 politics query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > INSERT INTO a SELECT 2 politics query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > INSERT INTO a SELECT 3 politics query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > SET @ select_test = CONCAT ('SELECT * FROM', @ FROM) Query OK, 0 rows affected (0.00 sec) mysql > SET @ table_name = 'astute query OK, 0 rows affected (0 sec) mysql > PREPARE pr2 FROM @ select_test;Query OK, 0 rows affected (0 sec) Statement preparedmysql > EXECUTE pr2; +-+ | a | +-+ | 1 | 2 | 3 | +-+ 3 rows in set (0.00 sec) mysql > DROP PREPARE pr2;-where DROP can replace DEALLOCATEQuery OK, 0 rows affected (0.00 sec)

Every time you finish executing EXECUTE, form a good habit and execute DEALLOCATE PREPARE. Statement, which releases all database resources used in execution, such as cursors.

Not only that, if a session has too many preprocessing statements, it may reach the upper limit of max_prepared_stmt_count.

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

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

If used in a stored procedure, the preprocessing statement will still be valid after the stored procedure ends if DEALLOCATE is not dropped during the procedure.

The above is all the contents of how to use prepare, execute and deallocate in MySQL. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.