Get current AUTO_INCREMENT value for any table

Question

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

Answer 1

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!

Answer 2

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")

Answer 3

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

Answer 4

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;
?>  

Answer 5

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';

Answer 6

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