MySQL 获取任何表的当前 AUTO_INCREMENT 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15821532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get current AUTO_INCREMENT value for any table
提问by bparise
How do I get the current AUTO_INCREMENT value for a table in MySQL?
如何获取 MySQL 中表的当前 AUTO_INCREMENT 值？
回答by methai
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';
回答by jvdub
I believe you're looking for MySQL's LAST_INSERT_ID()function. If in the command line, simply run the following:
我相信您正在寻找 MySQL 的LAST_INSERT_ID()函数。如果在命令行中，只需运行以下命令：
LAST_INSERT_ID();
You could also obtain this value through a SELECT query:
您还可以通过 SELECT 查询获取此值：
SELECT LAST_INSERT_ID();
回答by johnnyjohnny
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
您应该在底部看到 auto_increment
回答by Davideas
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).
尽管如果您手动运行查询，则methai的答案是正确的，但当 2 个并发事务/连接在生产中（例如）在运行时实际执行此查询时，就会出现问题。
Just tried manually in MySQL workbench with 2 connections opened simultaneously:
刚刚在 MySQL 工作台中手动尝试同时打开 2 个连接：
CREATE TABLE translation (
id BIGINT PRIMARY KEY AUTO_INCREMENT
);
# Suppose we have already 20 entries, we execute 2 new inserts:
Transaction 1:
交易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:
交易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'.
事务 1 的插入没问题：事务 2 的插入出错：错误代码：1062。键“PRIMARY”的重复条目“21”。
A good solution would be jvdub's answer because per transaction/connection the 2 inserts will be:
一个好的解决方案是jvdub的答案，因为每个事务/连接的 2 个插入将是：
Transaction 1:
交易1：
insert into translation (id) values (null);
21 = SELECT LAST_INSERT_ID();
Transaction 2:
交易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!
但是我们必须在插入后立即执行last_insert_id()！我们可以重用该 id 插入到其他需要外键的表中！
Also, we cannot execute the 2 queries as following:
此外，我们无法执行以下 2 个查询：
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!
因为我们实际上有兴趣在其他表中获取/重用该 ID 或返回！
回答by Murat Ba?ar
mysqli executable sample code:
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;
?>
回答by Mukul
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.
如果列在 sql server 中是自动递增的，那么要查看当前的自动递增值，如果要编辑该列的值，请使用以下查询。
-- to get current value
select ident_current('Table_Name')
-- to update current value
dbcc checkident ('[Table_Name]',reseed,"Your Value")
回答by Ji?í Chmiel
Query to check percentage "usage" of AUTO_INCREMENT for all tables of one given schema (except columns with type bigint unsigned):
查询以检查一个给定模式的所有表的 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;
回答by McRui
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).
我一直在寻找相同的方法，最终在 Helper 类中创建了一个静态方法（在我的例子中，我将其命名为 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:
要调用该方法并获取 MySql AUTO_INCREMENT 只需使用以下内容：
$auto_increment = \App\Helpers\Database::getAutoIncrementValue(env('DB_DATABASE'), 'your_table_name');
Hope it helps.
希望能帮助到你。
