How to get the id of an inserted row ON insert?

Question

I would like to create a unique hash of an AUTO-INCREMENT field while inserting that row. Something like:

INSERT INTO `table` (`hash`,`salt`) VALUES (MD5(CONCAT(`id`,`salt`)),'1234789687');

Is it possible to use the AUTO-INCREMENTED id of the row WHILE I'm inserting? Or do I have to wait until it's inserted to grab it via PHP or something?

Answer 1

I don't know for sure, but really doubt that this is possible. I see why you would want to thought...It's not so elegant to insert the row, then immediately update it.

I would consider if you really need to store the hash in the DB or if you can calculate it during the select:

SELECT MD5(CONCAT(id,salt)) FROM table WHERE ...

Answer 2

The short answer is no- it is not possible. If your db has low transaction volume, then concurrency may not be an issue to use something similar to select max() (susceptible to interference) : Can you access the auto increment value in MySQL within one statement?

I would follow the insert in php with an update

update `table`
set `hash` = MD5(CONCAT(`id`,`salt`))
where `id` = LAST_INSERT_ID();