如何在 MySQL 中插入還不存在的行

Rayven Esplanada 2023年1月30日 2020年11月7日
  1. 在 MySQL 中使用 REPLACE 來插入還不存在的記錄
  2. 在 MySQL 中使用 INSERT IGNORE 來插入如果不存在的資訊
  3. 在 MySQL 中使用 INSERT ... ON DUPLICATE KEY UPDATE 插入如果不存在的記錄
如何在 MySQL 中插入還不存在的行

本教程告訴你如何在 mySQL 中插入一條還不存在的記錄到表中。

有三種簡單的方法可以完成這個問題,通過使用 REPLACEINSERT IGNORE 或者 INSERT ... ON DUPLICATE KEY UPDATE

首先,我們想象一下,我們有一個表 person,結構是這樣的。

CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
address VARCHAR(128) 
);

有以下現有記錄。

id name age address
1 John Doe 27 New York, New York
2 Jane Doe 26 Berlin, Germany
3 Chun Li 24 Beijing, China
4 Thor Odinson 40 Asgard

現在讓我們進入實際的解決方案。

在 MySQL 中使用 REPLACE 來插入還不存在的記錄

REPLACE 的作用是,當發現現有記錄時,它將覆蓋現有記錄;如果它還不存在,它將插入一條新的記錄。它本質上和 UPDATE 一樣,但具有 INSERT 功能。

讓我們提供一個示例查詢,對 John Doe 執行 REPLACE

REPLACE INTO 'person'
SET id = 1,
name = 'John Doe'`,
age = 28,
address = 'Los Angeles, California'

如果 John Doe 存在,他將被新資料覆蓋;如果他不存在,將為 John Doe 建立一個新條目。

然而,這種方法對於插入來說並不高效,因為它覆蓋了現有的記錄,而不是在發現時直接跳過。雖然它能完成工作,但並不是最好的方法。

當查詢選擇 John Doe 時,新的結果將是這樣的。

id name age address
1 John Doe 28 Los Angeles, California

John Doe 的年齡和地址已經被 REPLACE 指令碼修改或更新。

在 MySQL 中使用 INSERT IGNORE 來插入如果不存在的資訊

我們在上面的表上也會使用同樣的未編輯資料來進行。

INSERT IGNORE 上,如果記錄的主鍵已經存在於資料庫中,則會被悄悄忽略或跳過。

如果我們把 IGNORE 關鍵字去掉,插入一條 id 或主鍵已經存在的記錄,會中止查詢,並顯示錯誤資訊,說主鍵的記錄已經存在。

INSERT 之後,IGNORE 則是代替了因為錯誤而中止,而只是繼續查詢,雖然沒有插入新記錄。

INSERT IGNORE person
SET 'id' = 4,
age = 1000,
address = 'Chicago'

根據我們上面宣告的表,已經存在一條 id 或主鍵 4 的記錄(Thor Odinson)。因此,這個查詢被指令碼忽略,從而沒有在資料庫中更新或插入一條記錄。

如果我們在中查詢 Thor,結果將是。

id name age address
4 Thor Odinson 40 Asgard

查詢中的年齡和地址完全沒有影響到 id 為 4 的記錄。

在 MySQL 中使用 INSERT ... ON DUPLICATE KEY UPDATE 插入如果不存在的記錄

如果你使用 ON DUPLICATE KEY UPDATE 子句,並且你要插入的行是 UNIQUE 索引或主鍵中的重複,該行將執行 UPDATE

這本質上和 REPLACE 做的事情是一樣的。

比方說,我們要插入一條 id 為 2 的記錄。

INSERT INTO person (id, name, age, address)
VALUES (1, 'Jane Deer', '33', 'Tallahassee Florida')
ON DUPLICATE KEY UPDATE id = id+1;

id 是遞增的,以生成一個新的記錄,而不是更新現有的記錄。

如果我們查詢 person 的所有記錄,那麼結果會是這樣。

id name age address
1 John Doe 27 New York, New York
2 Jane Doe 26 Berlin, Germany
3 Chun Li 24 Beijing, China
4 Thor Odinson 40 Asgard
5 Jane Deer 33 Tallahasee, Florida

Jane Deer, 不是取代 Jane doe, 而是在表中增加了一條新記錄。

處理插入和檢查它的存在,最有效的方法肯定是使用 INSERT IGNORE

不過,另外兩個關鍵字也是可以根據具體情況使用的,如果有一天你想更新你要插入的行是否存在,就使用 REPLACE。如果你不想修改行,但真的想插入新的記錄,使用 INSERT ... ON DUPLICATE KEY UPDATE

Rayven Esplanada avatar Rayven Esplanada avatar

Skilled in Python, Java, Spring Boot, AngularJS, and Agile Methodologies. Strong engineering professional with a passion for development and always seeking opportunities for personal and career growth. A Technical Writer writing about comprehensive how-to articles, environment set-ups, and technical walkthroughs. Specializes in writing Python, Java, Spring, and SQL articles.

LinkedIn