在 MySQL 表中使用重複鍵驗證插入值
-
使用
ON DUPLICATE KEY更新方法在具有重複鍵驗證的 MySQL 表中插入值 -
使用
REPLACE方法在具有重複鍵驗證的 MySQL 表中插入值 -
使用
IGNORE方法在具有重複鍵驗證的 MySQL 表中插入值
傳統 SQL 的 INSERT 語句不會針對現有資料庫表執行其引數/值的輸入驗證。在插入過程中發現重複鍵時，有時會導致錯誤。
這在 MySQL 中通過 INSERT 的擴充套件與 ON DUPLICATE KEY UPDATE、REPLACE 和 IGNORE 等語句進行處理。
為了說明這些方法，讓我們建立一個名為 programming_languages 的示例資料庫。
-- Here goes the definition of the database
CREATE DATABASE programming_languages;
USE programming_languages;
-- Creating a table
CREATE TABLE Details(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(25) UNIQUE, -- Making name column unique
year_released VARCHAR (5),
PRIMARY KEY(id)
);
輸出：
20:22:03 CREATE DATABASE programming_languages 1 row(s) affected 0.219 sec
20:22:03 USE programming_languages 0 row(s) affected 0.000 sec
20:22:03 CREATE TABLE Details(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(25) UNIQUE, year_released VARCHAR (5),PRIMARY KEY(id)) 0 row(s) affected 0.625 sec
現在，詳細資訊表將填充如下值。
-- Names of popular programming languages and their release year
INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java', 1995);
SELECT * FROM Details ORDER BY id; -- Previewing table
輸出：
id name year_released
1 python 1991
2 c++ 1985
3 Java 1995
-----------------------------------------------------------------------------------------
20:23:24 INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java',1995) 3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0 0.109 sec
20:23:24 SELECT * FROM Details ORDER BY id LIMIT 0, 1000 3 row(s) returned 0.000 sec / 0.000 sec
我們嘗試在表中插入一個名為 python 的行。正如預期的那樣，此操作會導致錯誤。
-- Trying to insert a new value
INSERT INTO Details (name, year_released) VALUES ('python', 1992)
輸出：
20:27:31 INSERT INTO Details (name, year_released) VALUES ('python',1992) Error Code: 1062. Duplicate entry 'python' for key 'details.name' 0.046 sec
使用 ON DUPLICATE KEY 更新方法在具有重複鍵驗證的 MySQL 表中插入值
此方法要麼插入新值（如果表中不存在），要麼更新現有行。因此，如果一行包含與新插入相同的資料，MySQL 不會進行任何更改。但是，如果其資料與插入查詢不同，它會更新該行。如果該行不存在，則插入該行。
INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released;
SELECT * FROM Details ORDER BY id; -- Checking the output
輸出：
id name year_released
1 python 1992
2 c++ 1985
3 Java 1995
-----------------------------------------------------------------------------------------
20:47:35 INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released 2 row(s) affected 0.172 sec
20:49:23 SELECT * FROM Details ORDER BY id LIMIT 0, 1000 3 row(s) returned 0.000 sec / 0.000 sec
從輸出中觀察到，year_released 列已針對 python 進行了更新，並且日誌表明兩 (2) 行受到影響（典型的行更新操作）。可以參考此擴充套件的官方參考以獲取更多選項。
使用 REPLACE 方法在具有重複鍵驗證的 MySQL 表中插入值
謹慎使用此方法！與之前的擴充套件不同，它刪除行並插入包含所需資料的新行。雖然這可能看起來是良性的，但如果在操作期間刪除了行的唯一關係，則可能會出現問題。
REPLACE INTO Details (name, year_released) VALUES ('python', 1993);
SELECT * FROM Details ORDER BY id;
輸出：
id name year_released
2 c++ 1985
3 Java 1995
7 python 1993
-----------------------------------------------------------------------------------------
20:56:48 REPLACE INTO Details (name, year_released) VALUES ('python', 1993) 2 row(s) affected 0.093 sec
現在，觀察 Insert 操作是否執行。但是，id 已更改（由於刪除和插入）。建議檢視此擴充套件的官方參考以確定安全使用和額外選項。
使用 IGNORE 方法在具有重複鍵驗證的 MySQL 表中插入值
IGNORE 方法對具有 duplicate keys 的行沒有任何作用。但是，它不會為 INSERT 操作引發錯誤。此方法可以處理不允許更新並且不需要引發異常/錯誤的情況。
INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991);
SELECT * FROM Details ORDER BY id;
輸出：
id name year_released
2 c++ 1985
3 Java 1995
7 python 1993
-----------------------------------------------------------------------------------------
21:29:44 INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991) 0 row(s) affected, 1 warning(s): 1062 Duplicate entry 'python' for key 'details.name' 0.157 sec
正如預期的那樣，MySQL 不會引發任何錯誤。但是，該表保持不變。
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub