Precompilation and understanding of mysql statements in php

This article mainly shares with you the understanding of mysql statement precompilation in PHP. Preprocessing statements are used to execute multiple identical SQL statements with higher execution efficiency. Preprocessing statements work as follows:

1. Preprocessing: Create a SQL statement template and send it to the database. Reserved values ​​are marked with the parameter "?". For example:

2. INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)

3. Database parsing, compilation, query optimization on SQL statement templates, and storage of results without output.

4. #Execution: Finally, the application-bound value is passed to the parameter ("?" mark), and the database executes the statement. The application can execute the statement multiple times if the parameter values ​​are different.

Compared with directly executing SQL statements, prepared statements have two main advantages:

• The prepared statement greatly reduces the analysis time and only makes one query (although the statement is executed multiple times).

• Bind parameters reduce server bandwidth, you only need to send the parameters of the query instead of the entire statement.

• Prepared statements are very useful for SQL injection, because different protocols are used after the parameter values ​​are sent, ensuring the legitimacy of the data.

Attached below are the preprocessing codes for DML (insert delete update) and DQL (select) statements

DML preprocessing:
##

//预处理1--操作数据库
$mysqli = new mysqli('localhost','root','mayi1991','mysqldemo');
$mysqli->query('set names utf8');
//创建预编译对象
$mysqli_stmt = $mysqli->prepare("insert account (balance,name) values (?,?)");
//绑定参数
$balance = 122;
$name = '小白';
$mysqli_stmt->bind_param("ds",$balance,$name);
//$mysqli_stmt->bind_param("ds",122,'小白');    //这样是错误的，我也不知道为啥子
//执行 返回boolean值
$mysqli_stmt->execute();

DQL preprocessing:

//预处理2--查询数据库
//创建预处理对象
$mysqli_stmt = $mysqli->prepare(&#39;select name,balance from account where id < ?&#39;);
//绑定参数
$id = 5;
$mysqli_stmt->bind_param(&#39;i&#39;,$id);
//执行
$mysqli_stmt->execute();
//绑定结果集
$mysqli_stmt->bind_result($name,$balance);  //这里的变量指向的是内存地址
//输出结果集
while($mysqli_stmt->fetch()){
    echo "$name--$balance";
}

Related recommendations:

Detailed introduction to mysql statements

How to implement MySQL statement locking

Detailed tutorial on creating data tables using mysql statements

The above is the detailed content of Precompilation and understanding of mysql statements in php. For more information, please follow other related articles on the PHP Chinese website!