MySQL current_insert_id()? (Not last_insert_id())

Question

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!

I am curious, why would you ever need a column with a hash generated from the rows key ? — duckyflip, May 16 '09 at 22:43
In this case, it's actually not a hash, but a semi readable public key, made of the row key in base 36, appended to some other stuff. It's used as the public key in forms and other places where I need a unique key to id the row, but don't want to expose the actual row id, or show a predictable pattern. — Eli, May 17 '09 at 06:56

score 11 · Accepted Answer · answered May 16 '09 at 22:43

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.

score 2 · Answer 2 · edited May 23 '17 at 12:03

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.

edited May 23 '17 at 12:03

Community

1
1

answered May 16 '09 at 22:48

da5id

9,100
9
39
53

1

This is susceptible to a race condition. – Bill Karwin May 17 '09 at 16:26

score 1 · Answer 3 · answered May 17 '09 at 00:04

1

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

answered May 17 '09 at 00:04

great_llama

11,481
4
34
29

1

Yes, this would be susceptible to a race condition. – Bill Karwin May 17 '09 at 16:26

score 0 · Answer 4 · answered May 16 '09 at 23:34

0

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

answered May 16 '09 at 23:34

Jason

51,583
38
133
185

Eli wants the id that is *about* to be inserted, not the one that's just been inserted. – da5id May 16 '09 at 23:41
hmm... SELECT whatever, (SELECT id+1 FROM table ORDER BY id DESC LIMIT 1) AS newid .... ? i dunno if this actually works – Jason May 17 '09 at 00:03
1

FWIW, querying the top id+1 is susceptible to a race condition. Don't use it if you have more than one concurrent client. – Bill Karwin May 17 '09 at 16:25

MySQL current_insert_id()? (Not last_insert_id())

4 Answers4