2 Replies Latest reply on Jan 11, 2020 5:44 AM by Gaz in Oz

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

    263a3ea1-0c3f-4fcc-b613-e8c4d3ef014d

      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.

        • 1. Re: Want To Do Bulk Insert In MySQL Database With Connector/C++
          Dave Stokes-MySQL Community Team-Oracle

          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)

          • 2. Re: Want To Do Bulk Insert In MySQL Database With Connector/C++
            Gaz in Oz

            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';