Take AUTO_INCREMENT value from a table

Asked

Viewed 3,829 times

6

I want to take the last record registered in a database table, ie the value of AUTO_INCREMENT from the table, I tried using:

SELECT MAX(id) as max FROM people

It works, but if I have no record in the table it will return NULL, what may be wrong, because not always that return NULL the table is new, for example:

Create 5 record in the table, and right after that I delete the 5, mine query will return the NULL while should return the 5 (which was the last to be registered, regardless of whether it was deleted or not).

I read about the lastInsertId() of PDO, but in the examples, it is always necessary to execute a query of INSERT before the lastInsertId().

What is the best way to get the current value of AUTO_INCREMENT of a table at any time?

PS:. When I say 'at any time' I mean that you don’t need to enter, update or delete a record before you can pick it up.

php mysql sql
  • 1

    take a look at this: http://stackoverflow.com/questions/6761403/how-to-get-the-next-auto-increment-id-in-mysql

  • So you don’t want to get the id of the last record, but the current value of your right key?

  • That’s almost always a problem. Once obtained the value and stored in a variable, how do you prevent it to change externally and not collide?

  • @Bacco I was also thinking about it, I think you will not have problem, because the time period that this variable is used is extremely short, use when I insert a data in the database, then the last to store as class id.

  • @Fleuquer File yes, soon also it will be the same as the last registered, even if the last registered by chance tries to be deleted.

  • 1

    There is no right or wrong, there is no right: https://pt.wikipedia.org/wiki/Condi%C3%A7%C3%A3o_de_corrida

  • @Bigown did not know this 'Race Condition', I read the article and researched more on the subject, I analyzed the code used and I believe that in the case will not occur such failure, because there are no simultaneous processes in the case, therefore, 'I think' that it will not occur. Thank you for quoting, one more knowledge :)

  • Is there a mechanism to ensure that there will be no simultaneous processes? Especially in PHP I don’t usually see this.

  • I did it just waiting for the result of one, until it has that result it will not go to the other process. I always do it just like this, I don’t know if there’s any other way to avoid it.

Show 4 more comments

2 answers

13


Make an inquiry on information_schema it stores information about your database(metadata), the field to be returned is the AUTO_INCREMENT, it is necessary to inform the table and database.

The code lowers returns the next value auto-increment, if the last entered record was id 200, the query will return 201.

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_name = 'tabela' AND table_schema = 'database' ;

Based on: How to get the next auto-increment id in mysql

  • 2

    Obs: only add a -1 to catch the last entry.

  • 1

    It is not just adding a -1 to get the last registered element, this statement does not proceed. You can be with the auto_increment in 200 and not having a 199 on the table. Both because the record 199 has been deleted, and a new table created with autoincrement in 1000, for example.

  • @Bacco I will correct later, it makes sense the comment in a broken sequence :P the logic breaks tbm.

  • @rray I think the hole is even further down, vide comment I left in the question :)

  • PS: in the case, apart from the -1, I think it is right

  • 1

    In the case -1 would be useful, because the objective was not to take the indicator of the last registered record still existing, but the latter regardless of whether it was deleted or not. But each case is a case, it is valid to highlight the issue of -1 for visitors.

Show 1 more comment

0

use like this: 

select coalesce( max( id ), 0) + 1 from tabela

  • Tested here, returned 1, when should have returned 8.

Browser other questions tagged php mysql sql

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