MySQL 的 INSERT 與 UPDATE 徹底解說：高效資料管理的基礎與應用

1. 簡介

MySQL 是資料庫管理系統中廣泛使用的工具之一。其中，INSERT 和 UPDATE 是為了在資料庫中新增與更新資料而經常使用的基本操作。特別是在 EC 網站的商品資訊管理或使用者資料的註冊・更新等日常業務中，扮演重要的角色。

正確理解並有效率地活用此類基本操作，在資料庫管理中極為重要。本篇文章針對初學者至中級者，從 INSERT 和 UPDATE 的基本用法到實踐應用範例，詳細說明。此外，也會提及操作時的注意事項及最佳實務，以支援更有效的資料管理。

下一節將說明 INSERT 語句的基本語法及其使用方法。旨在為想複習 SQL 基礎或獲取新知識的人，提供有用的內容。

2. INSERT 陳述式的基本語法與使用方法

在資料庫中新增資料時使用的就是 INSERT 陳述式。此 SQL 陳述式雖然是 MySQL 中非常基本的語法，但卻在資料管理中扮演支撐基礎的重要角色。本節將說明 INSERT 陳述式的基本語法、具體範例以及使用時的注意事項。

INSERT 陳述式的基本語法

INSERT 陳述式的基本語法如下所示。

INSERT INTO 資料表名稱 (欄位1, 欄位2, ...)
VALUES (值1, 值2, ...);

• 資料表名稱: 插入資料的資料表名稱。
• 欄位1, 欄位2, …: 對應插入資料的欄位名稱。
• 值1, 值2, …: 插入的實際資料。

具體範例

例如，要在 users 資料表中新增使用者的名稱和電子郵件地址時，可以使用以下 SQL 陳述式。

INSERT INTO users (name, email)
VALUES ('Yamada Taro', 'taro@example.com');

此陳述式會在 users 資料表的 name 欄位中新增「Yamada Taro」，在 email 欄位中新增「taro@example.com」。

INSERT 陳述式的選項

1. 一次插入多行資料

INSERT 陳述式可以一次插入多行資料。以下範例同時在 users 資料表中新增兩個使用者。

INSERT INTO users (name, email)
VALUES 
  ('Yamada Hanako', 'hanako@example.com'),
  ('Sato Jiro', 'jiro@example.com');

此方法在新增多行資料時相當有效率。

2. 僅插入特定欄位的資料

不需將資料插入所有欄位。也可以僅將資料新增到特定欄位。

INSERT INTO users (name)
VALUES ('Suzuki Ichiro');

在這種情況下，email 欄位會設定為 NULL 或預設值。

使用時的注意事項

1. 注意欄位與值的對應

• 指定的欄位數量與順序，必須與 VALUES 中指定的值數量與順序一致。
• 範例：
  INSERT INTO users (name, email) VALUES ('Ota Yoko', 'yoko@example.com'); -- 正確

1. 確認唯一鍵限制

• 插入的資料是否與現有資料重複，需要事先確認。
• 若插入重複資料，將會發生錯誤。

1. 活用預設值

• 若未指定特定欄位的值，則會插入資料表設計時設定的預設值。

3. UPDATE 語句的基本語法與使用方法

UPDATE 語句是用來修改・更新現有資料的 SQL 語句。例如，用來修改使用者資訊或更新庫存數量時會加以活用。本節將說明 UPDATE 語句的基本語法、實踐範例以及使用時的注意事項。

UPDATE 語句的基本語法

UPDATE 語句的基本語法如下所示。

UPDATE 表格名稱
SET 欄位1 = 新值1, 欄位2 = 新值2, ...
WHERE 條件;

• 表格名稱: 更新目標的表格。
• SET 子句: 要更新的欄位及其新值。
• WHERE 子句: 指定要更新的列的條件。若省略條件，則表格內的所有列都會被更新。

具體範例

以下是修改 users 表格內特定使用者的電子郵件地址的範例。

UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

此語句會將 id 為 1 的使用者的電子郵件地址更新為「newemail@example.com」。

使用時的注意事項

1. WHERE 子句的重要性

若省略 WHERE 子句，則表格內的所有列都會被更新。以下範例會將 email 欄位全部變更為相同的值，因此需要注意。

UPDATE users
SET email = 'sameemail@example.com'; -- 所有列都會被更新

為了避免誤將所有資料變更，請務必指定必要的條件。

2. 多欄位更新

