This article is mainly to share with you mysqli batch execution of multiple statements and a function call to execute multiple statement methods, I hope you can have their own ideas through the example of this article.
Supports the execution of multiple statements specified in a single string. To use this feature with a given connection, you must assign the Client_multi_statements option in the flag parameter to Mysql_real_connect () when you open the connection. You can also set it for an existing connection by calling Mysql_set_server_option (mysql_option_multi_statements_on).
Common routines:
/* Connect to server with option Client_multi_statements */mysql_real_connect (..., client_multi_statements);/* Now Execute multiple queries */mysql_query (MySQL, "DROP table IF EXISTS test_table;\ CREATE TABLE test_table (id INT); \
insert into test_table VALUES, \ UPDATE test_table SET id=20 WHERE id=10;\ SELECT * from test_table;\ DR OP TABLE test_table ");d o{/ * Process All results */ ... printf ("Total Affected rows:%lld", Mysql_affected_rows (MySQL)); ... if (! ( result= mysql_store_result (mysql)) { printf (stderr, "Got fatal error processing query\n"); Exit (1); } Process_result_set (result); /* Client function */ mysql_free_result (result);} while (!mysql_next_result (MySQL));
See the code specifically:
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <dlfcn.h> #include < mysql/mysql.h> #include <stdio.h> #include <stdlib.h> #include <unistd.h> #include <string.h > #include <errno.h> #include <termios.h> #include <mysql/mysql.h>void process_result_set (MySQL * MySQL, mysql_res *result) {int i = 0; unsigned int fieldnum; From the result set, get the header information Mysql_field *fields = mysql_fetch_fields (result); Fieldnum = Mysql_field_count (mysql); for (i=0; i<fieldnum; i++) {printf ("%s\t", fields[i].name); } printf ("\ n"); From the result set, the information is obtained by row mysql_row row = NULL; Gets the data from a row in the result set (row = Mysql_fetch_row (result)) {fieldnum = Mysql_field_count (mysql); optimization, how many columns of my row are .... Look for such API functions for (i=0; i<fieldnum; i++)//tested to find a pointer array that is not ending with 0: {printf ("%s\t", Row[i]); } printf ("\ n"); }}int Main () {int ret = 0, status = 0; MYSQL *mysql; Mysql_res *result; Mysql_row ROW; Char *query; MySQL = Mysql_init (NULL); MySQL =mysql_real_connect (mysql, "localhost", "root", "123456", "Mydb2", 0, NULL, client_multi_statements); if (mysql = = NULL) {ret = Mysql_errno (MySQL); printf ("Func mysql_real_connect () err\n"); return ret; } else {printf ("ok......\n"); }/* Execute multiple statements */status = mysql_query (mysql, "DROP table IF EXISTS test_table;\create table test_table (id INT); \insert into Test_table VALUES (ten); \update test_table SET id=20 WHERE id=10;\select * from Test_table;\drop table Test_table "); if (status) {printf ("Could not execute statement (s)"); Mysql_close (MySQL); Exit (0); }/* Process Each statement result */do {/* Did current statement return data? */result = M YsqL_store_result (MySQL); if (result) {/* YES; process rows and free the result set */Process_result_set ( MySQL, result); Mysql_free_result (result); } else/* No result set or error */{if (mysql_field_count (mysql) = = 0) {printf ("%lld rows affected\n", Mysql_affected_rows (MySQL)); } else/* Some error occurred */{printf ("Could not retrieve result set\ n "); Break }}/* More results? -1 = no, >0 = error, 0 = yes (keep looping) */if (status = Mysql_next_result (mysql)) > 0) pr intf ("Could not execute statement\n"); } while (status = = 0); Mysql_close (MySQL);}
This is the first function call of MySQL to execute the contents of multiple statements.
Next, we mainly introduce the PHP implementation of mysqli batch execution of multiple statements, combined with an example of PHP connection mysqli and batch execution of multiple statements of the relevant operation skills,
Specific as follows:
You can perform multiple operations at once or retrieve multiple result sets.
Instance:
<?php$mysqli = new mysqli ("localhost", "root", "111111", "test");/* check Connection */if (Mysqli_connect_errno ()) {printf ("Connect failed:%s\n", Mysqli_connect_error ()); Exit ();} /* Multi_query executes one or more queries against the database. Multiple queries are delimited by semicolons. */$query = "SELECT * from Test where id = 1;"; $query. = "SELECT name from Test";/* executes the query in bulk and returns FALSE if the first query fails. */if ($mysqli->multi_query ($query)) {do {/*) gets the first result set */if ($result = $mysqli->store_result ()) {while ($row = $result->fetch_row ()) {printf ("%s\n", $row [0]); } $result->free (); }/* Check if a multi-query has more results */if ($mysqli->more_results ()) {printf ("-----------------\ n"); }//Prepare the next result set} while ($mysqli->next_result ());} /* Close connection */$mysqli->close ();?