MySQL BULK Insert return ID confusion (cause analysis)

In the project, there are often the following scenarios:

After you bulk insert a batch of data into the database, you need to know which inserts were successful and which failed.

At this time there will always be two ideas, one is to determine the same record before inserting the existence, filter out the duplicate data, the other is the edge of the insertion edge of the judgment, dynamic filtering.

The first approach does not apply to situations where the volume of data is too large, and in order to take the second approach, we use the "MyBatis BULK insert return self-increment primary key" method for processing.

After the MySQL insert operation returns the primary key is the JDBC function, uses the method is the Getgeneratedkeys () method, uses this method obtains the self-increment data, the performance is good, only needs once the interaction.

        String sql = "Insert IGNORE into User (User_name,password,nick_name,mail) VALUES (?,?,?,?)";        List<user> userlist = Lists.newarraylist ();        Userlist.add (New User ("2", "2", "2", "2"));        Userlist.add (New User ("3", "3", "3", "3"));        Userlist.add (New User ("4", "4", "4", "4"));            try {conn = Databaseutil.getconnectdb ();            PS = conn.preparestatement (Sql,preparedstatement.return_generated_keys);                for (User user:userlist) {ps.setstring (1, User.getusername ());                Ps.setstring (2, User.getpassword ());                Ps.setstring (3, User.getnickname ());                Ps.setstring (4, User.getmail ());            Ps.addbatch ();            } ps.executebatch ();            ResultSet Generatedkeys = Ps.getgeneratedkeys ();            arraylist<integer> list = Lists.newarraylist ();            while (Generatedkeys.next ()) {List.add (Generatedkeys.getint (1)); }} CATCH (SQLException e) {logger.error ("error:{}", E.getmessage (), E);        } finally {Databaseutil.close (conn, PS, NULL); }

Getgeneratedkeys () returns the ID that was just generated.

If used in MyBatis, you only need to set the parameter "keyproperty=" id "usegeneratedkeys=" "true" in the mapper file of MyBatis. For example:

   <insert id= "insertlistselective" keycolumn= "id" keyproperty= "id"            parametertype= "Bill" usegeneratedkeys= " True ">          </insert>

In order to meet our needs, we need to transform the above SQL, the idea is that when the bulk INSERT, if you encounter duplicate data, ignore, continue to insert the next record, then we are using ignore:

MySQL provides ignore to avoid repetitive insertions of data. IGNORE: If there is a record that causes a unique key conflict, the record will not be inserted into the database. Example: Insert IGNORE into ' table_name ' (' email ', ' phone ', ' user_id ') VALUES (' [E Mail protected] ', ' 99999 ', ' 9999 ') so that when duplicate records are ignored, the number 0 is returned after execution

However, after several tests, the ID returned by the object was found to be garbled.

For the above situation, if there is no duplication of data will not be the problem, so guess is because of the reasons for ignore, after reviewing the source code, verified their ideas:

public void Processbatch (mappedstatement ms, Statement stmt, collection<object> parameters) {ResultSet rs = null    ;      try {rs = Stmt.getgeneratedkeys ();      Final Configuration Configuration = Ms.getconfiguration (); Final Typehandlerregistry typehandlerregistry = Configuration.gettypehandlerregistry ();
      //refers to the keyproperty= "id" parameterFinal string[] keyproperties = Ms.getkeyproperties ();
      //ResultSet metadata refers to information about the name and type of the column in the ResultSet. Final ResultSetMetaData rsmd = Rs.getmetadata ();      typehandler<?>[] typehandlers = null; if (keyproperties! = null && rsmd.getcolumncount () >= keyproperties.length) {for (Object Parameter:pa Rameters) {//There should is one row for each statement (also one for each parameter)if (!rs.next ()) {break;          } final MetaObject Metaparam = configuration.newmetaobject (parameter); if (typehandlers = = null) {typehandlers = Gettypehandlers (Typehandlerregistry, Metaparam, Keyproperties, RSMD)          ; }
         //Set the returnedKeyproperty (Reflection)Populatekeys (RS, Metaparam, keyproperties, typehandlers); }}} catch (Exception e) {throw new Executorexception ("Error getting generated key or setting result to par Ameter object.    Cause: "+ E, E);        } finally {if (rs! = null) {try {rs.close (); } catch (Exception e) {//Ignore}}}}

private void Populatekeys (ResultSet rs, MetaObject Metaparam, string[] keyproperties, typehandler<?>[] Typehandlers) throws SQLException {
  for (int i = 0; i < keyproperties.length; i++) {
    String property = Keyproperties[i];
    typehandler<?> th = typehandlers[i];
    if (th = null) {
      Object value = Th.getresult (RS, i + 1);
      Metaparam.setvalue (property, value);
    }
  }
}

Note this sentence in the code: //There should is one row for each statement (also-one for each parameter), translated to each element corresponding to a ResU Ltset

Analyze This Loop code:

for (Object parameter:parameters) {          //There should is one row for each statement (also one for each parameter)          if (!rs.next ()) {break            ;          }          Final MetaObject Metaparam = configuration.newmetaobject (parameter);          if (typehandlers = = null) {            typehandlers = gettypehandlers (Typehandlerregistry, Metaparam, Keyproperties, RSMD);          }          //Set the returned Keyproperty (reflection)          Populatekeys (RS, Metaparam, keyproperties, typehandlers);}

looping through the elements to be inserted, and then setting the value of the primary key by reflection, but note that each time you traverse the insertion element,resultset is also traversing down, and there is a problem:
Stmt.getgeneratedkeys () always returns the ID of the record that was inserted successfully, if there are several duplicate elements in the inserted collection, then the inserted collection element does not correspond to the returned resultset. That's why it's the problem that was created before.

To avoid the above problems, we are now using a single insert to return the ID.

MySQL BULK Insert return ID confusion (cause analysis)