C++ insert into table in bulk
1.Create table in mysql
CREATE TABLE `mt` ( `BookIndex` int NOT NULL AUTO_INCREMENT, `BookId` bigint NOT NULL, `BookName` varchar(100) NOT NULL, `BookTitle` varchar(100) NOT NULL, PRIMARY KEY (`BookIndex`) )
//ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
run below command to validate the table created statement.
desc mt;
2.Cpp code
#include <cppconn/driver.h> #include <cppconn/resultset.h> #include <cppconn/statement.h> #include <cppconn/exception.h> #include <cppconn/prepared_statement.h> #include <iostream> #include <sstream> #include <string.h> using namespace std; static char *uuidValue = (char *)malloc(40); char *getUuid() { uuid_t newUUID; uuid_generate(newUUID); uuid_unparse(newUUID, uuidValue); return uuidValue; } void batchInsert(int loops); int main(int args, char **argv) { batchInsert(atoi(argv[1])); } void batchInsert(int loops) { try { sql::Driver *driver; sql::Connection *conn; sql::PreparedStatement *prepStmt; driver=get_driver_instance(); conn=driver->connect(tcp://127.0.0.1:3306,root,Password); conn->setSchema(myDB); stringstream ss; ss<<insert into mt(BookId,BookName,BookTitle) values ; for(int i=0;i<loops;i++) { ss<<(<<i<<,'<<getUuid()<<','<<getUuid()<<'),<<endl; } string str=ss.str(); int lastIndex=str.find_last_of(,); str=str.erase(lastIndex); prepStmt=conn->prepareStatement(str); prepStmt->execute(); delete prepStmt; delete conn; } catch (sql::SQLException &e) { cout << # ERR: SQLException in << __FILE__; cout << ( << __FUNCTION__ << ) on line << __LINE__ << endl; cout << # ERR: << e.what(); cout << (MySQL error code: << e.getErrorCode(); cout << , SQLState: << e.getSQLState() << ) << endl; } cout<<Finished in void batchInsert(int loops)!<<endl; }
3.Compile via g++ as below
g++ -g -std=c++2a -I. -Wall -I/usr/include/cppconn *.cpp ./Model/*.cpp -o h1 -luuid -lpthread -L/usr/lib -lmysqlcppconn
4.Execute the compiled result via
time ./h1 100000;
The fact has illustrated that insert into table once in bulk will be much faster than insert looply one by one.
5.Validate in mysql via sql
select * from mt;