MySQL入门之一次函数调用执行多条语句

 支持在单个字符串中指定的多语句的执行。要想与给定的连接一起使用该功能，打开连接时，必须将标志参数中的CLIENT_MULTI_STATEMENTS选项指定给mysql_real_connect()。也可以通过调用mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)，为已有的连接设置它。

常用套路：

/* 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(10);\                   UPDATE test_table SET id=20 WHERE id=10;\                   SELECT * FROM test_table;\                   DROP TABLE test_table");do{  /* 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));

具体看代码：

#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;        //从结果集，获取表头信息        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");        //从结果集， 按照行获取信息信息        MYSQL_ROW row = NULL;        //从结果集中一行一行的获取数据        while (  row = mysql_fetch_row(result))        {            fieldnum = mysql_field_count(mysql);             //优化，我的行有多少列。。。。查找这样的api函数            for (i=0; i<fieldnum; i++) //经过测试 发现 不是以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(10);\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 = mysql_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)                printf("Could not execute statement\n");    } while (status == 0);    mysql_close(mysql);}