MySQL--Execute multiple SQL statements at once

A recent software upgrade involves changes to the database table fields (new or deleted or modified), All SQL statements about the changes to the database are stored in the Sqlupdate.sql file, and all SQL statements in the Sqlupdate.sql are executed every time the upgrade, which involves the issue of executing multiple statements. Software developed on the CodeIgniter framework, CodeIgniter has encapsulated functions that execute SQL statements: $this->db->query (' * * * *) ', but this function can execute only one SQL statement at a time. So we have to find another way. Baidu, found some information. The Multi_query () method is executed exactly according to the statement in the SQL environment, so you can execute multiple SQL statements at once (spelled by SQL syntax, and ending with each statement).

 $mysqli=NewMysqli ($this->db->hostname,$this->db->username,$this->db->password,$this->db->database);//connect to MySQL database if($mysqli-Connect_errno) { //Determine if the connection is successful printf("Connect failed:%s\n",$mysqli-connect_error); Exit(); }  $mysqli->multi_query ($dataStr);//Execute SQL statement

The following is a reference to Baidu:

You can only execute one SQL command per call using the query () method in the Mysqli object. If you need to execute more than one SQL command at a time, you must use the Multi_query () method in the Mysqli object. This is done by writing multiple SQL commands in the same string as arguments to the Multi_query () method, separating multiple SQL with semicolons (;). If the first SQL command executes without error, this method returns True, otherwise false is returned.

Because the Multi_query () method can connect to execute one or more queries, each SQL command may return a result and need to get each result set if necessary. So there are some changes in the processing of the return result of the method, the result of the first query command is read with the Use_result () or Store_result () method in the Mysqli object, and of course, the full result is retrieved immediately to the client using the Store_result () method , this approach is more efficient. In addition, you can use the More_results () method in the Mysqli object to check if there are additional result sets. If you want to process the next result set, you should call the Next_result () method in the Mysqli object to get the next result set. This method returns True (with the next result) or false. If you have the next result set, you also need to use the Use_result () or Store_result () method to read. The code for executing multiple SQL commands is as follows:

<?PHP$mysqli=NewMysqli ("localhost", "Mysql_user", "Mysql_pwd", "demo");//connect to MySQL database    if(Mysqli_connect_errno()) {//Check for connection errors         printf("Connection failed:%s<br>",Mysqli_connect_error()); Exit(); }     /*use a semicolon (;) to separate three SQL commands and concatenate them into a single string*/         //set the query character set to GB2312    $query= "SET NAMES GB2312;"; //get current user from MySQL server    $query. = "Select Current_User ();"; //reading data from the ContactInfo table    $query. = "Select Name,phone from ContactInfo LIMIT 0,2"; //execute multiple SQL commands    if($mysqli->multi_query ($query)) {                               Do {            //get first result set             if($result=$mysqli-Store_result ()) {                            //iterate through each record in the result set                   while($row=$result-Fetch_row ()) {                           //get each column of data from a row of record arrays                      foreach($row  as $data){                             //output each column of data                        Echo $data." &nbsp;&nbsp; "; }                    //output line break symbols                    Echo"<br>"; }                    //close an open result set                    $result-Close (); }            //determine if there are more result sets          if($mysqli-more_results ()) {                          //Output Line Divider             Echo"-----------------<br>"; }        }  while($mysqli->next_result ());//get the next result set and continue the loop     }    $mysqli->close ();//Close Mysqli Connection?>

The output results are as follows:

1. [Email protected]
2. -----------------
3. High XXX 15801688338
4. Luo xxx 15801681234

In the example program above, use the Multi_query () method in the Mysqli object to execute three SQL commands at a time to get multiple result sets and traverse the data from them. If an error occurs during the processing of the command, the Multi_query () and Next_result () methods can cause problems. The return value of the Multi_query () method, as well as the properties of the Mysqli errno, error, info, and so on are only related to the first SQL command, and it is not possible to determine whether the second and subsequent commands have an error executing. Therefore, when executing the return value of the Multi_query () method is true, it does not mean that subsequent commands do not have errors at execution time.

MySQL--Execute multiple SQL statements at once