Recover last row inserted in table with non-sequential primary key - Mysql

Asked

Viewed 139 times

4

It is possible to know which was the last row inserted in a Mysql table where the primary key is not sequential?

I have a table where the primary key consists of two columns that are FK, so they don’t follow a sequence.

When executed the query select last_insert_id(); is returned 0 (makes sense, why no ID was entered even).

The best output is to put an ID column even?

mysql sql query select
  • 1

    I could be wrong but I think the ID seems more appropriate.

  • 1

    If this ID will not impact the use of your PK, it is the best idea. Another possibility would be to include a field of type Datetime with DEFAULT CURRENT_TIMESTAMP (to get the last one is only to do the descending ordering) but this is not the best solution, mainly from the point of view of query performance.

  • 1

    Yes, I believe that a self-supporting column is the best solution msm thinking about ease to solve the problem and performance. Thank you!

  • 1

    If it is to avoid the ID, even worse by automatic date. Only comes out more "expensive", and depends on the machine clock, and does not support competition. ID does not have these problems, and just see the biggest.

1 answer

1


Add a column with the insertion date:

ALTER TABLE tabela ADD insercao DATETIME DEFAULT CURRENT_TIMESTAMP;

Or an auto increment id:

ALTER TABLE tabela ADD id INTEGER NOT NULL AUTO_INCREMENT;

  • Yeah, I got the second option anyway, I believe I’m the best for the performance. Thank you!

Browser other questions tagged mysql sql query select

You are not signed in. Login or sign up in order to post.