title: Get auto increment keys has different behavior between JDBC and MySQL client during insert multi with key in one sql
body: <p>Create test table:</p>
  <pre class="lang-sql prettyprint-override"><code>create table t1(
    id integer auto_increment primary key,
    name varchar(32)
  );
  </code></pre>
  <p>MySQL can insert multi values separated by comma in one SQL and try to get auto_increment key as below:</p>
  <pre class="lang-sql prettyprint-override"><code>-- use MySQL client:
  insert into t1(id, name) values (1, 'apple'),(2,'banana');
  select LAST_INSERT_ID();
  </code></pre>
  <p>The <code>select LAST_INSERT_ID()</code> result will be 0, because <a href="https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id" rel="nofollow noreferrer">MySQL Reference</a> said:</p>
  <blockquote>
  <p>The value of <code>LAST_INSERT_ID()</code> is not changed if you set the AUTO_INCREMENT column of a row to a non-“magic” value (that is, a value that is not NULL and not 0).</p>
  </blockquote>
  <p>Now, let's try java program with JDBC:</p>
  <pre class="lang-java prettyprint-override"><code>public static void main(String[] args) {
      String sql = &quot;insert into t1(id, name) values(3, 'orange'),(4, 'pear')&quot;;
      try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
          PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
          stmt.executeUpdate();
          ResultSet rs = stmt.getGeneratedKeys();
          while (rs.next()) {
              System.out.print(&quot;ID: &quot; + rs.getInt(1));
          }
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  </code></pre>
  <p>We will get the incorrect result (0,0 or 3,4 should as expected):</p>
  <pre class="lang-none prettyprint-override"><code>ID: 4
  ID: 5
  </code></pre>
  <p>Diving into the JDBC code, we can find the <code>updateId</code>=4 is return by MySQL:</p>
  <pre class="lang-java prettyprint-override"><code># com.mysql.cj.protocol.a.result.OkPacket#parse
  
  ok.setUpdateID(buf.readInteger(IntegerDataType.INT_LENENC)); // last_insert_id
  </code></pre>
  <p>But MySQL should not return <code>updateId</code>=4. As <a href="https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id" rel="nofollow noreferrer">MySQL reference</a> said, the <code>updateId</code> should be 3.</p>
  <blockquote>
  <p><code>function_last-insert-id</code> : If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.</p>
  </blockquote>
  <p>And JDBC driver thinks the result should be 3.</p>
  <pre class="lang-java prettyprint-override"><code>// JDBC driver calc multi ids by add increment on `insertId`
  // com.mysql.cj.jdbc.StatementImpl#getGeneratedKeysInternal(long)
  if ((beginAt != 0 /* BIGINT UNSIGNED can wrap the protocol representation */) &amp;&amp; (numKeys &gt; 0)) {
      for (int i = 0; i &lt; numKeys; i++) {
          byte[][] row = new byte[1][];
          if (beginAt &gt; 0) {
              row[0] = StringUtils.getBytes(Long.toString(beginAt));
          } else {
              // ignored...
          }
          rowSet.add(new ByteArrayRow(row, getExceptionInterceptor()));
          // add increment as next id
          beginAt += this.connection.getAutoIncrementIncrement();
      }
  }
  </code></pre>
  <p>Enable MySQL Query Log, but can not get useful info:</p>
  <pre class="lang-none prettyprint-override"><code>2022-07-26T02:38:14.303740Z    62 Connect   xxx@1.1.1.1 on test using SSL/TLS
  2022-07-26T02:38:14.338218Z    62 Query /* mysql-connector-java-8.0.29 (Revision: dd61577595edad45c398af508cf91ad26fc4144f) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
  2022-07-26T02:38:14.386780Z    62 Query SHOW WARNINGS
  2022-07-26T02:38:14.418325Z    62 Query SET character_set_results = NULL
  2022-07-26T02:38:14.454823Z    62 Query SET autocommit=1
  2022-07-26T02:38:14.502757Z    62 Query insert into t1(id, name) values(15, 'orange'),(16, 'pear')
  2022-07-26T02:38:14.549495Z    62 Quit
  </code></pre>
  <p>Questions:</p>
  <ol>
  <li>Compare the difference between MySQL client and JDBC result, can we guess MySQL does not use <code>LAST_INSERT_ID()</code> during JDBC access generated keys?</li>
  <li>But what is the mechanism during JDBC access generated keys? (<code>updateId</code>=4 but not 3)</li>
  </ol>
  <p>Of course, if we do not set <code>id</code> in SQL and then get generated keys, everything will be correct.</p>
  <p>MySQL version: 5.7.32-0ubuntu0.16.04.1<br />
  JDBC driver version:</p>
  <pre class="lang-xml prettyprint-override"><code>&lt;dependency&gt;
      &lt;groupId&gt;mysql&lt;/groupId&gt;
      &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
      &lt;version&gt;8.0.29&lt;/version&gt;
  &lt;/dependency&gt;
  </code></pre>