如何在mysql中获取下一个自增id

Question

如何在mysql中获取下一个id并将其插入表中

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("sahf4d2fdd45", id))

- faressoft

---

使用一个 auto_increment 列。 - knittl

你想要“下一个”编号还是当前插入行的编号？也就是说，支付代码末尾的编号应该是存储支付代码的行的编号吗？ - Mike

在这种情况下，当你检索这些值时将它们连接起来，而不是在插入它们时这样做。这样做更容易，而且可以避免获取错误的ID或者必须运行更新 - 想象一下，如果另一个客户端在更新运行之前请求了您刚插入的行，该怎么办：客户端将得到无效的付款代码。 在低流量系统上，可能不会发生这种情况，但我认为冒这个风险没有意义。 - Mike

正如@binaryLV所指出的那样，资源锁定也可能解决这个问题。请参见：http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html - Mike

如果从表中删除最近的记录，则使用MAX的所有其他答案都将失败。另一方面，大多数数据库设置会锁定information_schema，因此@ravi404的答案可能会失败，但就稳健性而言，它是迄今为止最好的答案。 - Peter Chaula

Answer 1

您可以使用

SET information_schema_stats_expiry = 0;
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'table_name'
AND table_schema = DATABASE( );

默认的information_schema_stats_expiry值为86400，所以如果没有第一个语句（每个会话只需要一次），这种方法可能会返回过期的数据。

或者，您可以使用此查询，值将在Auto_increment列中，但还会返回其他附加数据：

SHOW TABLE STATUS LIKE 'table_name';

- ravi404

---

@GerardONeill 已将其删除 - ravi404

由于缓存用于从information_schema.tables检索数据，因此对于MySQL 8，该解决方案不再起作用。 - Bruno

@Bruno，你可以发布一个参考文献吗？ - mvorisek

此官方文档说明 TABLES.AUTO_INCREMENT 被缓存。MySQL 博客也有多篇文章提到此事，但他们所提到的系统变量似乎已过时。 - Bruno

第二个查询确实会返回下一个自增值，但它被缓存了（默认缓存持续时间为24小时）。要获取最新的自增值，只需在查询之前运行 SET information_schema_stats_expiry = 0;。 - aetonsi

Answer 2

您可以通过以下方式获取下一个自增值：

SHOW TABLE STATUS FROM tablename LIKE Auto_increment
/*or*/
SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'tablename'

请注意，不应使用这种方法来更改表格，而应使用自动增量列来自动执行此操作。
问题在于last_insert_id()是回顾性的，并且因此在当前连接中可以得到保证。
该方法是前瞻性的，因此不能保证在每个连接中都是唯一的，不能依赖它。
只有在单个连接数据库中才有效，在单个连接数据库今天可能会变成多个连接数据库。
请参阅：SHOW TABLE STATUS。

- Johan

---

我没有给它点踩，但是试图使用最后一个自动递增的值存在一个问题，那就是在你使用它的时候，它可能已经不是最新的了，无论SELECT和接下来的INSERT有多快。 - Mike

@binaryLV：SELECT和INSERT之间的差距越小，值发生改变的可能性就越小，但并不能完全排除。想象一下一个每分钟有数千次数据库访问量的系统-值发生改变的可能性会大大增加。如果将其作为单个查询执行，则表或行锁定可以防止此情况，但这依赖于数据库引擎的特定行为，可能没有很好的文档记录。如果必须的话，我会选择随后的“UPDATE”。但我宁愿在显示时间将两者连接起来，省去所有麻烦。 - Mike

基本上需要查看下一个自动增量将是什么。这是最佳选项，因为我还没有插入任何东西！只需确保在调用并稍后使用它之前（当然，在解锁后），锁定将插入记录的表！ 例如：LOCK TABLES t1 READ; - Bretticus

SHOW TABLE STATUS 命令需要在 FROM 后面跟上 数据库名 ，并在 LIKE 后面跟上 '表名模式'。该命令用于查看数据库中表的状态信息。 - x-yuri

由于缓存用于从information_schema.tables检索数据，因此该解决方案对于MySQL 8不再起作用。 - Bruno

Answer 3

这将返回MySQL数据库的自增值，我没有在其他数据库上进行验证。请注意，如果您正在使用任何其他数据库，则查询语法可能会不同。

SELECT AUTO_INCREMENT 
FROM information_schema.tables
WHERE table_name = 'your_table_name'
     and table_schema = 'your_database_name';

SELECT AUTO_INCREMENT 
FROM information_schema.tables
WHERE table_name = 'your_table_name'
     and table_schema = database();

- Chaminda Bandara

