Forum Stats

  • 3,872,039 Users
  • 2,266,369 Discussions


Want To Do Bulk Insert In MySQL Database With Connector/C++

edited Jan 11, 2020 12:44AM in MySQL Community Space

Hi Everyone,

I have one question for all.

I want to do bulk insert in  MySQL Database With Connector/C++.

Any idea how i can do that.?

I have searched so many things but didn't find any way.

I have seen that there is a one method in JDBC which helps to do bulk(batch) insert in mysql which is addBatch().

Is there any method like that in Connector/C++..

Please Guide me regarding that.

Thank you all in advance.


  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Jan 10, 2020 12:22PM

    The addBatch() allows you to group related SQL statements into a batch and submit them with one call to the database.  You can get the same result by starting a transaction, adding all your data, and then issuing a commit.  This also is heavily dependent on the load on your server and record contention.

    I would suggest uploading without transactions (set auto commit to 0) and testing performance.  Then try using transactions with groups of records.  Very big transactions can be problematic for several reasons.

    Also look at the parallel bulk loader in the new MySQL shell (mysqlsh)

    Dave Stokes

    MySQL Community Manager

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jan 11, 2020 12:44AM

    In its simplest form you could just write the insert statement like this to "bulk" insert records:

    insert into a_table (col1, col2, col3) values(1, 'text1', 'text'),(2, 'text2', 'and more'),(3, 'text3', 'etc...');

    which will be faster than issueing n seperate insert statements. For large datasets you will need to split the data into multiple queries, keeping the query byte size below the "max_allowed_packet" size to avoid getting an error of exceeding the Maximum allowed packet size. You can check what the limit in bytes is by:

    mysql> show variables like 'max_allowed_packet';