Abstract

We often use useGenerateKeys to return auto-increment primary keys to avoid additional queries. On duplicate key update is often used for insertOrUpdate to avoid query before insert/ UPDATE. It’s nice to use, but I stomp a lot, and I don’t know why. This article takes a closer look at the principle of getting auto-increment primary keys.

The problem

First pick two pieces of our company some old code bugs

Batch insert user favorites

for (tries = 0; tries < MAX_RETRY; tries++) {
    final int result = collectionMapper.insertCollections(collections);
    if (result == collections.size()) {
        break; }}if (tries == MAX_RETRY) {
    throw new RuntimeSqlException("Insert collections error"); } // Rely on the collectionID generated by the databasereturn collections;
Copy the code

CollectionMapper insertCollections method

<insert id="insertCollections" parameterType="list" useGeneratedKeys="true"
        keyProperty="collectionId">
    INSERT INTO collection(
    userid, item
    )
    VALUES
    <foreach collection="list" item="collection" separator=",">
        (#{collection.userId}, #{collection.item})
    </foreach>
    ON DUPLICATE KEY UPDATE
    status = 0
</insert>
Copy the code

I don’t know if you can see the problem

Analysis of the

There are two questions

Error judgment of return value result

The number of rows affected by batch update using on Duplicate Key is different from the number of inserted rows. The main mistake is to take it for granted. If you don’t look at the documentation, look at the official website and the documentation is very clear

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

There are three types of 0: no update 1: insert 2. Update There is also a special case where the same value is updated to the original value, which may be 0 or 1 depending on the client configuration.

So this is clearly wrong

Use the userGeneratedKey of the bulk InsertOrUpdate to return the auto-increment primary key

This problem is detected when the update statement is inserted in batches. The increment primary keys returned are all wrong. Why?

1. First, let’s take a look at mybatis’ description of useGeneratedKey

>This tells MyBatis to use the JDBC getGeneratedKeys method to retrieve keys generated internally by the database (e.g. auto increment fields in RDBMS like MySQL or SQL Server). Default: false. This is obtained using JDBC's getGeneratedKeys method.Copy the code

2. Let’s look againThe JDBC specification

Before Version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported auto increment or identity columns. With older JDBC drivers for MySQL, you could always use a MySQL-specific method on the Statement interface, or issue the query SELECT 1528426 after issuing an INSERT to a table that had an AUTO_INCREMENT key. Using the MySQL-specific method call isn’t portable, and issuing a SELECT to get the AUTO_INCREMENT key’s value requires another round-trip to the database, which isn’t as efficient as possible. The following code snippets demonstrate the three different ways to retrieve AUTO_INCREMENT values. First, We demonstrate the use of the new JDBC 3.0 method getGeneratedKeys() which is now the preferred method to use if you Need to retrieve AUTO_INCREMENT keys and have access to JDBC 3.0. The second example shows how you can retrieve The same value using a standard SELECT 1528426 query. The final example shows how updatable result sets can retrieve the AUTO_INCREMENT value when using the insertRow() method.

The getGeneratedKeys() method was not unified until 3.0. It’s congruent on both sides. The database returns a LAST_INSERT_ID. This is strongly related to auto_increment_id.

Let’s look at the definition of auto_increment_id. Focus on bulk inserts

For a multiple-row insert, 1528426 and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

When a batch insert is performed, only one ID is returned. This ID is the AUTO_INCREMENT value of the first row to be inserted. As for why this is done, the reason why mysql-server can keep the id values uniform even in master-slave architecture can be seen in this article. This article will not expand.

Mysql server returns only one ID, so how can I get all ids from mysql server

3. Client implementation

Let’s look at the implementation of the client getGeneratedKeys.

JDBC com.mysql.jdbc.StatementImpl

public synchronized ResultSet getGeneratedKeys() throws SQLException {
       if(! this.retrieveGeneratedKeys) { throw SQLError.createSQLException(Messages.getString("Statement.GeneratedKeysNotRequested"), "S1009", this.getExceptionInterceptor());
       } else if(this batchedGeneratedKeys = = null) {/ / batch, the logic of this wayreturn this.lastQueryIsOnDupKeyUpdate ? this.getGeneratedKeysInternal(1) : this.getGeneratedKeysInternal();
       } else {
           Field[] fields = new Field[]{new Field(""."GENERATED_KEY"- 5, 17)}; fields[0].setConnection(this.connection);return ResultSetImpl.getInstance(this.currentCatalog, fields, new RowDataStatic(this.batchedGeneratedKeys), this.connection, this, false); }}Copy the code

Look down in the way this. GetGeneratedKeysInternal ()

Protected ResultSet getGeneratedKeysInternal() throws SQLException {int numKeys = this.getupDatecount ();return this.getGeneratedKeysInternal(numKeys);
    }
Copy the code

It is important to first get the number of rows affected by the batch insert, and then perform the specific fetch ID operation.

GetGeneratedKeysInternal method

protected synchronized ResultSet getGeneratedKeysInternal(int numKeys) throws SQLException {
       Field[] fields = new Field[]{new Field(""."GENERATED_KEY"- 5, 17)}; fields[0].setConnection(this.connection); fields[0].setUseOldNameMetadata(true); ArrayList rowSet = new ArrayList(); long beginAt = this.getLastInsertID(); // Increase step by affected rangefor(int i = 0; i < numKeys; ++i) {
              if(beginAt > 0L) {// Insert the value into row[0] = stringutils.getBytes (long.toString (beginAt)); } beginAt += (long)this.connection.getAutoIncrementIncrement(); }}Copy the code

Iterate over the number of affected rows, and then get the ids in turn.

So batch inserts are correct and can be returned. The number of rows affected by a batch insertOrUpdate is not the number of inserted rows, which may be 0,1,2.

Mybatis will insert the first 3 rows into the data, 2 rows will update,1 row will insert, then the updateCount will be 5, generateID will be 5, mybatis will insert the first 3 rows into the data.

The above is the principle analysis, if you want to know more detailed experimental results, you can see the experiment

conclusion

Bulk insert

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username, password, email, bio) values
  <foreach item="item" collection="list" separator=",">
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>
Copy the code

The @param parameter cannot be specified in mapper, otherwise there will be problems

Batch insertOrUpdate

You cannot rely on useGeneratedKey to return the primary key.

Follow the public account [Abbot’s Temple], receive the update of the article in the first time, and start the road of technical practice with Abbot

reference

Blog.csdn.net/slvher/arti…

Blog.csdn.net/qq_27680317…

Dev.mysql.com/doc/connect…

Dev.mysql.com/doc/refman/…

Dev.mysql.com/doc/refman/…