Get AUTO_INCREMENT value from a table

Navigation
6

I want to get the last record entered in a database table, that is, the value of AUTO_INCREMENT of the table, I tried using: 

SELECT MAX(id) as max FROM people

It works, but if I do not have any records in the table it will return NULL , which can be wrong, because not every time you return NULL the table is new, for example:

  

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

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

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

PS:. When I say 'anytime' I mean that you do not have to insert, update or delete a record before you can pick it up.     

asked by anonymous 06.07.2016 / 19:10

2 answers

13

Make an appointment at information_schema it stores information about your bank of data (metadata), the field to be returned is AUTO_INCREMENT , it is necessary to inform the table and database.

The code below returns the next value of the auto-increment, if the last record entered 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

    
06.07.2016 / 19:29
0

use like this: 

select coalesce( max( id ), 0) + 1 from tabela
    
06.07.2016 / 19:24
How to calculate Euclidean distance Problem to remove element added with jQuery