Mysql 一次性执行多条语句的实现

1.mysql数据库默认情况下，mysql_query()是一次只执行一条语句。

#include "stdafx.h"
#include <mysql.h>
#include <string>

using namespace std;

int _tmain(int argc, _TCHAR* argv[])
{
    MYSQL mysql;
    mysql_init(&mysql);

    MYSQL* pConn = mysql_real_connect(&mysql,"127.0.0.1","root","root","test",3306,NULL,0);

    if(pConn == NULL){
        printf("connect db error!\n");
        int n = mysql_errno(&mysql);
        string strError = mysql_error(&mysql);

        printf("error id: %d ,%s",n,strError.c_str());

        return 1;
    }

    string s = "";
    string sql = "";

    s = "insert into t1 values(2,'ya');";

    //执行多条语句
    /*for(int i = 0; i < 10;i++){
        sql+=s; 
    }*/
    //执行一条语句
    sql = s;

    int err = mysql_query(&mysql,sql.c_str());
    if(err!=0){
        printf("execute sql failed.\n");
    }

    mysql_close(&mysql);
    mysql_library_end();

    getchar();

    return 0;
}

默认情况下一次执行一条语句的结果输出如下：

一次执行多条语句的结果输出如下：

2.为了实现mysql_query一次可以执行多条语句：
有两个方法：
a)在建立数据库连接时指定

//a)连接时指定
MYSQL* pConn = mysql_real_connect(&mysql,"127.0.0.1","root","root","test",3306,NULL,CLIENT_MULTI_STATEMENTS);

b)要执行多条语句之前指定

MYSQL* pConn = mysql_real_connect(&mysql,"127.0.0.1","root","root","test",3306,NULL,0);
......
//连接时关闭，执行多条语句之前指定
    mysql_set_server_option(&mysql,MYSQL_OPTION_MULTI_STATEMENTS_ON);
    int err = mysql_query(&mysql,sql.c_str());
    mysql_set_server_option(&mysql,MYSQL_OPTION_MULTI_STATEMENTS_ON);

3.完整代码：

#include "stdafx.h"
#include <mysql.h>
#include <string>

using namespace std;

int _tmain(int argc, _TCHAR* argv[])
{
    MYSQL mysql;
    mysql_init(&mysql);

    MYSQL* pConn = mysql_real_connect(&mysql,"127.0.0.1","root","root","test",3306,NULL,0);
    //a)连接时指定
    //MYSQL* pConn = mysql_real_connect(&mysql,"127.0.0.1","root","root","test",3306,NULL,CLIENT_MULTI_STATEMENTS);

    if(pConn == NULL){
        printf("connect db error!\n");
        int n = mysql_errno(&mysql);
        string strError = mysql_error(&mysql);

        printf("error id: %d ,%s",n,strError.c_str());

        return 1;
    }

    string s = "";
    string sql = "";

    s = "insert into t1 values(2,'ya');";

    //执行多条语句
    for(int i = 0; i < 10;i++){
        sql+=s; 
    }
    //执行一条语句
    //sql = s;

    //连接时关闭，执行多条语句之前指定
    mysql_set_server_option(&mysql,MYSQL_OPTION_MULTI_STATEMENTS_ON);
    int err = mysql_query(&mysql,sql.c_str());
    mysql_set_server_option(&mysql,MYSQL_OPTION_MULTI_STATEMENTS_ON);
    if(err!=0){
        printf("execute sql failed.\n");
    }else{
        printf("execute sql successed.\n");
    }

    mysql_close(&mysql);
    mysql_library_end();

    getchar();

    return 0;
}