Php-mysqli pre-compilation of an extension library

(1) Benefits of precompilation

If you want to execute 100 similar SQL statements, each execution, on the MySQL side will be compiled once, inefficient. The way to improve efficiency is to reduce the number of compilations.

First, create a template for SQL statements, pre-compiled on the MySQL side, and then only need to pass the data at a time.

In addition to improving efficiency, precompilation also prevents SQL injection.

(2)DmlPre-compilation of statements

For example, insert data into a table. The table structure is as follows:

+----------+----------------------------+
| Field | Type |
+----------+----------------------------+
| ID | Int (11) |
| name | varchar (20) |
| Height | Float (5,2) |
| Gender | Enum (' Male ', ' female ') |
| class_id | Int (11) |
+----------+----------------------------+

1 //pre-compilation of DML statements2 //1. Connect to the database3 $mysqli=NewMysqli (' localhost ', ' root ', ' root ', ' Lianxi ');4 if(Mysqli_connect_errno()){5     Echo' Connection failed '.$mysqli-Connect_error;6 }7 $mysqli->query (' Set names UTF8 ');8 //2. Pre-compilation9 //question mark is a placeholderTen $sql= ' INSERT into student values (?,?,?,?,?) '; One //The SQL template is compiled with the prepare () method of the Mysqli class, returning a Mysqli_stmt class object A $stmt=$mysqli->prepare ($sql) or die($mysqli-connect_error); - //Use the Bind_param () method in the Mysqli_stmt class to bind parameters. The first parameter represents the type of each field, I (int), S (String), D (double) - $stmt->bind_param (' isdii ',$id,$name,$height,$gender,$classId); the //3. Inserting data using the Execute () method in the Mysqli_stmt class - $id=NULL; - $name= ' Mildred '; - $height= 165.00; + $gender= 2; - $classId= 12; + $stmt->execute () or die($stmt-error); A //continue inserting Data at $id=NULL; - $name= ' Shaw '; - $height= 174.50; - $gender= 1; - $classId= 11; - $stmt->execute () or die($stmt-error); in  - //Close Connection to $stmt-close (); + $mysqli->close ();

(3) Pre-compilation of DQL statements

Unlike DML statements, you need to bind the result set in addition to binding parameters

1 //pre-compilation of DQL statements2 //1. Connect to the database3 $mysqli=NewMysqli (' localhost ', ' root ', ' root ', ' Lianxi ');4 if(Mysqli_connect_error()){5      die(' Connection failed '.$mysqli-connect_error);6 }7 $mysqli->query (' Set names UTF8 ');8 //2. Compiling SQL statements9 $sql= ' SELECT * from student where id>? ';Ten $stmt=$mysqli->prepare ($sql) or die($mysqli-error); One //3. Binding Parameters A $stmt->bind_param (' I ',$id); - //4. Binding result set - $stmt->bind_result ($id,$name,$height,$gender,$classId); the //5. Implementation - $id= 2; - $stmt-execute (); - //6. Use the Fetch () method in the Mysqli_stmt class to iterate through the data that is being queried +  while($stmt-Fetch ()) { -     Echo $id.‘ --‘.$name.‘ --‘.$height.‘ --‘.$gender.‘ --‘.$classId; +     Echo' <br> '; A } at //7. Close the connection - $stmt-Free_result (); - $stmt-close (); - $mysqli->close ();

Php-mysqli pre-compilation of an extension library