MySQL current_insert_id()? (Not last_insert_id())

Question

Hi All,

I am inserting a row with a char column for a hash based on (among other things) the row's auto id.

I know I can insert it, fetch the insert_id, calculate the hash, and update it.

Does anyone know of a way to do this in a single query? You would need the rows insert_id at the time of insert. Is that completely impossible, or is there something like current_insert_id()...

Thanks!

Answer 1

No, there's no function in MySQL that gives you the current_insert_id().

The only way to get a generated ID value from an AUTO_INCREMENT field in MySQL is to do the INSERT and then call last_insert_id(). So your plan of doing a separate UPDATE to calculate the hash is probably what you'll have to do.

I can think of two other alternatives:

• Generate the unique value yourself before the INSERT with some other mechanism besides the AUTO_INCREMENT. For example, see the UUID() function.

  SET @id = SELECT UUID();
  INSERT INTO MyTable (id, hash) VALUES (@id, hash(@id...));
  

• Don't include the ID in your hash calculation.

Answer 2

There's no way that I know of to do it in MySQL in one query, but you could do something like this in your server-side scripting language of choice:

<?php

$query = mysql_query("SHOW TABLE STATUS LIKE 'MyTable'");
$row = mysql_fetch_assoc($query);
$next_id = $row['Auto_increment'];

?>

...which gives you the id to incorporate in your SQL.

EDIT: I also found this answer which may be helpful.

Answer 3

When I do inserts I do something like this:

INSERT INTO table (col1,col2) VALUES (data1,data2);SELECT LAST_INSERT_ID()

and just run the query like I was fetching data. In VB.NET the syntax is (assuming you have the MySql.Data.MySqlClient .dll):

Dim sql As String = "[sql string above]"
Dim dr As MySqlDataReader = YourRetrieveDataFunction(sql)

dr.Read()
yourObjectInstance.ID = dr(0)
dr.Close

It's technically two queries, but only one hit on the database :)

Answer 4

You can query the next-to-be-used value from the information_schema.TABLES table, the AUTO_INCREMENT column there. (You might be setting yourself up for a race condition?)