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);
}