C API向MySQL插入批量数据的快速方法——关于mysql_autocommit

来源：互联网发布：python安装视频编辑：程序博客网时间：2024/06/03 20:31

MySQL默认的数据提交操作模式是自动提交模式（autocommit）。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。查询当前数据库事务提交方式的命令为：

mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+1 row in set (0.04 sec)

其中“ON”代表autocommit模式为打开状态，使用MySQL C API关闭事务自动提交的代码为：

        MYSQL mysql;        mysql_init(&mysql);        if(!mysql_real_connect(&mysql, host, user, password, schema, port, NULL, 0))         {            printf("MySQL数据库连接失败。\n");            return -1;        }        int set_cs_r = mysql_set_character_set(&mysql, "gbk");        mysql_autocommit(&mysql, 0);

　　MySQL默认的存储引擎是MyISAM，MyISAM存储引擎不支持事务处理，所以改变autocommit没有什么作用，InnoDB存储引擎支持事务处理。InnoDB表引擎下关闭mysql自动事物提交可以大大提高数据插入的效率，这是因为如果需要插入1000条数据，mysql会自动发起（提交）1000次的数据写入请求，如果把autocommit关闭掉，通过程序来控制，只要一次commit就可以搞定。示例代码（逻辑过程）如下：

int H_Utility::insertRecordsToMySQL(    const char* dataFilePath,    const char* host,    const char* user,    const char* password,    const char* schema,    const char* table,    const int    port,    const char* logFilePath){    ifstream rpf;    rpf.open(dataFilePath);    int lineCount = 0;    if (rpf.is_open())    {        MYSQL mysql;        mysql_init(&mysql);        if(!mysql_real_connect(&mysql, host, user, password, schema, port, NULL, 0))         {            printf("MySQL数据库连接失败。\n");            return -1;        }        ofstream f1(logFilePath);        if (!f1.is_open())        {            printf("日志文件创建失败！\n");            return 0;        }        mysql_autocommit(&mysql, 0);//关闭自动提交        char* out_text = new char[1024];        int cursor = 0;        while (!rpf.eof())        {            memset(out_text,0x00,1024);            rpf.getline(out_text,1024);            string str(out_text);            if (str.length()>0)            {                lineCount++;                if (lineCount>1)                {                    std::vector<string> strVec;                    int cellCount = H_Utility::stringSplitToVector(str.c_str(), strVec, ",");                    if (cellCount<3)                    {                        printf("第%d行数据不完整，写入失败:\n",lineCount);                        f1<<str<<endl;                        continue;                    }                    string sql_str = "";                    sql_str.append("INSERT INTO `").append(SCHEMA_NAME).append("`.`").append(TABLE_NAME).append("` ");                    sql_str.append("(id,name,birthday) values (");                    sql_str.append(strVec[0]).append(",'").append(strVec[1]).append("',");                    sql_str.append("STR_TO_DATE('").append(strVec[31]).append("','%Y-%m-%d %H:%i:%s'))");                    int iSuccess = mysql_query(&mysql, sql_str.c_str());                     if (iSuccess != 0)                    {                        const char *mysql_err = mysql_error(&mysql);                        printf("%s\n",mysql_err);                        f1<<str<<endl;                    }                    else                    {                        cursor++;                    }                    if (cursor==50000)//每50000条记录提交一次                    {                        mysql_commit(&mysql);                        cursor = 0;                        printf("%d\n",lineCount);                    }                }            }        }        delete []out_text;        rpf.close();        f1.flush();        f1.close();        mysql_close(&mysql);    }    return lineCount;}

　　在本人笔记本电脑上（Thinkpad T430; i5-3380 CPU; 4G DDR3 RAM; 500G&7200RPM HDD; Win7 旗舰版 x64; MySQL 5.6 社区版）的测试结果为：上述代码往mysql中插入200万条记录（数据文件大小约为300M）耗时仅约为345秒，而逐条提交时运行约3小时仅仅写入了不到50万条数据，由此可见在使用InnoDB数据引擎进行大数据量插入时，代码中必须对该问题进行优化。

0 0