Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the preprocessing sentences prepare, execute and deallocate in MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

MySQL officially refers to prepare, execute and deallocate as PREPARE STATEMENT.

I used to call it a preprocessing statement.

Its use is very simple.

PREPARE stmt_name FROM preparable_stmt

EXECUTE 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 prepared

Mysql > SET @ axi1, @ bread10

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)

Using PAREPARE STATEMENT can reduce the parsing of each SQL execution.

For example, to execute SELECT and DELETE with WHERE conditions, or UPDATE, or INSERT, you only need to change 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

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;-here DROP can replace DEALLOCATE

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

This is the end of the article on "how to use the preprocessing sentences prepare, execute and deallocate in MySQL". I hope the above content can be of some help to you so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.