MySQL BULK INSERT data

Review code discovery, colleague MySQL Bulk insert data is implemented by iterating with a for loop, inserting the list each data single time. It is equivalent to inserting n data in bulk, and the database connection and inserting operations are performed n times.

The bottom layer has a bulk insert method, but there is a problem, so it has been deactivated, see the following implementation is, take a database connection, to handle all subsequent insertions. If this list ops all the SQL statements executed by the database is the same, there is no problem, if there is a scatter in the case, as long as the first is not in the same library, will execute the failure.

  Public voidInsertbatch (list<opbatchupdate>Ops) throws SQLException {if(Ops! =NULL&& ops.size ()! =0) {opbatchupdate firstop= (opbatchupdate) Ops.Get(0); if(Firstop.bizname! =NULL&& FirstOp.bizName.trim (). Length ()! =0) {PreparedStatement PS=NULL; Connection Conn=NULL; LongBegin =0L; Try{begin= This. Sqlbegin (); Conn= This. Getconn ('W', firstop);//Take the first one to supervise the database connection Iterator i$=Ops.iterator ();  while(I$.hasnext ()) {opbatchupdate OPB=(opbatchupdate) i$.next (); PS=conn.preparestatement (Opb.getsql ());                        Opb.setparam (PS); ps.executeupdate (); if(PS! =NULL) {ps.close (); }                    }                } finally {                     This. CLOSERSC ((ResultSet)NULL, PS, conn);  This. Sqlend ("Excutebatch SQL,", begin); }            } Else {                Throw NewSQLException ("-----The Bizname of the first Opbatchupdate object can\ ' t null-------------"); }        } Else {            Throw NewSQLException ("-----The Opbatchupdate list can\ ' t null-------------"); }    }

Bulk INSERT operations on the same table for the same library. There are two ways of doing this:

Take table Smily_test as an example:

CREATE TABLE ' smily_test ' (  int(one) not NULL auto_increment,  int (one) unsigned not NULL,  PRIMARY KEY (' id ')) ENGINE=innodb auto_increment= DEFAULT Charset=utf8;

1. For loop, insert data into all strips.

INSERT into Smily_test (UID) VALUES (1), insert into smily_test (UID) VALUES (2); INSERT into Smily_test (UID) VALUES (3);

2. One-time BULK INSERT.

INSERT into Smily_test (UID) VALUES (1), (2), (3);

Through profile analysis (view MySQL statement run time) know:
Method 2 is more efficient than Method 1.

+----------+------------+------------------------------------------------+| query_id | Duration | Query |+----------+------------+------------------------------------------------+ |5|0.00079800| INSERT into Smily_test (UID) VALUES (1)         ||6|0.00081300| INSERT into Smily_test (UID) VALUES (2)         ||7|0.00078700| INSERT into Smily_test (UID) VALUES (3)         ||8|0.00083200| INSERT into Smily_test (UID) VALUES (1),(2),(3) |+----------+------------+------------------------------------------------+

Summarize:

Method 1: Inefficient execution, multiple database connections, time-consuming. However, it is suitable for the case of scattered and scattered tables.

If the data is in a different library, only multiple database connections can be made.

If the list data is to be inserted into a different table of the same 1 libraries, you can select 1 database connections and multiple data insertions to perform the same way.

Method 2: The execution time is short and only applies when bulk inserting data into the same table in the same library.

MySQL BULK INSERT data