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

This article mainly tells you briefly about the use of preprocessing technology in MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help. I hope this article can bring you some practical help.

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

Traditional mysql processing flow

1. Prepare sql statements on the client side

2. Send sql statements to MySQL CVM

3. Execute the sql statement on the MySQL CVM

4. The CVM returns the execution result to the client

In this way, if each sql statement is requested once, the mysql CVM will receive and process it once. When a script file is executed repeatedly for the same statement, the pressure on the mysql CVM will increase, so there will be mysql preprocessing to reduce the pressure on the CVM!

Basic strategies for preprocessing:

Force the sql statement into two:

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

The second part is the later variable data part.

When executing the sql statement, first send the same command and structure part to the MySQL CVM, and let the MySQL CVM do a pre-processing (there is no real execution of the sql statement at this time). In order to ensure the structural integrity of the sql statement, the changeable data parts are represented by a data placeholder when sending the sql statement for the first time! Like a question mark? Is a common data placeholder!

There are two forms of MySQL preprocessing: preprocessing with or without parameters

1. Preprocessing without parameters

First take a look at the data in the table!

1. Prepare the preprocessing statement

Prepare statement name from "preprocessed sql statement"

Prepare sql_1 from "select * from pdo"

2. Execute the preprocessing statement

Execute statement name

Execute sql_1

3. Delete preprocessing

Drop prepare statement name

Drop prepare sql_1

After deletion, the preprocessing statement can no longer be executed!

Second, pretreatment with parameters

1. Prepare the preprocessing statement

Prepare statement name from "preprocessed sql statement"

Prepare sql_2 from "select * from pdo where id =?"

2. Define parameter variables

Set @ variable name = value;-- where @ is a grammatical form for defining variables in MySQL (analogous to the $symbol in php)

Set @ id=2

3. Pass parameter variables and execute preprocessing statements

Execute statement name using parameter variable

Execute sql_2 using @ id;-- choose the message for 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 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 parameters here should correspond to the placeholders in step 1

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

What is the use of preprocessing technology in MySQL? let's stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.