若要同時更新多個欄位，請在 SET 子句中使用逗號分隔欄位來指定。

UPDATE users
SET name = 'Yamada Jiro', email = 'jiro@example.com'
WHERE id = 2;

此語句會同時更新 id 為 2 的使用者的姓名與電子郵件地址。

3. 表格鎖定與效能

• 使用 UPDATE 語句時，目標表格或列會被鎖定，因此在更新大量資料時，可能會影響效能。
• 視需要，請考慮批次處理或索引的活用。

實踐範例

更新使用者的啟用狀態

以下範例會將符合特定條件的用戶狀態變更為「active」。

UPDATE users
SET status = 'active'
WHERE last_login > '2025-01-01';

此語句會將最後登入日期為 2025 年 1 月 1 日之後的使用者更新為啟用狀態。

減少庫存數量

在減少商品庫存數量時，可以如以下方式基於目前值進行計算。

UPDATE products
SET stock = stock - 1
WHERE product_id = 101;

此語句會將 product_id 為 101 的商品庫存減少 1。

UPDATE 語句的應用

UPDATE 語句也可以從其他表格取得值來進行更新。例如，從其他表格參照值來更新的語法如下所示。

UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.order_date
WHERE o.order_status = 'completed';

此語句會將 orders 表格中訂單完成的日期反映到 users 表格的 last_order_date。

摘要

UPDATE 語句是資料更新中不可或缺的操作。透過正確理解基本語法並適當使用 WHERE 子句，即可安全且有效率地操作資料。下一節將詳細說明結合 INSERT 與 UPDATE 的操作方法。

4. Combining INSERT and UPDATE Operations

In MySQL, to efficiently manage data, INSERT and UPDATE can be used in combination. This allows you to insert if the new data does not exist and update if it already exists, all in a single SQL statement. This section focuses on explaining methods that leverage ON DUPLICATE KEY UPDATE.

What is ON DUPLICATE KEY UPDATE?

ON DUPLICATE KEY UPDATE is a convenient feature that integrates the operations of INSERT statements and UPDATE statements. By using this syntax, data insertion and updating can be streamlined.

The basic syntax is as follows.

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

• INSERT Part: Operation to add new data.
• ON DUPLICATE KEY UPDATE Part: Operation to update data if a duplicate unique key is detected during insertion.

Usage Examples

Basic Example

The following is an example of inserting data into the users table and updating an existing username.

