Get current AUTO_INCREMENT value for any table
MysqlAuto IncrementMysql Problem Overview
How do I get the current AUTO_INCREMENT value for a table in MySQL?
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
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 - Mysql
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 - Mysql
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);
}
Solution 5 - Mysql
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;
?>
Solution 6 - Mysql
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;
Solution 7 - Mysql
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.
Solution 8 - Mysql
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
Solution 9 - Mysql
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")