摘要

我們經(jīng)常使用useGenerateKeys來(lái)返回自增主鍵，避免多一次查詢(xún)。也會(huì)經(jīng)常使用on duplicate key update，來(lái)進(jìn)行insertOrUpdate，來(lái)避免先query 在insert/update。用起來(lái)很爽，但是經(jīng)常踩坑，還不知為何。本篇就是深入分析獲取自增主鍵的原理。

問(wèn)題

首先摘兩段我司一些老代碼的bug

批量插入用戶(hù)收藏

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");
}
// 依賴(lài)數(shù)據(jù)庫(kù)生成的collectionid
return collections;

collectionMapper.insertCollections 方法

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

不知道大家能不能發(fā)現(xiàn)其中的問(wèn)題

分析

問(wèn)題有兩個(gè)

返回值result的判斷錯(cuò)誤

使用on duplicate key 批量update返回影響的行數(shù)是和插入的數(shù)不一樣的。犯這種錯(cuò)主要在于想當(dāng)然，不看文檔

看下官網(wǎng)文檔

寫(xiě)的很清楚

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.

返回值有三種

0: 沒(méi)有更新 1 ：insert 2. update

還有一個(gè)特殊情況，update 一個(gè)相同值到原來(lái)的值，這個(gè)根據(jù)客戶(hù)端配置，可能為0，可能為1。

所以這個(gè)判斷明顯錯(cuò)誤

利用批量InsertOrUpdate的userGeneratedKey來(lái)返回自增主鍵

這個(gè)問(wèn)題批量插入時(shí)有update語(yǔ)句時(shí)，就會(huì)發(fā)現(xiàn)有問(wèn)題。返回的自增主鍵都是錯(cuò)的，這是為什么呢？

1. 首先我們看下mybatis對(duì)于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.

就是使用JDBC的getGeneratedKeys的方法來(lái)獲取的。

2. 我們?cè)僬蚁?a rel="external nofollow" target="_blank" >JDBC的規(guī)范

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 LAST_INSERT_ID() 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 LAST_INSERT_ID() query. The final example shows how updatable result sets can retrieve the AUTO_INCREMENT value when using the insertRow() method.

意思就是JDBC3.0以前，有些亂七八糟的定義的，沒(méi)有統(tǒng)一，之后統(tǒng)一成了getGeneratedKeys()方法。兩邊是一致的。實(shí)現(xiàn)的原理主要就是數(shù)據(jù)庫(kù)端返回一個(gè)LAST_INSERT_ID。這個(gè)跟auto_increment_id強(qiáng)相關(guān)。

我們看下auto_increment_id的定義。重點(diǎn)關(guān)注批量插入

For a multiple-row insert, LAST_INSERT_ID() 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.

批量插入的時(shí)候只會(huì)返回一個(gè)id，這個(gè)id值是第一個(gè)插入行的AUTO_INCREMENT值。至于為什么這么干，能夠使得mysql-server在master-slave架構(gòu)下也能保證id值統(tǒng)一的原因可以看下這篇。本篇文章就不展開(kāi)了。

那么mysql server只返回一個(gè)id，客戶(hù)端批量插入的時(shí)候怎么能實(shí)現(xiàn)獲取全部的id呢

3. 客戶(hù)端的實(shí)現(xiàn)

我們看下客戶(hù)端getGeneratedKeys的實(shí)現(xiàn)。

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) {
   // 批量走這邊的邏輯
   return 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);
  }
 }

看下調(diào)用的方法 this.getGeneratedKeysInternal()

protected ResultSet getGeneratedKeysInternal() throws SQLException {
    // 獲取影響的行數(shù)
    int numKeys = this.getUpdateCount();
    return this.getGeneratedKeysInternal(numKeys);
  }

這里有個(gè)重要知識(shí)點(diǎn)了，首先獲取本次批量插入的影響行數(shù)，然后再執(zhí)行具體的獲取id操作。

getGeneratedKeysInternal方法

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();
    // 按照受影響的范圍+遞增步長(zhǎng)
    for(int i = 0; i < numKeys; ++i) {
       if (beginAt > 0L) {
            // 值塞進(jìn)去
            row[0] = StringUtils.getBytes(Long.toString(beginAt));
          }
      beginAt += (long)this.connection.getAutoIncrementIncrement();
    }
}

迭代影響的行數(shù)，然后依次獲取id。

所以批量insert是正確可以返回的。

但是批量insertOrUpdate就有問(wèn)題了，批量insertOrUpdate的影響行數(shù)不是插入的數(shù)據(jù)行數(shù)，可能是0，1，2這樣就導(dǎo)致了自增id有問(wèn)題了。

比如插入3條數(shù)據(jù)，2條會(huì)update,1條會(huì)insert,這時(shí)候updateCount就是5，generateid就會(huì)5個(gè)了，mybatis然后取前3個(gè)塞到數(shù)據(jù)里，顯然是錯(cuò)的。

以上是原理分析，如果想了解更詳細(xì)的實(shí)驗(yàn)結(jié)果，可以看下實(shí)驗(yàn)

總結(jié)

批量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>

來(lái)自官網(wǎng)的例子，mapper中不能指定@Param參數(shù)，否則會(huì)有問(wèn)題

批量insertOrUpdate

不能依賴(lài)useGeneratedKey返回主鍵。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。