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

This article mainly introduces how to use prepare preprocessing in Mysql. It is very detailed and has certain reference value. Friends who are interested must finish it!

The significance of MySQL PREPARE preprocessing technology is that it is a technology to reduce the pressure on the server.

That is to say, in the vast majority of cases, a certain SQL statement may be called repeatedly for execution, or only individual values will be different each time.

For example:

The WHERE clause of SELECT has different values

The SET clause of UPDATE has different values

INSERT has different VALUES values.

If you need to go through the above lexical and semantic parsing, sentence optimization, making an execution plan, and so on, the efficiency will be significantly reduced.

1. Pretreatment

MySQL provides support for server-side preparation statements, which is called preprocessing.

This support takes advantage of an efficient client / server binary protocol, and using precompiled statements with parameter value placeholders has the following benefits:

Reduces the overhead of parsing statements each time the statement is executed. Typically, database applications process a large number of almost identical statements, changing only the literal or variable values in clauses, such as WHERE for queries and deletions, SET for updates, and values for inserts.

Prevent SQL injection attacks. Parameter values can contain unescaped SQL quotes and delimiters.

Preprocessing interface

1. Preprocessing statements in an application

Statements prepared on the server side can be used through the client programming interface, including MySQL C API client libraries for C programs, MySQL Connector/J for Java programs, and for use. MySQL Connector/NET of the program of NET technology. For example, C API provides a set of function calls that make up its precompiled statement API

Prepare statements in 2.SQL scripts

There is also an alternative SQL interface for preprocessing statements. But it does not require programming, is available directly at the SQL level, and can be used in any program that can send SQL statements to the server to be executed, such as a mysql client program.

two。 Pretreatment application mode

The SQL syntax for preprocessing statements is based on three SQL statements:

The PREPARE statement is ready to execute.

EXECUTE executes a preprocessing statement.

DEALLOCATE PREPARE releases a preprocessing statement.

a. Example:

Preprocessing statements cannot operate across SESSION:

Mysql > CREATE TABLE `t1` (`id` int NOT NULL, NAME varchar (20), KEY `PREPARE stmt1 FROM id` (`id`) ENGINE=InnoDB; mysql > INSERT INTO T1 (id,name) values (1 values A'), (2), (3)), (4), (5), (6)); # set the preprocessing statement mysql > PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE astata?'; # set the transfer variable mysql > SET @ a8 # execute statement mysql > EXECUTE stmt1 USING @ a; # release preprocessing statement mysql > DEALLOCATE PREPAR stmt1;B. Preprocessing tracks changes in the execution plan

Determine whether it will be affected by the change in the amount of data by observing the changes in the status indicator Select_scan (the number of full-table search queries performed).

The execution plan of the preprocessing sql statement changes as the amount of data changes.

c. Stored procedures include preprocessing

Preprocessing statement creates a preprocessing statement in a stored routine, the statement is not released at the end of the stored routine.

DELIMITER / / DROP PROCEDURE IF EXISTS proc_prepared;CREATE PROCEDURE proc_prepared () BEGINDECLARE an INT;DECLARE I INT;PREPARE stmt1 FROM 'SELECT * FROM T1 WHERE id >?; SET @ a = 5scape execute stmt1 USING @ an exec end / / DELIMITER; call proc_prepared (); call the preprocessing statement separately after the stored procedure to return the result set: it indicates that the preprocessing did not destroy SET @ a = 5scape execute stmt1 USING @ a +-+-+ | id | NAME | +-+-+ | 6 | F |.

After the stored procedure, the preprocessing statement is called separately to return the result set: it indicates that the preprocessing is not destroyed.

SET @ a = 5; EXECUTE stmt1 USING @ a; +-+-+ | id | NAME | +-+-+ | 6 | F |.

d. View the cost of parsing statements through profile

Through the execution time of various statements in profile, the time taken to parse statements is less than 0.01s. It's negligible.

So at present, there is no obvious advantage in pretreatment.

3. Summary

The initial role of precompilation:

Improve efficiency: parse, check, compile and other work in advance.

Improve security: prevent SQL injection

Limitations and practical effects:

Because preprocessing is limited to the session level, it does not reflect the real value right now. Because the mysql GA version does not have the concept of thread pool, each link is each session

The overhead of parsing compiled statements is basically negligible for mysql environments.

The execution plan also varies with the amount of data.

From the perspective of limitations and practical effects, it has not played its due function at present. Not suitable for use in sound field environment.

The above is all the contents of this article entitled "how to use prepare preprocessing 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.