3

I love the LAST_INSERT_ID() function of MySQL. I use it all the time to retrieve the id of the just inserted row to return from my stored procedure afterwards. However, now I have a table which has a TIMESTAMP as Primary Key that is set to DEFAULT CURRENT_TIMESTAMP. How can I retrieve this last inserted timestamp?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Simon Baars
  • 1,877
  • 21
  • 38
  • 1
    What happens if concurrent inserts yield the same CURRENT_TIMESTAMP? Are you sure its a good choice for your pk? – Alex K. Sep 11 '17 at 14:44
  • 1
    @AlexK. The OP might be using millisecond precision on the timestamp, but it still has a pretty high chance of conflicts. Not a good choice, because inserts will fail frequently, and need to be re-executed. – Bill Karwin Sep 11 '17 at 14:50
  • Although I generally agree with @BillKarwin, there is an occasional case when the time really is (or is part of) the natural identifier, and it also happens that I want to take advantage of the unique constraint to shoot an error back to any app or SProc trying. "It's a feature!" – Christopher McGowan Sep 21 '17 at 02:38
  • 1
    @ChristopherMcGowan, yes, if it's part of the design that there _shouldn't_ be more than one row with the same timestamp, then that's okay. – Bill Karwin Sep 21 '17 at 04:30

3 Answers3

5

This should do it safely:

START TRANSACTION;
{do your insert};
SELECT MAX({timestamp field}) FROM {table};
COMMIT;
Jon Harmon
  • 803
  • 1
  • 9
  • 20
  • 1
    Although I provided an alternative, I actually prefer your way of doing this in a transaction, because it is the most straight-forward to read for others (and myself) in a few months :-P. – Christopher McGowan Sep 21 '17 at 02:30
1

Another way to go about it is to leverage the LAST_INSERT_ID(expr) version of that function, which can actually set the value to whatever integer you like (perhaps the UNIX_TIMESTAMP integer of your timestamp?):

INSERT INTO my_tbl SET the_ts = FROM_UNIXTIME(LAST_INSERT_ID(UNIX_TIMESTAMP()));

Now you can:

SELECT FROM_UNIXTIME(LAST_INSERT_ID());

As in the transactional solution provided by Jon Harmon, one downside of this solution is that one has to go into the insert with this in mind (overriding the column's default with the same value explicitly, or starting a transaction). Code readability decreases a bit, and it can confuse others who wish to use this table. I think Jon's solution does a better job of retaining readability.

Also, bear in mind that if you're doing this in a trigger, triggers swallow last_insert_id() value changes.

However, you can bubble it out of a trigger using a session var if you really need to.

Try this example of setting LAST_INSERT_ID:

SELECT FROM_UNIXTIME(LAST_INSERT_ID(UNIX_TIMESTAMP()));
SELECT FROM_UNIXTIME(LAST_INSERT_ID());
Christopher McGowan
  • 1,351
  • 10
  • 10
-1

Using MAX method on timestamp field.

Deepansh Sachdeva
  • 1,168
  • 9
  • 16
  • But that is not concurrency safe like LAST_INSERT_ID is it? Because that is why we don't use the MAX method on the id field for ids. – Simon Baars Sep 11 '17 at 14:39
  • 1
    @SimonBaars: If you use repeatable-read transaction isolation, your transaction will not see any rows inserted by concurrent clients since your transaction started. You must check for errors carefully, because if your INSERT failed, then MAX() will return a value, when you think it shouldn't. FWIW, I would not use a timestamp as the primary key, because there's too great a chance of conflicts. – Bill Karwin Sep 11 '17 at 14:44
  • @SimonBaars I think `LAST_INSERT_ID` does the same and returns the MAX id – Deepansh Sachdeva Sep 11 '17 at 14:44
  • 2
    @DeepanshSachdeva, LAST_INSERT_ID() does not return the MAX id. It returns the latest id generated by an insert *in the current session*. If other concurrent clients subsequently generate greater id values, your session will still return the id generated by your session. You can prove this by opening two sessions in terminal windows side by side and do an insert and select last_insert_id() in each window. – Bill Karwin Sep 11 '17 at 14:47
  • @BillKarwin Oh! Thanks for providing the correct information :) – Deepansh Sachdeva Sep 11 '17 at 14:51