当mysql引擎为Innodb时,mysql支持提交模式的设置,默认是自动提交的,通过设置提交模式为关闭,最后统一提交,我们可以提
高批量保存数据的效率,下面是实例代码:
MYSQL mysql;
mysql_init(&mysql);
if (!mysql_real_connect(&mysql, "host", "user", "password", "dbname", 0, NULL, 0))
{
printf( "Error connecting to database: %s\n", mysql_error(&mysql));
exit(-1);
}
mysql_autocommit(&mysql, 0);//关闭自动提交
for (i = 0; i < TABLE_SIZE; i++)
{
Flow *data = table.table[i]->next;
while (data != NULL)
{
char insert[256] = {0};
sprintf(insert, "insert into tbas_flow(BigIp, SmallIp, BigPort, SmallPort, Protocol, PacketNum, AttackTime) values('%s', '%s', %u, %u, '%s', %d, '%s')",
data->bigIp, data->smallIp, data->bigPort, data->smallPort,
data->protocol, data->packetNum, data->attackTime);
int t = mysql_real_query(&mysql, insert, (unsigned int)strlen(insert));
if (t)
{
printf("Error making query: %s\n", mysql_error(&mysql));
}
else
{
records++;
}
Flow* tmp = data;
if (tmp != NULL)
{
tmp->next = NULL;
free(tmp);
tmp = NULL;
}
data = data->next;
}
table.table[i]->next = NULL;
}
// commit to db
if (records > 0)
{
mysql_commit(&mysql);
}