從 MySQL 中的表中查詢最後插入的 ID

Subhan Hagverdiyev 2023年1月30日
  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