know the auto_increment of a MySQL table after deleting the last ID's?


Friends, I come to you to know who tells me how to know the auto_increment of a table.

Suppose that id_campo has 30 values, but after 6 rows is deleted, it would be in id_campo (24) , but the auto_increment is at 31.

Is there a way to get that auto_increment(31) with SQL query without having to create another table that saves the last value auto_increment of the main table?

asked by Angel Zambrano 24.10.2017 в 21:00

2 answers


You can execute the following query.

WHERE TABLE_SCHEMA = 'Nombre de Base de Datos'
AND   TABLE_NAME   = 'Nombre de Tabla';
answered by 24.10.2017 / 21:04

If you want to get the last value of a self-incremental field, not the value that is in memory in information_schemes, it would be better to use the query that does the same InnoDB .


  • Table: r_categoria
  • Column auto_increment: id_categoria

We execute this query:

SELECT MAX(id_categoria) ultimo FROM r_categoria; 

The query would return the id of the last existing value.

If you want to get the value of an upcoming new value, it would only be a matter of adding one:

SELECT MAX(id_categoria)+1 ultimo FROM r_categoria; 

In that case, this query will be more performant than having to read the INFORMATION.SCHEMES , it acts directly on the table.

According to the documentation , that's the way that InnoDB initializes the auto-incremental counter:


InnoDB AUTO_INCREMENT Counter Initialization


If you specify a column AUTO_INCREMENT for a table InnoDB , the   table handler in the data dictionary InnoDB contains a   special counter called automatic increment counter that is used   to assign new values for the column. This counter is stored   only in main memory, not on disk.


To initialize an automatic increment counter after   restart the server, InnoDB executes the equivalent of the following   instruction in the first insertion in a table containing a   column AUTO_INCREMENT .

SELECT MAX (ai_col) FROM table_name FOR UPDATE;

InnoDB increases the value retrieved by the instruction and assigns it to   the column and the automatic increment counter for the table. Of   By default, the value is incremented by 1. This value   default can be overridden by configuration settings    auto_increment_increment .

answered by 24.10.2017 в 21:27