title: Ways to get mysql(innodb) AUTO_INCREMENT Column, JDBC/getGeneratedKeys()/last_insert_id (in OkPacket) and LAST_INSERT_ID()
body: <p>According to mysql doc:
  <a href="https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-last-insert-id.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-last-insert-id.html</a></p>
  <blockquote>
  <p>At times, it can be tricky to use the SELECT LAST_INSERT_ID() query,
  as that function's value is scoped to a connection. So, if some other
  query happens on the same connection, the value is overwritten. On the
  other hand, the getGeneratedKeys() method is scoped by the Statement
  instance, so it can be used even if other queries happen on the same
  connection, but not on the same Statement instance.</p>
  </blockquote>
  <p>First, I consider <code>LAST_INSERT_ID()</code>.
  The SQL function <code>LAST_INSERT_ID()</code> is connection safe, but not session/transaction/statement safe. It can't be used in production, because in real environment multiple session/transaction/statement in one connection is very common.</p>
  <p>Then <code>getGeneratedKeys()</code> using JDBC. When I'm using <code>getGeneratedKeys()</code> in Java. I want to see what it does in database. I try to track the SQL statement with the following statements after a simple insert into a demo table with auto increase primary key using JDBC:</p>
  <pre><code>SET GLOBAL log_output = 'TABLE'; 
  SET GLOBAL general_log = 'ON';
  SELECT * FROM mysql.general_log;
  </code></pre>
  <p>I'm sure the new row is correctly inserted and <code>getGeneratedKeys()</code> brings the auto-incremented id back. However, I find nothing but just an insert statement which JDBC executed before, and some static data like <code>&quot;SELECT database(),version()...&quot;</code>.
  Now, conclusion is, <code>getGeneratedKeys()</code> doesn't execute any SQL statement to get auto-incremented id. Then I find another possibility, I debug into call stacks, see JDBC get auto-incremented id from an object called <code>OkPacket</code>. It has a property called <code>last_insert_id</code>. Here I find it finally.</p>
  <p>My questions are:</p>
  <ol>
  <li>Is there really no way to get a STATEMENT SAFE (at least transaction safe) auto-incremented id using pure SQL statement (without JDBC)?</li>
  <li>How does <code>OkPacket</code> work under hood? How does it get a statement safe auto increased id? Maybe it calls some low level C function in MySQL driver or MySQL server/client protocol?</li>
  </ol>