HOWTO · MySQL

在 MySQL 中如果不存在的話插入記錄

本教程演示瞭如何在 MySQL 資料庫中在 MySQL 中如果不存在的話插入記錄。

本頁內容

MySQL 沒有名為 INSERT IF NOT EXISTS 的字面子句。若要只在沒有資料列符合指定規則時才插入,請使用 INSERT ... SELECT ... WHERE NOT EXISTS。NOT EXISTS 子查詢會檢查該規則;它不會替你決定什麼才算重複。

例如,在示範中,可以把名字當作學生資料的比對條件。在實際應用程式中,請選擇能代表你要維持唯一之身分的欄位。通常不適合只用名字判定身分,因為不同的人可能有相同的名字。以下範例僅供說明;它不會連線至 MySQL,也不會執行 SQL。

選擇重複判定規則

撰寫查詢前,先決定哪些現有值會讓候選資料列被視為重複。NOT EXISTS 中的條件必須檢查這些欄位。如果學生以學號識別,就比對學號;如果一筆選課資料以課程和學生共同識別,就比對這兩個值。若檢查的欄位不正確,可能會略過原本想插入的資料列,或讓邏輯上重複的資料通過。

這是一種條件式寫法:SELECT 提供候選值,而其中的 WHERE NOT EXISTS 條件只有在子查詢找不到符合項目時,才允許執行 INSERT。MySQL 沒有名為 INSERT IF NOT EXISTS 的特殊子句;這是附帶條件的一般 INSERT ... SELECT 查詢。

準備範例資料表

SQL 範例使用一個包含整數學生 ID 和兩個姓名欄位的資料表。資料表可以這樣定義:CREATE TABLE student_details (stu_id INT PRIMARY KEY, stu_firstName VARCHAR(255), stu_lastName VARCHAR(255));。接著使用一般的 INSERT INTO student_details (stu_id, stu_firstName, stu_lastName) VALUES ... 陳述式加入幾筆資料,其中包括 (1, 'Preet', 'Sanghavi') 和 (2, 'Rich', 'John')。這些 SQL 片段用來說明資料表設定;下方的 Python 範例會另外模擬判斷流程,不會連線至 MySQL。

只在沒有符合資料列時插入

對於包含 stu_id、stu_firstName 和 stu_lastName 欄位的資料表,請為每筆候選資料各使用一個陳述式。第一筆候選資料的名字與現有的 Preet 相同,因此這個陳述式不會插入資料列:INSERT INTO student_details (stu_id, stu_firstName, stu_lastName) SELECT 8, 'Preet', 'Shah' WHERE NOT EXISTS (SELECT 1 FROM student_details WHERE stu_firstName = 'Preet');。第二筆候選資料的名字 Dhruv 與現有資料不符,因此這個陳述式會插入 ID 為 9 的資料列:INSERT INTO student_details (stu_id, stu_firstName, stu_lastName) SELECT 9, 'Dhruv', 'Shah' WHERE NOT EXISTS (SELECT 1 FROM student_details WHERE stu_firstName = 'Dhruv');。兩個陳述式使用相同的名字重複判定規則。這些是供 MySQL 工作階段使用的 SQL 範例;下方的 Python 範例不會執行這些 SQL。

執行以下範例:

本節中的程式碼以及顯示的標籤或輸出仍為英文。

students = [
    {"id": 1, "first_name": "Preet", "last_name": "Sanghavi"},
    {"id": 2, "first_name": "Rich", "last_name": "John"},
]

candidates = [
    {"id": 8, "first_name": "Preet", "last_name": "Shah"},
    {"id": 9, "first_name": "Dhruv", "last_name": "Shah"},
]

for candidate in candidates:
    match = any(
        student["first_name"] == candidate["first_name"]
        for student in students
    )
    if not match:
        students.append(candidate)

for student in students:
    print(student["id"], student["first_name"], student["last_name"])
1 Preet Sanghavi
2 Rich John
9 Dhruv Shah

這個小程式對兩筆候選資料都採用相同的名字比對方式:Preet 已存在,而 Dhruv 會新增。請將比對欄位改成你自己的資料中用來判定重複的欄位。Python 清單僅供說明,不會提供 MySQL 的交易或約束行為。

驗證插入與略過的結果

執行兩個 SQL 陳述式後,請執行 SELECT stu_id, stu_firstName, stu_lastName FROM student_details ORDER BY stu_id;。若從兩筆預先加入的資料開始,查詢會傳回 (1, 'Preet', 'Sanghavi')、(2, 'Rich', 'John') 和 (9, 'Dhruv', 'Shah'):因為名字 Preet 符合條件,所以看不到 ID 8;因為 Dhruv 不符合條件,所以會看到 ID 9。這個查詢的目標資料表與設定和插入範例所用的 student_details 相同。

上述 SQL 結果是根據展示的陳述式和範例資料推得。Python 程式會在清單中另外模擬相同的判斷;程式列印的資料列只是用來說明規則,不會連線至 MySQL,也不會驗證資料庫狀態。

使用 UNIQUE 約束強制唯一性

只做一次 NOT EXISTS 檢查,無法保證多個用戶端同時插入時仍維持唯一性。兩個陳述式可能都在彼此新增的資料列可見之前完成檢查,接著都嘗試插入符合條件的資料。請在資料庫中為實際定義重複規則的欄位或欄位組合加上 UNIQUE 約束。由資料庫中的約束負責強制唯一性;在一般使用情況下,條件式檢查仍可避免不必要的插入嘗試。

請選擇符合預期身分判定方式的約束。例如,若學號是唯一識別碼,就應對學號設定約束,而不是對名字設定。應用程式可依自身需求處理唯一性衝突。這樣能讓檢查條件、資料庫規則和你對重複資料的定義保持一致。