INSERT INTO users (id, name, email)
VALUES (1, 'Yamada Taro', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = 'Yamada Taro', email = 'taro@example.com';

This statement performs the following actions:

1. If data with id 1 does not exist, insert a new record.
2. If data with id 1 exists, update name and email.

Inventory Management Example

This is a convenient syntax for updating or inserting product stock quantities.

INSERT INTO inventory (product_id, stock)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

This statement executes the following:

1. If the product with product_id 101 does not exist, insert a new record.
2. If it already exists, add 50 to the stock quantity (stock).

Notes

1. Necessity of Unique Key

ON DUPLICATE KEY UPDATE only works if a unique key (PRIMARY KEY or UNIQUE index) is set in the table. If there is no unique key, an error will occur.

2. AUTO_INCREMENT Increase

When using ON DUPLICATE KEY UPDATE, note that the AUTO_INCREMENT value increases even if a duplicate key is detected. This is because MySQL advances the count at the insertion attempt stage.

INSERT INTO users (id, name)
VALUES (NULL, 'Suzuki Hanako')
ON DUPLICATE KEY UPDATE
name = 'Suzuki Hanako';

In this case, the AUTO_INCREMENT value increases even if a duplicate occurs.

3. Impact on Performance

When handling large amounts of data, ON DUPLICATE KEY UPDATE processing may affect performance. In particular, if frequent insert and update operations are required, consider using transactions to improve efficiency.

Advanced Examples

Data Integration Between Tables

It is also possible to retrieve data from another table and insert or update it using ON DUPLICATE KEY UPDATE.

INSERT INTO users (id, name, email)
SELECT id, full_name, dept
FROM external_users
ON DUPLICATE KEY UPDATE
name = VALUES(name),
department = VALUES(department);

This statement inserts data from the external_users table into the users table and updates it if there is a duplicate.

Summary

ON DUPLICATE KEY UPDATE is a powerful tool for efficiently managing data in MySQL. By leveraging this feature, insertion and updating can be achieved in a single SQL statement, leading to simpler code and improved performance. In the next section, based on specific scenarios useful in actual business, more detailed usage examples will be introduced.

5. 實踐範例：透過具體情境學習

在這裡，我們將介紹如何在實際情境中運用INSERT ... ON DUPLICATE KEY UPDATE以及基本的INSERT・UPDATE語句。透過參考這些範例，您將能夠習得可應用於實務的實踐技能。

情境1: EC網站的商品庫存管理

在電子商務網站中，有時會新增商品，有時現有商品會重新進貨。在這種情況下，需要有效管理庫存數量。

實踐範例

以下的SQL語句會新增商品或更新現有商品的庫存數。

INSERT INTO inventory (product_id, product_name, stock)
VALUES (101, '筆記型電腦', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

• 說明:

1. 如果不存在商品ID為101的商品，則插入新記錄。
2. 如果已存在商品ID為101的商品，則在庫存數（stock）中新增50。

如此一來，即可在商品管理中有效進行庫存更新。

情境2: 使用者註冊與更新

在會員制服務中，有時會有新使用者註冊，有時現有使用者資訊會被更新。兩種處理皆可透過單一SQL語句來應對。

實踐範例

以下的SQL語句會新增新使用者或更新現有使用者的姓名與電子郵件地址。

INSERT INTO users (id, name, email)
VALUES (1, 'Tanaka Ichirō', 'tanaka@example.com')
ON DUPLICATE KEY UPDATE
name = 'Tanaka Ichirō',
email = 'tanaka@example.com';

• 說明:

1. 如果不存在使用者ID為1的資料，則插入新記錄。
2. 如果已存在使用者ID為1的資料，則更新姓名與電子郵件地址。

這種方法有助於使用者資訊的一括管理。

情境3: 登入記錄的更新

在網頁應用程式中，記錄使用者的登入記錄是很常見的。讓我們看看註冊新使用者同時更新現有使用者最後登入時間的範例。

實踐範例

INSERT INTO login_history (user_id, last_login)
VALUES (1, NOW())
ON DUPLICATE KEY UPDATE
last_login = NOW();

• 說明:

1. user_id為1的記錄不存在時，插入新記錄。
2. 對於現有使用者，將最後登入時間（last_login）更新為目前時間（NOW()）。

此SQL語句提供了有效管理登入資訊的方法。

情境4: 商品價格的定期更新

如果需要定期更新價格，使用ON DUPLICATE KEY UPDATE即可同時新增新商品並更新現有商品的價格。

實踐範例

INSERT INTO products (product_id, product_name, price)
VALUES (201, '智慧型手機', 69900)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

• 說明:

1. product_id為201的商品不存在時，插入新商品資訊。
2. 現有商品存在時，更新其價格（price）。

這種方法是簡潔進行商品價格變更的手段。

情境5: 資料的整合管理

從外部系統匯入資料時，需要管理以避免與現有資料重複。

實踐範例

INSERT INTO employees (employee_id, name, department)
SELECT id, full_name, dept
FROM external_employees
ON DUPLICATE KEY UPDATE
name = VALUES(name),
department = VALUES(department);

• 說明:

1. 從external_employees表格取得資料，並插入至employees表格。
2. 偵測到重複時，更新姓名與部門資訊。

此類資料整合在系統間的資料聯動中扮演重要角色。

總結

這些情境展示了如何運用INSERT ... ON DUPLICATE KEY UPDATE來有效管理資料。為了應對現實業務中頻繁遇到的情況，請參考這些範例並適當運用SQL語句。

6. 注意事項與最佳實務

MySQL 中使用 INSERT 或 UPDATE，甚至是ON DUPLICATE KEY UPDATE時，理解幾個注意事項並活用最佳實務，即可提升效能，實現安全且高效的資料操作。本節將介紹在實務中有用的注意事項與推薦方法。

注意事項

1. 正確指定 WHERE 子句

• 問題: 在 UPDATE 語句中未指定 WHERE 子句時，可能會更新資料表中的所有列。
• 解決方案: 務必在 WHERE 子句中設定適當的條件，以限定目標列。

  UPDATE users
  SET email = 'updated@example.com'
  WHERE id = 1; -- 正確的

2. 唯一鍵的設定

• 問題: 使用ON DUPLICATE KEY UPDATE時，若不存在唯一鍵（PRIMARY KEY 或 UNIQUE 索引），將發生錯誤。
• 解決方案: 在可能重複的資料欄位設定唯一鍵。

  ALTER TABLE users ADD UNIQUE (email);

3. AUTO_INCREMENT 的處理

• 問題: 使用ON DUPLICATE KEY UPDATE時，即使發生重複，AUTO_INCREMENT 值仍會增加。
• 解決方案: 使用 AUTO_INCREMENT 時，進行不會受影響的適當唯一鍵設計，或明確管理值。

4. 對效能的影響

• 問題: 以 INSERT 或 UPDATE 處理大量資料時，資料庫負載會增加，速度會降低。
• 解決方案:
• 使用交易來批次管理處理。
• 視需要執行批次處理。

最佳實務

1. 交易的使用

為了維持資料的一致性，推薦在交易內執行多個操作。

START TRANSACTION;

INSERT INTO users (id, name) VALUES (1, 'Yamada Taro')
ON DUPLICATE KEY UPDATE name = 'Yamada Taro';

UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;

COMMIT;

• 使用交易時，若中途發生錯誤，即可回復（ROLLBACK），確保資料的一致性。

2. 索引的活用

為了提升 INSERT 或 UPDATE 的效能，適當設定索引。

CREATE INDEX idx_user_email ON users (email);

• 索引可加速資料搜尋，但過度設定可能降低 INSERT 或 UPDATE 的速度，因此需注意。

3. 錯誤處理

明確定義錯誤時的處理，即可防止未預期的行為。

DELIMITER //
CREATE PROCEDURE insert_user(IN user_id INT, IN user_name VARCHAR(255))
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '發生錯誤';
    END;
    START TRANSACTION;
    INSERT INTO users (id, name) VALUES (user_id, user_name)
    ON DUPLICATE KEY UPDATE name = user_name;
    COMMIT;
END;
//
DELIMITER ;

4. 預設值的設定

在資料表設計時設定預設值，即可明確 INSERT 時可省略的資料欄位，減少錯誤。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active'
);

5. 大量資料的處理

在處理大量資料時，限制一次插入・更新的列數，並分割執行，即可最佳化效能。

INSERT INTO large_table (col1, col2)
VALUES
  (1, 'value1'),
  (2, 'value2'),
  ...
  (1000, 'value1000'); -- 選擇適當的列數

常見陷阱與對策

• 陷阱1: 重複資料的處理錯誤
  → 解決: 活用唯一鍵與ON DUPLICATE KEY UPDATE。
• 陷阱2: 資料表鎖定導致的效能降低
  → 解決: 活用索引與交易，並限定處理範圍。
• 陷阱3: 不必要的列更新導致的資源浪費
  → 解決: 將更新目標資料欄位限制在必要最小限度。

摘要

MySQL 的 INSERT 或 UPDATE 要安全且高效地使用，理解注意事項並導入最佳實務即為重要。適當活用交易、索引、錯誤處理，即可提升資料庫操作的可靠性和效能。

7. FAQ

在這裡，我們將回答有關 MySQL 的 INSERT 或 UPDATE，以及 ON DUPLICATE KEY UPDATE 的常見問題。補充先前章節提到的內容，旨在解決實際工作中的疑問。

Q1: ON DUPLICATE KEY UPDATE 使用時，為什麼 AUTO_INCREMENT 的值會增加？

回答:
MySQL 在執行 INSERT 操作時，會分配 AUTO_INCREMENT 值。即使資料重複而轉移到更新處理（ON DUPLICATE KEY UPDATE），AUTO_INCREMENT 的計數器也會在該時点增加。這是 MySQL 的規範，沒有方法可以防止 AUTO_INCREMENT 值被跳過。對策:

• 為了管理可預測的 id，請考慮避免使用 AUTO_INCREMENT，改為手動管理的方法。

Q2: ON DUPLICATE KEY UPDATE 和 REPLACE INTO 的差異是什麼？

回答:

• ON DUPLICATE KEY UPDATE: 偵測到重複鍵時，會更新該記錄。現有資料不會被刪除而維持。
• REPLACE INTO: 偵測到重複鍵時，現有記錄會被刪除，並插入新記錄。因此，可能會遺失相關的外鍵或歷史資料。

使用指南:

• 如果想保留資料的歷史，請選擇 ON DUPLICATE KEY UPDATE。
• 如果需要完全取代舊資料，請使用 REPLACE INTO。

Q3: INSERT 語句中如何一次插入多行？

回答:
INSERT 語句可以使用 VALUES 子句一次插入多行。

INSERT INTO users (name, email)
VALUES 
  ('Yamada Tarō', 'taro@example.com'),
  ('Satō Hanako', 'hanako@example.com'),
  ('Suzuki Jirō', 'jiro@example.com');

這種方法比個別插入多行更能提升效能。

Q4: WHERE 子句中如何指定多個條件？

回答:
指定多個條件時，使用 AND 或 OR 運算子。

UPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01' AND status = 'active';

• AND: 適用於同時滿足兩個條件的狀況。
• OR: 適用於滿足任一條件的狀況。

Q5: 什麼情況下應該使用交易？

回答:
在以下資料一致性重要的情況下，應該使用交易。

1. 多個資料庫操作需要一致執行的情況:

   START TRANSACTION;
   INSERT INTO orders (order_id, user_id) VALUES (1, 123);
   UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;
   COMMIT;

2. 發生錯誤時想還原變更的情況:
   使用交易時，如果發生錯誤，可以進行回滾（恢復到原狀態）。

Q6: 如何有效率地處理大量資料的 INSERT 或 UPDATE？

回答:
在大量資料操作時，請考慮以下方法。

• 批次操作:
  使用 INSERT 語句一次插入多行。

  INSERT INTO users (name, email)
  VALUES 
    ('Tanaka Ichirō', 'ichiro@example.com'),
    ('Satō Hanako', 'hanako@example.com');

• 索引的活用:
  設定適當的索引，以提升資料搜尋或更新的速度。
• 處理的分批:
  不是一次處理所有資料，而是分批執行。

  UPDATE inventory
  SET stock = stock - 1
  WHERE product_id BETWEEN 100 AND 200;

Q7: 使用預設值有什麼優點？

回答:
設定預設值後，在 INSERT 時省略的欄位會自動插入值。這可以簡化程式碼並防止錯誤。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO users (name)
VALUES ('Yamada Tarō'); -- status 會自動設定為 'active'

總結

在 FAQ 章節中，我們詳細說明了實際工作中常見的疑問。活用這些知識，可以更有效率且準確地使用 MySQL 的 INSERT 或 UPDATE 操作。

8. 總結

本文中，我們系統性地說明了 MySQL 中的 INSERT 語句和 UPDATE 語句的基本操作，從高效管理資料的應用技巧，到針對初學者至中級者，融入實務上有用的知識和最佳實踐，重點傳達以下要點。

主要要點的回顧

1. INSERT 語句和 UPDATE 語句的基本語法

• INSERT 語句用來新插入資料，UPDATE 語句用來修改現有資料的方法，我們學習了。
• WHERE 子句或多行操作等，需要注意的要點，我們說明了。

1. ON DUPLICATE KEY UPDATE 的活用

• 作為以一個 SQL 語句執行新資料插入和現有資料更新的高效技巧，我們介紹了 ON DUPLICATE KEY UPDATE。
• 透過庫存管理和登入記錄更新等具體範例，展示了實務中的應用方法。

1. 實踐性的使用範例

• 我們呈現了基於 EC 網站庫存管理或使用者資訊更新等現實情境的 SQL 語句。
• 由此，應該能明確如何在實際業務中活用。

1. 注意事項和最佳實踐

• WHERE 子句的適當使用、透過交易確保資料一致性、活用索引等高效且安全的操作方法，我們說明了。

1. FAQ 中的疑問解決

• 透過對常見問題的回答，支援更深入的理解和實務中的疑問解決。

本文獲得的價值

MySQL 的 INSERT 或 UPDATE，以及 ON DUPLICATE KEY UPDATE，透過正確理解並實踐，應該能獲得以下優點。

• 資料庫管理的效率化: 以簡潔的 SQL 語句一元化插入・更新操作。
• 錯誤的防止: 活用 WHERE 子句或交易的安全操作。
• 實務應用力向上: 活用符合現實情境的具體範例。

下一步

參考本文，實際在自己的專案中活用 INSERT 或 UPDATE 看看吧。另外，若想進一步提升技能，推薦學習以下主題。

• MySQL 的效能調整
• 複雜查詢（子查詢、JOIN）的建構
• MySQL 中的觸發器或儲存程序的活用

透過本文，MySQL 的資料操作相關技能提升，並希望能連接到業務生產力的向上。今後，也請習得更高階的資料庫技術，並應用到專案中！