C API Quick way to insert bulk data into MySQL-about Mysql_autocommit

MySQL default data submission operation mode is autocommit mode (autocommit). This means that unless a transaction is explicitly started, each query is executed automatically as a separate transaction. We can change whether the autocommit mode is auto-committed by setting the value of autocommit. The command that queries the current database transaction submission method is:

Mysql> Show variables like ' autocommit '; +---------------+-------+| variable_name | Value |+---------------+-------+| Autocommit    | On    Set (0.04 sec) 

Where "on" stands for autocommit mode Open, the code that uses the MySQL C API to close transaction autocommit is:

  MySQL MySQL;        Mysql_init (&mysql); if (!mysql_real_connect (&mysql, host, user, password, schema, port, NULL, 0mysql database connection failed. \n "); return-1int Set_cs_r = Mysql_set_character_set (&mysql,  " Gbk "); Mysql_autocommit (&mysql, 0);         

MySQL default storage engine is the Myisam,myisam storage engine does not support transaction processing, so change autocommit does not have any effect, InnoDB storage engine supports transaction processing. InnoDB table engine shutdown MySQL Automatic transaction submission can greatly improve the efficiency of data insertion, because if you need to insert 1000 data, MySQL will automatically initiate (commit) 1000 times the data write request, if the autocommit shut down, through the program to control, It can be done once a commit is taken. The sample code (the logical process) is as follows:

IntH_utility::insertrecordstomysql (Constchar*DataFilePath,Constchar*HostConstchar*UserConstchar*PasswordConstchar*SchemaConstchar*TableConstIntPortConstchar*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 database connection failed. \ n");Return-1; } ofstream F1 (LogFilePath);if (!F1.is_open ()) {printf ("Log file creation failed! \ n");Return0; } mysql_autocommit (&mysql,0);//Turn off auto-commitchar* Out_text =Newchar[1024];int cursor =0;while (!Rpf.eof ()) {memset (Out_text,0x001024); Rpf.getline (Out_text,1024);StringSTR (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 ("Row%d data incomplete, write failed: \ n", LineCount); f1<<str<<EndlContinue; }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[). Append ("', '%y-%m-%d%h:%i:%s '))");int isuccess = mysql_query (&MySQL, Sql_str.c_str ());if (isuccess! =0) {ConstChar *mysql_err = mysql_error (&MySQL); printf"%s\n", Mysql_err); f1<<str<<Endl;} else {cursor++;} if (cursor==50000)// every 50,000 records are submitted once {Mysql_commit (&mysql); cursor = 0; printf ( "%d\n", LineCount);}}} delete []out_text; Rpf.close (); F1.flush (); F1.close (); Mysql_close (&MySQL);} return LineCount;}                

On my notebook computer (Thinkpad T430; i5-3380 CPU; 4G DDR3 RAM; 500G&7200RPM HDD; Win7 flagship version of x64; MySQL 5.6 Community Edition) test results are: The above code into MySQL inserted 2 million records (the data file size of about 300M) time is only about 345 seconds, and on a single commit run about 3 hours only write less than 500,000 data, This shows that when using the InnoDB data engine for large data volume insertions, the problem must be optimized in the code.

C API Quick way to insert bulk data into MySQL-about Mysql_autocommit