从 MySQL 中的表中查找最后插入的 ID

Subhan Hagverdiyev 2023年1月30日 2022年5月13日
  1. MySQL 中的 AUTO_INCREMENT ID 是什么
  2. 在 MySQL 中查找 AUTO_INCREMENT 列的最后插入 ID
从 MySQL 中的表中查找最后插入的 ID

许多关于在 MySQL 中查找一个表的最后插入 ID 的文章。但是,很难找到任何关于使用两个表查找最后插入的 ID 的教程。

在本文中,我们将尝试解决这种特殊情况,当然,我们还将探讨何时使用 LAST_INSERT_ID() 命令。首先,让我们看看 AUTO_INCREMENT 是什么 ID 以及它与 LAST_INSERT_ID() 的关系。

MySQL 中的 AUTO_INCREMENT ID 是什么

在处理数据库时,我们通常需要特定表的 ID 来作为主键。因为主键标识数据库中的记录,所以它必须是唯一的。

但是,我们如何保证主键始终是唯一的呢?另一种选择是使用一个公式来构造主键,该公式在添加数据之前验证该键在数据库中的存在。

这种策略可能会奏效,但它是困难的,而且并非万无一失。我们可以利用 MySQL 的 AUTO_INCREMENT() 函数来创建主键以避免这种复杂性并确保它们始终是唯一的。

对于 INT 数据类型,采用自动增量。INT 数据类型有符号和无符号都支持值。

无符号数据类型只能存储正数。当新记录输入数据库时​​,自动增量允许自动创建唯一编号。

让我们看一个例子。

CREATE TABLE cars (
     carID INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (carID)
);

在我们现在创建表之后,我们可以插入值。我们也不需要为 carID 列指定值。

INSERT INTO cars (name)
VALUES ('BMW'),('Mercedes'),('Audi');
SELECT * FROM cars;

输出:

+----+---------+
| carID | name |
+----+---------+
|  1 | BMW     |
|  2 | Mercedes|
|  3 | Audi    |
+----+---------+

默认情况下,它将以 1 作为起始值,递增 1。如果我们需要从另一个值开始,我们可以更改新的值表。

ALTER TABLE cars AUTO_INCREMENT=32;

最后,我们在定义自增数字的数据类型时需要小心。例如,如果我们将其设置为 TINYINT,它将限制记录数为 255。

我们可以使用 INT 数据类型,因为它的限制足够大。所以现在我们对 auto_increment 函数了解很多,我们可以继续 last_insert_id() 函数。

在 MySQL 中查找 AUTO_INCREMENT 列的最后插入 ID

由于最近执行的 INSERT 语句,LAST_INSERT_ID 返回为 AUTO_INCREMENT 列插入的第一个生成的 int。如果插入失败,则最后插入的 ID 的值不会改变。

我们将为上面创建的表执行它。

SELECT LAST_INSERT_ID();

输出:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

如你所见,即使 INSERT 语句在 cars 表中插入了三行,成功插入的第一行也是 ID=1 的行。假设我们需要 insert 语句中的最后一个 ID(在本例中为:3)。

我们将利用 MySQL 的 max 函数。它返回当前特定列的最大值。

SELECT MAX( carID ) FROM cars;

输出:

+-----------+
| MAX(carID)|
+-----------+
|         3 |
+-----------+

现在我们来分析另一种情况,我们是在一张一张的往两张表中插入数据的时候,我们需要得到第一张表最后插入的 ID。

CREATE TABLE cars (
     carID INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (carID)
);

CREATE TABLE carModels (

         otherID INT NOT NULL ,
          modelID INT NOT NULL AUTO_INCREMENT,
          name CHAR(30) NOT NULL,
           PRIMARY KEY (modelID)
);

ALTER TABLE carModels AUTO_INCREMENT=4;
INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
select LAST_INSERT_ID();

输出:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

首先,让我们了解我们已经执行的代码。我们创建了两个表,即 carscarModels

然后我们更改了 carModels 的起始 ID,以区分两个表的 ID,并使用我们上面学习的方法将其设置为 4。我们可以从输出中看到它返回了第二个表的最后插入的 ID。

如果我们需要最后插入的 cars 表 ID 怎么办?

根据具体情况,我们可以通过多种方式做到这一点。我们将根据我们的情况来查看它们中的每一个。

在 MySQL 中第一种方法:将 ID 存储在变量中

在这种方法中,我们会将第一个表的最后插入的 ID 存储在一个变量中,然后在第二个表中使用它,而不是直接调用 LAST_INSERT_ID

INSERT INTO cars (name) VALUES ('BMW');
SET @last_id_in_cars = LAST_INSERT_ID();
INSERT INTO carModels (otherID,name) VALUES (@last_id_in_cars,'F30');
SELECT @last_id_in_cars;

输出:

+------------------+
| @last_id_in_cars |
+------------------+
|               33 |
+------------------+

在选择变量之前,我们需要使用@符号。

第二种方法:从 MySQL 中的第二个表中检索最后插入的 ID

这是我们示例的一个特殊情况,因为我们在第二个表中使用了第一个表的最后插入 ID。所以我们将从第二个表中检索它。

INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
SELECT otherID from carModels where modelID = LAST_INSERT_ID();

cars 表中获取 MAX(carID)

我们已经学习了函数 max 并且可以在这个例子中使用它。

INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
SELECT MAX(carID) FROM cars;

但是,高级工程师不推荐这种方法,因为它会在竞态条件下出​​错。当多个线程可以同时访问共享数据时,就会出现竞争条件。

Subhan Hagverdiyev avatar Subhan Hagverdiyev avatar

Subhan is a professional back-end developer as well as mobile developer. He is also open-source contributor by writing articles.

LinkedIn