When will we give an argument to LAST_INSERT_ID()?

Question

Quoted from here:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

What's the use case of the above (what's the benefit )?

I've never used the LAST_INSERT_ID with any parameter before...

UPDATE

My question is why one wants to simulate such kind of sequence this way, when it's automatically available out of the box by auto-incremented primary key?

score 1 · Answer 1 · answered Aug 04 '19 at 18:46

1

A use-case for generating the sequence without the use of AUTO_INCREMENT would be when the resultant ID value is used in more than one table, and it needs to be unique across all the tables. An AUTO_INCREMENT in each table could, most often would, result in the same value existing in each table.

Disregarding normal form requirements, an example could be from a classroom. Every exam given needs a unique ID, yet the exams are recorded in multiple tables: Pop-quizes, weekly tests, mid-term exams, and final exams. Using the OP-provided quote allows the generation of an ID sequence which can be used to be keep the ID unique across all the tables.

answered Aug 04 '19 at 18:46

Chindraba

820
1
18
19

Yes, upvoted. I'd add that the multiple tables may even be on different MySQL instances. Using this kind of pseudo-sequence, one can generate a value that will be unique across a whole set of instances. This is common when a very large dataset is distributed so a subset of the data is on each of a number of _shards_. – Bill Karwin Aug 04 '19 at 20:01
@BillKarwin At that level of distribution the concept of a central "sequence" would hold, but the use of `LAST_INSERT_ID(arg)` wouldn't server the purpose as it would not be guaranteed unique across instances. It would require a central server to "issue" the ID. – Chindraba Aug 04 '19 at 22:26
Yeah, that's what I mean. The app that wants to insert some data to one of the shards would have to first query the central MySQL instance to generate a unique ID, then use that value in a subsequent insert to the respective shard. – Bill Karwin Aug 05 '19 at 00:47

score 0 · Answer 2 · answered Aug 19 '11 at 03:42

0

Also quoted from here:

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

answered Aug 19 '11 at 03:42

Jonathan Hall

75,165
16
143
189

Why ?Primary key can increament automatically,isn't that way easier? – asker Aug 19 '11 at 03:47
The document offers at least one possible reason why: To emulate a sequence. – Jonathan Hall Aug 19 '11 at 03:53
Sequences seem to be the reasoning... Found this while searching... http://ronaldbradford.com/blog/last_insert_idexpr-the-lesser-known-usage-2007-09-12/ – PlagueEditor Aug 19 '11 at 03:54
Why does one want to emulate a sequence that way when it's already available out of the box by the autoincremented primary key? – asker Aug 19 '11 at 04:07
That's not a sequence, in the sense the documentation is talking about. – Jonathan Hall Aug 19 '11 at 04:16
This is yet another answer that doesn't answer what's asked about. – Je Rog Aug 19 '11 at 12:37
@Je Rog: Why don't you provide a better answer, then? – Jonathan Hall Aug 19 '11 at 17:42
@asker: It is a sequence, but in an entirely different context. – Jonathan Hall Aug 19 '11 at 17:43
Can you elaborate that context? – asker Aug 22 '11 at 03:29

score 0 · Answer 3 · answered Aug 19 '11 at 05:18

Query given above is update query and query is just changing the id which was in the row before. Its just updating a rows id so it is needed to update it like this way. Might be user want to change the index of the result row after some work on it. LAST_INSERT_ID will return the last id inserted. And this will be the case only if user want to change the index of the row from the previous one to the one which will be latest in result set.

When will we give an argument to LAST_INSERT_ID()?

3 Answers3