How do I get the current AUTO_INCREMENT value for a table in MySQL?
9 Answers
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';
-
48Can also use
DATABASE()
instead of explicit database name. Commented Apr 4, 2013 at 20:58 -
25Note: DATABASE() is NULL if you have not issued a USE DATABASE command– methaiCommented Apr 5, 2013 at 14:13
-
9
-
61what if you deleted the last record? max won't give you the current auto_increment Commented May 5, 2015 at 14:41
-
9Does this give the latest AUTO_INCREMENT value in the table or what the nnext value is going to be?– sidxCommented Aug 19, 2015 at 6:50
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
-
13This doesn't seem to work if your table has no rows and auto_increment is at
1
.– PacerierCommented Feb 23, 2015 at 8:24
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();
-
5It's not the best idea to add one to it and expect, it will be the ID of next row. In the mean time another transaction could insert a new row and you would use the wrong ID right?– agimCommented Apr 4, 2013 at 21:11
-
You're right. I guess it depends on the environment you're running in. If it's a controlled test environment, then you could most likely add one and be safe. The best answer was provided by methai.– jvdubCommented Apr 4, 2013 at 21:18
-
2What the best answer is, depends on what you want to achieve. If you insert a new row and you want to know the created ID, then your answer is the best answer, since LAST_INSERT_ID() is transaction safe and ensures, you get the ID for the created object. I voted your answer up but I would delete the part with 'add one to it...'– agimCommented Apr 4, 2013 at 22:24
-
41LAST_INSERT_ID() is per connection. That means, if another process inserts three additional rows after you, YOUR LAST_INSERT_ID() call will be different than theirs. This question is asking for the current AUTO_INC val on the table itself.– methaiCommented Apr 5, 2013 at 14:08
-
2If the last INSERT statement inserted more than one row, this will give you the wrong answer. MySQL documentation (emphasis added): "LAST_INSERT_ID() returns a 64-bit value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement." Intuitive! dev.mysql.com/doc/refman/5.6/en/…– CharlieCommented Jun 12, 2015 at 22:04
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);
}
-
2Good explanation! thanks. It's now clear why would we want to use
last_insert_id()
– ImtiazCommented Sep 19, 2017 at 22:20 -
3This is an interesting scenario, but I don't think it actually answers the question which is to retrieve the
AUTO_INCREMENT
value.– SharlikeCommented Nov 21, 2018 at 21:38 -
-
Thanks @Davideas, now i'm understand that LAST_INSERT_ID for transaction more secure, And that Table Scheme use only when we need other table next increment number. Commented Sep 1, 2022 at 16:28
mysqli executable sample code:
<?php
$db = new mysqli("localhost", "user", "password", "YourDatabaseName");
if ($db->connect_errno) die ($db->connect_error);
$table=$db->prepare("SHOW TABLE STATUS FROM YourDatabaseName");
$table->execute();
$sonuc = $table->get_result();
while ($satir=$sonuc->fetch_assoc()){
if ($satir["Name"]== "YourTableName"){
$ai[$satir["Name"]]=$satir["Auto_increment"];
}
}
$LastAutoIncrement=$ai["YourTableName"];
echo $LastAutoIncrement;
?>
-
you can avoid the while Loop : $table=$db->prepare("SHOW TABLE STATUS FROM YourDatabaseName where Name = 'YourTableName' ") ; $values= $table->get_result(); return $values["Auto_increment"] ; Commented Dec 15, 2021 at 11:23
Query to check percentage "usage" of AUTO_INCREMENT for all tables of one given schema (except columns with type bigint unsigned):
SELECT
c.TABLE_NAME,
c.COLUMN_TYPE,
c.MAX_VALUE,
t.AUTO_INCREMENT,
IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) AS "Usage (%)"
FROM
(SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_TYPE,
CASE
WHEN COLUMN_TYPE LIKE 'tinyint(1)' THEN 127
WHEN COLUMN_TYPE LIKE 'tinyint(1) unsigned' THEN 255
WHEN COLUMN_TYPE LIKE 'smallint(%)' THEN 32767
WHEN COLUMN_TYPE LIKE 'smallint(%) unsigned' THEN 65535
WHEN COLUMN_TYPE LIKE 'mediumint(%)' THEN 8388607
WHEN COLUMN_TYPE LIKE 'mediumint(%) unsigned' THEN 16777215
WHEN COLUMN_TYPE LIKE 'int(%)' THEN 2147483647
WHEN COLUMN_TYPE LIKE 'int(%) unsigned' THEN 4294967295
WHEN COLUMN_TYPE LIKE 'bigint(%)' THEN 9223372036854775807
WHEN COLUMN_TYPE LIKE 'bigint(%) unsigned' THEN 0
ELSE 0
END AS "MAX_VALUE"
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA LIKE '%auto_increment%'
) c
JOIN INFORMATION_SCHEMA.TABLES t ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)
WHERE
c.TABLE_SCHEMA = 'YOUR_SCHEMA'
ORDER BY
`Usage (%)` DESC;
-
An excellent option, I would also take into account the moment when the specified length may be missing in
WHEN COLUMN_TYPE LIKE
conditions, and the brackets are superfluous in these queries.– wnullCommented Mar 1, 2023 at 20:08
I was looking for the same and ended up by creating a static method inside a Helper class (in my case I named it App\Helpers\Database).
The method
/**
* Method to get the autoincrement value from a database table
*
* @access public
*
* @param string $database The database name or configuration in the .env file
* @param string $table The table name
*
* @return mixed
*/
public static function getAutoIncrementValue($database, $table)
{
$database ?? env('DB_DATABASE');
return \DB::select("
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '" . env('DB_DATABASE') . "'
AND TABLE_NAME = '" . $table . "'"
)[0]->AUTO_INCREMENT;
}
To call the method and get the MySql AUTO_INCREMENT just use the following:
$auto_increment = \App\Helpers\Database::getAutoIncrementValue(env('DB_DATABASE'), 'your_table_name');
Hope it helps.
list dbname all tabename and AUTO_INCREMENT
SELECT `AUTO_INCREMENT`,`TABLE_NAME`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbname' ORDER BY AUTO_INCREMENT desc
If column is autoincremented in sql server then to see the current autoincremented value, and if you want to edit that value for that column use the following query.
-- to get current value
select ident_current('Table_Name')
-- to update current value
dbcc checkident ('[Table_Name]',reseed,"Your Value")