Get current AUTO_INCREMENT value for any table

How do I get the current AUTO_INCREMENT value for a table in MySQL?

Solution 1:

You can get all of the table data by using this query:

SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE 'TableName' ;

You can get exactly this information by using this query:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

Solution 2:

If you just want to know the number, rather than get it in a query then you can use:

SHOW CREATE TABLE tablename;

You should see the auto_increment at the bottom

Solution 3:

I believe you're looking for MySQL's LAST_INSERT_ID() function. If in the command line, simply run the following:

LAST_INSERT_ID();

You could also obtain this value through a SELECT query:

SELECT LAST_INSERT_ID();

Solution 4:

Even though methai's answer is correct if you manually run the query, a problem occurs when 2 concurrent transaction/connections actually execute this query at runtime in production (for instance).

Just tried manually in MySQL workbench with 2 connections opened simultaneously:

CREATE TABLE translation (
  id BIGINT PRIMARY KEY AUTO_INCREMENT
);
# Suppose we have already 20 entries, we execute 2 new inserts:

Transaction 1:

21 = SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'translation';

insert into translation (id) values (21);

Transaction 2:

21 = SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'translation';

insert into translation (id) values (21);

# commit transaction 1;
# commit transaction 2;

• Insert of transaction 1 is ok
• Insert of transaction 2 goes in error: Error Code: 1062. Duplicate entry '21' for key 'PRIMARY'.

A good solution would be jvdub's answer because per transaction/connection the 2 inserts will be:

Transaction 1:

insert into translation (id) values (null);
21 = SELECT LAST_INSERT_ID();

Transaction 2:

insert into translation (id) values (null);
22 = SELECT LAST_INSERT_ID();

# commit transaction 1;
# commit transaction 2;

But we have to execute the last_insert_id() just after the insert! And we can reuse that id to be inserted in others tables where a foreign key is expected!

Also, we cannot execute the 2 queries as following:

insert into translation (id) values ((SELECT AUTO_INCREMENT FROM
    information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()
    AND TABLE_NAME='translation'));

because we actually are interested to grab/reuse that ID in other table or to return!

EDIT: 02/08/2021, Retrieving the value

To actually retrieve the value in the application (example done in Java with MyBatis):

@Mapper
public interface ClientMapper {
    @Select("select last_insert_id()")
    Long getLastInsertedId();

    @Insert({...})
    void insertClientInfo(ClientInfo client);
}

And then in Repository

public void insertClientInfo(ClientInfo clientInfo) {
    mapper.insertClientInfo(clientInfo);
    Long pk = mapper.getLastInsertedId();
    clientInfo.setId(pk);
}