MySQL pre-processing technology and MySQL pre-processing technology

MySQL pre-processing technology and MySQL pre-processing technology

The so-called preprocessing technology was originally proposed by MySQL to reduce the pressure on servers!

Traditional mysql processing process

1. Prepare SQL statements on the client

2. Send an SQL statement to the MySQL server.

3. Execute the SQL statement on the MySQL server.

4. The server returns the execution result to the client.

In this way, the mysql server receives and processes each SQL statement request once. When a script file executes the same statement multiple times, the pressure on the mysql server increases, so mysql preprocessing can reduce the pressure on the server!

Basic preprocessing policy:

Force split an SQL statement into two parts:

The first part is the same command and structure.

The second part is the variable data section.

When executing an SQL statement, first send the same command and structure to the MySQL server, so that the MySQL server can perform a preprocessing in advance (at this time, the SQL statement is not actually executed ), to ensure the structural integrity of SQL statements, the variable data part is represented by a data placeholder when the SQL statement is sent for the first time! For example, question mark? Is a common data placeholder!

There are two forms of MySQL preprocessing: preprocessing with/without Parameters

1. Preprocessing without Parameters

First, check the data in the table!

1.1 prepare preprocessing statements

PrepareStatement name from"Pre-processed SQLStatement ";

prepare sql_1 from "select * from pdo";

1.2 execute preprocessing statements

ExecuteStatement name;

execute sql_1;

1.3 Delete preprocessing

Drop prepareStatement name;

drop prepare sql_1;

　　

After deletion, you cannot execute preprocessing statements!

 

2. Parameter preprocessing

 2.1 prepare preprocessing statements

PrepareStatement name from"Pre-processed SQLStatement ";

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

　　

2.2 define parameter variables

Set @Variable name =Value;-- @ Here is a syntax form for defining variables in MySQL (like the $ symbol in php)

set @id=2;

2.3 pass parameter variables and execute preprocessing statements

ExecuteStatement name usingParameter variables;

Execute SQL _2 using @ id; -- select the id = 2

　　

2.4 Delete preprocessing

Drop prepareStatement name;

drop prepare sql_2;

　　

Note: if there are more than one data placeholder, you can pass the parameter in the order of the Data placeholder:

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 parameter must correspond to the placeholder in step 1.

The above is MySQL preprocessing. The effect of preprocessing an SQL statement in a script file is not obvious. The efficiency of preprocessing is improved when a statement is repeatedly executed!

After learning how to use preprocessing in MySQL, the next article introduces how to use preprocessing in PDO!