CODEWITHSUNDEEP
mysqlauto-increment
bparise
bparise

Reputation: 3999

Get current AUTO_INCREMENT value for any table

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

Upvotes: 391

Views: 445617

Answers (9)

seqwait
seqwait

Reputation: 199

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

Upvotes: 2

Davideas
Davideas

Reputation: 3276

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

Upvotes: 11

Jiří Chmiel
Jiří Chmiel

Reputation: 876

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;

Upvotes: 5

McRui
McRui

Reputation: 1931

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.

Upvotes: 2

Murat Başar
Murat Başar

Reputation: 69

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

Upvotes: 5

Mukul
Mukul

Reputation: 45

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

Upvotes: 0

methai
methai

Reputation: 9133

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

Upvotes: 731

johnnyjohnny
johnnyjohnny

Reputation: 391

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

Upvotes: 39

jvdub
jvdub

Reputation: 927

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

Upvotes: 20

Related Questions