---

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'your_table_name' and table_schema = 'your_database_name'; 查询数据库中指定表名的自增ID。 - LJay

Answer 4

顶部的答案使用了 PHP MySQL_ 解决方案，但我想分享一个更新的 PHP MySQLi_ 解决方案来实现这个。在这个示例中没有错误输出！

$db = new mysqli('localhost', 'user', 'pass', 'database');
$sql = "SHOW TABLE STATUS LIKE 'table'";
$result=$db->query($sql);
$row = $result->fetch_assoc();

echo $row['Auto_increment'];

将表中即将出现的下一个自增值删除。

- Hexchaimen

---

由于缓存用于从information_schema.tables检索数据，因此该解决方案对于mysql 8不再起作用。 - Bruno

Answer 5

在 PHP 中，您可以尝试以下操作：

$query = mysql_query("SELECT MAX(id) FROM `your_table_name`");
$results = mysql_fetch_array($query);
$cur_auto_id = $results['MAX(id)'] + 1;

或者

$result = mysql_query("SHOW TABLE STATUS WHERE `Name` = 'your_table_name'");
$data = mysql_fetch_assoc($result);
$next_increment = $data['Auto_increment'];

- Naresh Jain

---

请记住，如果使用第一种方法删除了具有最高id的记录，则会创建一个新记录，并使用该已删除记录曾经拥有的id。 - Tom Jenkinson

如果先前的键在其他表中使用并仍然保留在那里，则可能会出现非常奇怪的魔法。 - Tebe

如果您删除最后插入的记录，则第一个将返回错误的ID。 - Ali Parsa

如果从表中删除记录，则会返回错误的ID。 - Huthaifah Kholi

@Tom Jenkinson，我不太确定这一点。实际上，如果您删除表中的所有行，则自动增量将从以前的值继续？我正在重新创建一个表以对它们进行重新编号。 - mckenzm

用第一种方法呢？ - Tom Jenkinson

Answer 6

解决方案：

CREATE TRIGGER `IdTrigger` BEFORE INSERT ON `payments`
  FOR EACH ROW
BEGIN

SELECT  AUTO_INCREMENT Into @xId
    FROM information_schema.tables
    WHERE 
    Table_SCHEMA ="DataBaseName" AND
    table_name = "payments";

SET NEW.`payment_code` = CONCAT("sahf4d2fdd45",@xId);

END;

"DataBaseName" 是我们数据库的名称。

- Angel

Answer 7

使用SQL查询中的LAST_INSERT_ID()。

或者

您也可以使用PHP的mysql_insert_id()来获取它。

- Sarfraz

---

问题是关于下一个ID而不是最后一个ID，你提到了LAST_INSERT_ID()函数。请注意修改翻译内容以符合此要求。 - Felipe Pereira

如果在表中删除了行，那么最大值将会出错。 - Peter Chaula

仅供参考，自2017年起mysql*函数已被弃用，可以使用mysqli_insert_id来完成上述操作。 - treyBake

谁标记了这个作为答案？问题是关于下一个插入ID而不是最后一个。 - Amit Shah

Answer 8

一条简单的查询语句即可：SHOW TABLE STATUS LIKE 'table_name'

- Raa

Answer 9

对于MySQL 8，使用SHOW CREATE TABLE来检索下一个自增插入id：

SHOW CREATE TABLE mysql.time_zone

结果：

CREATE TABLE `time_zone` (
  `Time_zone_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1784 DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Time zones'

请查看返回的查询结果的最后一行AUTO_INCREMENT=1784.

与插入的最后一个值进行比较: select max(Time_zone_id) from mysql.time_zone

结果:

+-------------------+
| max(Time_zone_id) |
+-------------------+
|              1783 |
+-------------------+

在MySQL v8.0.20上进行了测试。

- Alexeyer

---

很高兴看到使用select max()，对于一个可能被索引且可能在缓存中的值来说是非常好的。 - mckenzm

使用max()函数来获取下一个自增id是错误的。我只是将它放在这里进行结果比较。 - Alexeyer

Answer 10

SELECT id FROM `table` ORDER BY id DESC LIMIT 1

尽管我怀疑它的生产力，但它是100%可靠的。

- Tebe

---

我也同意这是最简单的方法。不确定为什么你被踩了，但我会用赞成来抵消它。 - recurse

我并没有提到事务的必要性，如果你不将其包装在事务中，那么这段代码会很糟糕，一旦遇到真实负载就会显现出来。 - Tebe

如果最新的行被删除了怎么办？或者多个最新的行被删除了呢？ - Liam W

非问题，释放的键只有在您明确指定时才会被使用。 - Tebe