Tips on mysql preprocessing technology

Let’s first look at the traditional method of operating a database.

There are two traditional methods of operating the database:

1. First write a sql statement, and then use mysqli->query($sql) To operate the database (the author uses the mysqli extension library here). There is nothing wrong with this operation, but what if you want to insert thousands or tens of thousands of pieces of data? Do we still have to write a SQL statement like this and then operate the database? Then some people will say, it's easier to use mysqli's own method of operating multiple sql statements, which is the second method.

2. ##mysqli->multi_query($sql), This is the method to operate multiple sql statements, as follows:

## If you think this can completely solve the problem, then you That's wrong, let's take a look at the principle of MySQL database executing sql statements!

　

As can be seen from the above figure, whether we send one sql statement or multiple sql statements, the database must compile them one by one. Then when the data reaches a certain amount, the database The cost will be huge. So how to solve this problem? At this time, the concept of preprocessing technology was introduced.

Now let’s look at a piece of code for preprocessing technology:

 <?         =  mysqli("localhost", "root", "123456", "student"  (->     ("连接失败" . ->  ->query("set names utf8"  = "insert into student_info(name,age,sex,studentNo,grade) values (?,?,?,?,?)"   = ->prepare(   = "张三"  = 30  = 0;  = "1501222"  = 89 
 ->bind_param("siisd", , , , ,   ->  ->close();

First let’s look at

　　
　　等等，为什么values会是问号呢？这里的问号相当于一个占位符，之后只要向数据库发送数据就能够自动把数据对应的填充进去

　　这就是预编译技术的精髓之处，我们通过bind_param，顾名思义，就是绑定参数的意思，那么，它给谁绑定参数呢？看看上面的values (?,?,?,?,?)，bind_param里面的参数一一对应

着values的每一个参数。那么bind_param里面的siisd又是什么意思呢？别急，请看下方：

　　参数有以下四种类型: 
　　i - integer（整型） 
　　d - double（双精度浮点型） 
　　s - string（字符串） 
　　b - BLOB（布尔值） 
　　每个参数都需要指定类型。 
　　通过告诉数据库参数的数据类型，可以降低 SQL 注入的风险.

$mysqli_stmt->execute();

This code is to pass the data to the database.

　

Querying the database using preprocessed data

<span style="color: #000000"> 　代码如下：<br/>　　</span>

<?php/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/7/21
 * Time: 9:37 */$mysqli = new mysqli("localhost","root","123456","student");
 if($mysqli->connect_error){    
 die("连接失败".$mysqli->error);
}
$mysqli->query("set names utf8");
$sql="select name,sex,age from student_info where id>?";
$mysqli_stmt=$mysqli->prepare($sql);
$id=1;$mysqli_stmt->bind_param("i",$id);
$mysqli_stmt->execute();//绑定结果集，传递的是引用$mysqli_stmt->bind_result($name,$sex,$age);
while($mysqli_stmt->fetch()){    
    echo "$name--$sex--$age"."<br>";
}//关闭资源$mysqli_stmt->free_result();//关闭预编译语句，否则数据库会一直保存$mysqli_stmt->close();//关闭连接$mysqli->close();

It can be seen that query has more code like

than insertion. So what does this code mean?

As you can imagine, when querying the database, the database will inevitably return a result set, so if you want to get some data about the result, you must add this sentence. So, what do these parameters mean?

Look at this sql statement, you will find that the parameters in bind_result correspond one-to-one to the fields (name, sex, age) you want to query in the sql statement. Of course, the parameters in bind_result do not have to be the same as the field names of the sql statement, but we usually recommend this.

Not much to say, the picture above:

# can be understood like this: $name, $sex, $age are passed by reference, they are equivalent to pointers, Point to the column address of the first row of the result set respectively. Each time the while statement is executed, the pointer moves downward

In this way, the data of each row can be printed out. The results are as follows:

At the same time, don’t forget those three closures, they are the top priority.

The above is the detailed content of Tips on mysql preprocessing technology. For more information, please follow other related articles on the PHP Chinese website!