我如何获得当前的AUTO_INCREMENT值为一个表在MySQL?


当前回答

你可以使用这个查询来获取所有的表数据:

SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE 'TableName' ;

你可以通过使用这个查询来获得确切的信息:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

其他回答

Mysqli可执行示例代码:

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

列出dbname所有tabename和AUTO_INCREMENT

SELECT `AUTO_INCREMENT`,`TABLE_NAME`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbname' ORDER BY AUTO_INCREMENT desc

如果列在sql server中是自动递增的,那么要查看当前自动递增的值,如果您想编辑该列的值,请使用以下查询。

-- to get current value
select ident_current('Table_Name')

-- to update current value
dbcc checkident ('[Table_Name]',reseed,"Your Value")

你可以使用这个查询来获取所有的表数据:

SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE 'TableName' ;

你可以通过使用这个查询来获得确切的信息:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

查询一个给定模式下的所有表的AUTO_INCREMENT的“使用率”百分比(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;