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

浏览数：100 / 时间：2015年06月12日

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数据引擎进行大数据量插入时，代码中必须对该问题进行优化。

技术分享