What is preprocessing technology in MySQL? Use of pre-processing techniques

The content of this article is to introduce to you what is MySQL preprocessing technology? Use of preprocessing techniques. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The so-called preprocessing technology was originally proposed by MySQL as a technology to reduce server pressure!

Traditional mysql processing process

1, Prepare the sql statement on the client

2, Send the sql statement to the MySQL server

3, Execute on the MySQL server The sql statement

4, the server returns the execution result to the client

In this way, each sql statement is requested once, and the mysql server will receive and process it once. When a script file requests the same statement When executed repeatedly many times, the pressure on the MySQL server will increase, so MySQL preprocessing occurs to reduce the pressure on the server!

Basic strategy for preprocessing:

Forcibly divide the sql statement into two parts:

The first part is the same command and structure part as before

The second part is the variable data part that follows

When executing the sql statement, first send the same command and structure part to the MySQL server, and let the MySQL server perform preprocessing in advance (The sql statement is not actually executed at this time). In order to ensure the structural integrity of the sql statement, the variable data part of the sql statement is represented by a data placeholder when the sql statement is sent for the first time! Like a question mark? It’s a common data placeholder!

There are two forms in MySQL preprocessing: preprocessing with parameters/preprocessing without parameters

1. Preprocessing without parameters

First check the data in the table!

1. Prepare prepared statements

prepare Statement name from “ Preprocessed sqlstatement”;

prepare sql_1 from "select * from pdo";

2, execute the preprocessed statement

execute Statement name;

execute sql_1;

3. Delete preprocessing

drop prepare Statement name;

drop prepare sql_1;

　

After deletion, preprocessing can no longer be performed Statement!

2. Preprocessing with parameters

1. Prepare prepared statements

prepare Statement name from “Preprocessed sqlstatement”;

prepare sql_2 from "select * from pdo where id = ?";

2. Define parameter variables

set @Variable name = value; --The @ here is a grammatical form for defining variables in MySQL (analogous to the $ symbol in php)

set @id=2;

3. Pass parameter variables and execute the prepared statement

execute Statement name using Parameter variable;

execute sql_2 using @id;   --选出id=2的信息

　

4. Delete preprocessing

drop prepare Statement name;

drop prepare sql_2;

　

##Note: If there is more than one data placeholder, just pass the parameters in the order of the data placeholders:

prepare sql_2 from "select * from pdo where id > ? && age > ?";

　

set @id=2;
set @age=30;

　

execute sql_2 using @id,@age;

Note that the unknown parameters here should correspond to the placeholders in step one

The above is MySQL preprocessing. The effect of preprocessing a sql statement in a script file is not obvious. The efficiency of preprocessing will be improved when a certain statement is repeatedly executed!

The above is the detailed content of What is preprocessing technology in MySQL? Use of pre-processing techniques. For more information, please follow other related articles on the PHP Chinese website!