MySQL - Insert on Duplicate Key Update

你好，有志於MySQL的熱情者！今天，我們將深入探討MySQL一個非常令人興奮且非常有用的功能： "Insert on Duplicate Key Update" 語句。作為你們親切友善的電腦老師，我將用清晰的解釋和大量的範例來引導你們了解這個主題。所以，拿起你們最喜歡的飲料，放鬆身心，讓我們一起踏上這次MySQL的冒險之旅！

What is Insert on Duplicate Key Update？

在我們深入細節之前，讓我們從基礎開始。想像你正在經營一家小型線上書店。你有一個書籍數據庫，有時候你需要添加新書或更新現有的書籍。這就是 "Insert on Duplicate Key Update" 派上用場的地方！

這個MySQL功能讓你可以在單一個查詢中插入一條新記錄或更新一條現有的記錄。這就像擁有一個聰明的助手，他知道是否將一本新書添加到你的庫存中，或者更新你已經有的書籍的細節。

The Basic Syntax

以下是最基本的語法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = value1,
column2 = value2, ...;

別擔心，這個語法起初看起來可能會讓人覺得有些令人却步。我們將通過一些現實世界的範例一步步分解它。

MySQL Insert on Duplicate Key Update Statement

讓我們創建一個簡單的範例來說明這是如何工作的。我們將使用我們的線上書店情景。

Setting Up Our Book Table

首先，讓我們為我們的書籍創建一個表：

CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
price DECIMAL(10, 2),
stock INT
);

這將創建一個包含書籍ID（我們的主鍵）、書名、作者、價格和庫存數量的列。

Inserting or Updating a Book

現在，讓我們假設我們想要添加一本新書或更新一本現有的書：

INSERT INTO books (id, title, author, price, stock)
VALUES (1, 'The MySQL Guidebook', 'Jane Doe', 29.99, 100)
ON DUPLICATE KEY UPDATE
title = VALUES(title),
author = VALUES(author),
price = VALUES(price),
stock = stock + VALUES(stock);

讓我們分解這個語句：

1. 我們試圖插入ID為1的書籍。
2. 如果ID為1的書籍不存在，它將會被插入給定的值。
3. 如果ID為1的書籍已經存在，ON DUPLICATE KEY UPDATE 部分將啟動：

• 它會更新書名、作者和價格為新值。
• 對於庫存，它會將新的庫存值添加到現有的庫存中（想像你在補貨！）。

這個單一查詢讓我們免於编写分開的INSERT和UPDATE語句。很棒，不是嗎？

INSERT or UPDATE Multiple Records at Once

現在，如果我們想要一次性添加或更新多本書呢？MySQL為我們提供了支持！

INSERT INTO books (id, title, author, price, stock)
VALUES
(2, 'SQL for Beginners', 'John Smith', 24.99, 50),
(3, 'Advanced Database Design', 'Emma Wilson', 39.99, 30),
(4, 'The Art of Querying', 'Michael Brown', 34.99, 40)
ON DUPLICATE KEY UPDATE
title = VALUES(title),
author = VALUES(author),
price = VALUES(price),
stock = stock + VALUES(stock);

這個查詢將一次性插入或更新三本書！這就像一次性為你的書店補貨。

A Word of Caution

儘管這個功能很強大，但要謹慎使用。一次更新多條記錄可能會有風險，如果你不夠小心。總是仔細檢查你的數據，或許先在小範圍內進行測試。

Client Program Example

現在，讓我們看看我們如何在真正的Python程序中使用這個。想像我們正在為我們的書店創建一個簡單的庫存管理系統。

import mysql.connector

def update_book_inventory(book_id, title, author, price, stock):
try:
connection = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="bookstore"
)

cursor = connection.cursor()

query = """
INSERT INTO books (id, title, author, price, stock)
VALUES (%s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
title = VALUES(title),
author = VALUES(author),
price = VALUES(price),
stock = stock + VALUES(stock)
"""

values = (book_id, title, author, price, stock)

cursor.execute(query, values)
connection.commit()

print("Book information updated successfully!")

except mysql.connector.Error as error:
print(f"Failed to update book information: {error}")

finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")

# Example usage
update_book_inventory(5, "Python for Data Science", "Sarah Johnson", 44.99, 25)

這個Python函數連接到我們的MySQL數據庫，執行我們的 "Insert on Duplicate Key Update" 查詢，並處理可能發生的任何錯誤。這就像擁有一個能夠輕鬆更新你的書籍庫存的友好圖書管理員！

Conclusion

親愛的學生們，你們已經走過了MySQL的 "Insert on Duplicate Key Update" 功能的世界。從理解它的基本語法到看到它在現實世界的Python程序中是如何使用的，你現在已經擁有了MySQL工具箱中的一個強大工具。

記住，像任何強大的工具一樣，要謹慎使用。總是在小規模上測試你的查詢，然後再應用到整個數據庫。最重要的是，持續練習！你越多使用這些功能，你就會越熟悉它們。

祝你們查詢愉快，願你的數據庫永遠優化，查詢速度飛快！

方法	描述
INSERT INTO ... ON DUPLICATE KEY UPDATE	根據唯一鍵插入新記錄或更新現有記錄
VALUES()	在ON DUPLICATE KEY UPDATE中使用，指代將要插入的值
多值插入	允許在單一查詢中插入或更新多條記錄
連接處理	客戶程序中正確的連接和游標管理
錯誤處理	使用try-except語句來捕獲和處理MySQL錯誤

Credits: Image by storyset