从 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 |
+------------------+
首先,让我们了解我们已经执行的代码。我们创建了两个表,即 cars
和 carModels
。
然后我们更改了 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 is a professional back-end developer as well as mobile developer. He is also open-source contributor by writing articles.
LinkedIn