Get current AUTO INCREMENT value for any table

Question

How do I get the current AUTO INCREMENT value for a table in MySQL

User · Answer

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

User · Answer

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  gt  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

User · Answer

mysqli executable sample code    lt  php      db   new mysqli  localhost    user    password    YourDatabaseName        if   db- gt connect errno  die   db- gt connect error         table  db- gt prepare  SHOW TABLE STATUS FROM YourDatabaseName         table- gt execute         sonuc    table- gt get result        while   satir  sonuc- gt fetch assoc             if   satir  Name      YourTableName                 ai  satir  Name     satir  Auto increment                         LastAutoIncrement  ai  YourTableName        echo  LastAutoIncrement    gt

User · Answer

you can also use this if you know the name of the primary key SELECT     MAX primary key name    1 FROM     TABLE NAME

User · Answer

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

User · Answer

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

User · Answer

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 - gt 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

User · Answer

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

User · Answer

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

[mysql] Get current AUTO_INCREMENT value for any table

Examples related to mysql

Examples related to auto-increment