MySQL INSERT 與 UPDATE 完全指南｜從基本操作到錯誤處理

1. 簡介

MySQL 是一種廣受歡迎的關係型資料庫管理系統，在許多 Web 應用程式和資料庫管理系統中使用。其中，用於新增或更新資料的「INSERT 語句」和「UPDATE 語句」，在基本的資料操作中扮演重要的角色。透過正確理解並有效運用這些語句，可以讓資料庫的運作更加順暢。

本文將詳細說明 MySQL 中 INSERT 語句和 UPDATE 語句從基本用法到進階操作。內容適合初學者到中級者使用，請務必參考。

2. INSERT 語句的基本

INSERT 語句的基本語法

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

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

例如，考慮在 users 表格中新增使用者。

INSERT INTO users (name, email, age)
VALUES ('Yamada Tarō', 'taro@example.com', 30);

此 SQL 會將「Yamada Tarō」、「taro@example.com」、「30」等值分別插入 users 表格的 name、email、age 欄位。

多行插入

在 MySQL 中，也可以一次插入多行資料。此時的語法如下。

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

使用此方法可以減少對資料庫的存取次數，從而提升效能。

NULL 值的處理

使用 INSERT 語句時，可能需要處理 NULL 值。例如，若 age 未設定時，可如以下所述。

INSERT INTO users (name, email, age)
VALUES ('Tanaka Jirō', 'jiro@example.com', NULL);

注意事項：若欄位設定了 NOT NULL 限制，則插入 NULL 值會產生錯誤。此時，需要設定預設值或指定值。

3. UPDATE 語句的基本

UPDATE 語句的基本語法

UPDATE 語句用於變更現有記錄的資料。本節將說明基本語法、條件式更新的方法，以及 WHERE 子句的重要性。

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

例如，考量更新 users 表格中特定使用者的年齡。

UPDATE users
SET age = 35
WHERE name = 'Yamada Tarō';

此 SQL 會在 users 表格中，將姓名為「Yamada Tarō」的使用者年齡更新為 35。

WHERE 子句的重要性

若在 UPDATE 語句中省略 WHERE 子句，則表格中的所有列都會被更新。這可能導致非預期的資料遺失，因此務必指定條件。

-- 省略 WHERE 子句的情況
UPDATE users
SET age = 30;

此 SQL 會將所有使用者的年齡設定為 30。

條件式更新

若要指定多個條件，可使用 AND 或 OR。

UPDATE users
SET age = 28
WHERE name = 'Satō Hanako' AND email = 'hanako@example.com';

如此一來，即可使用更精確的條件來更新資料。

4. 結合 INSERT 和 UPDATE 的操作

在資料庫操作中，新增資料和更新現有資料的情形常常混雜。在這種情境下，使用INSERT ... ON DUPLICATE KEY UPDATE或REPLACE語句，可以有效率地處理。此節將詳細說明各自的使用方式和注意事項。

INSERT … ON DUPLICATE KEY UPDATE 的使用方式

INSERT ... ON DUPLICATE KEY UPDATE在存在主鍵或唯一鍵約束時有效。使用此語法，可以在一個 SQL 語句中實現：如果資料已存在則更新，否則插入。

語法

INSERT INTO 表格名稱 (欄位1, 欄位2, ...)
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE
欄位1 = 新值1, 欄位2 = 新值2, ...;

使用範例

考慮在users表格中新增使用者。如果已存在相同的email，則更新該使用者的name和age。

INSERT INTO users (email, name, age)
VALUES ('taro@example.com', 'Yamada Tarō', 30)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age);

此 SQL 語句將執行以下動作：

1. 如果不存在email = 'taro@example.com'的記錄，則插入資料。
2. 如果存在現有記錄，則更新name和age。

注意事項

• 如果有AUTO_INCREMENT欄位，即使發生重複鍵，計數器仍會增加。這可能導致非預期的行為，因此需注意。
• 使用VALUES()函數，可以直接將欲插入的值用於更新。

REPLACE 語句的使用方式與差異

REPLACE語句在插入資料時，會完全刪除現有資料，並插入新資料。與INSERT ... ON DUPLICATE KEY UPDATE不同，其特點是原記錄會被刪除。

語法

REPLACE INTO 表格名稱 (欄位1, 欄位2, ...)
VALUES (值1, 值2, ...);

使用範例

在users表格中插入資料，如果email重複，則刪除現有資料並插入新資料。

REPLACE INTO users (email, name, age)
VALUES ('taro@example.com', 'Yamada Tarō', 30);

此 SQL 語句將執行以下動作：

1. 如果存在email = 'taro@example.com'的記錄，則刪除該記錄。
2. 插入新資料。

注意事項

• 由於會執行刪除和插入，因此可能影響觸發器或外部鍵約束。
• 需注意資料刪除所帶來的副作用（相關資料消失）。

效能考量

INSERT ... ON DUPLICATE KEY UPDATE和REPLACE語句各有優缺點。在大型資料庫或高頻率操作中，效能差異很重要，因此請考量以下要點。

使用情境的選擇

• 適合使用 INSERT … ON DUPLICATE KEY UPDATE 的情況
• 存在外部鍵約束或觸發器，且想避免刪除的情況。
• 資料更新頻率高的情況。
• 適合使用 REPLACE 語句的情況
• 需要完全取代資料的情況。
• 不受外部鍵約束或觸發器影響的簡單表格情況。

5. 實踐範例

在這裡，我們將介紹利用 MySQL 的 INSERT 語句和 UPDATE 語句，以及「INSERT … ON DUPLICATE KEY UPDATE」或「REPLACE」來實現的實際使用案例。透過這些，您可以了解先前學到的知識如何應用於實際工作中。

使用案例 1：庫存管理系統

在庫存管理系統中，商品資訊的註冊和庫存數量的更新經常進行。新增商品時使用 INSERT 語句，更新現有商品時使用 UPDATE 語句，或「INSERT … ON DUPLICATE KEY UPDATE」。

商品資料的插入與更新

例如，假設商品表格products的結構如下。

新商品的註冊

INSERT INTO products (product_id, name, stock)
VALUES (1, '筆記型電腦', 50);

庫存數量的更新（現有商品）

UPDATE products
SET stock = stock + 20
WHERE product_id = 1;

新增或庫存數量的更新

如果要註冊新商品，或對於現有商品更新庫存數量，可以使用「INSERT … ON DUPLICATE KEY UPDATE」。

INSERT INTO products (product_id, name, stock)
VALUES (1, '筆記型電腦', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

這個 SQL 語句實現以下功能：

• 如果商品 ID 為 1 的資料不存在，則插入。
• 如果商品 ID 為 1 的資料存在，則新增 50 至庫存數量。

使用案例 2：使用者資訊管理

在 Web 應用程式中，使用者資訊的註冊和更新是日常操作。新規使用者註冊時使用 INSERT 語句，更新現有使用者資訊時使用 UPDATE 語句或「INSERT … ON DUPLICATE KEY UPDATE」。

使用者表格的結構

新規使用者的註冊

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

使用者資訊的更新

例如，使用者變更個人資料時。

UPDATE users
SET name = 'Yamada Hanako', email = 'hanako@example.com'
WHERE user_id = 1;

新規註冊或資訊更新

如果使用者首次登入，則進行新規註冊；如果是現有使用者，則更新最後登入時間。

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

使用案例 3：定期資料更新

在處理感測器或記錄資料的情況下，每分或每秒都會插入新資料。此時，需要使用 INSERT 語句插入新資料，或有條件地更新現有資料。

記錄資料的插入

以下是記錄感測器資料的表格sensor_logs的範例。

新感測器資料的記錄

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW());

資料的更新或插入

如果感測器 ID 已存在，則更新資料；如果不存在，則插入。

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW())
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
last_updated = VALUES(last_updated);

注意事項與最佳實務

1. 錯誤處理：
   使用 ON DUPLICATE KEY UPDATE 或 REPLACE 語句時，重要的是事先確認觸發器或外鍵約束的影響。
2. 效能最佳化：
   處理大量資料時，請利用索引設計和交易來有效率地進行操作。
3. 資料一致性：
   特別是 REPLACE 語句會發生刪除和插入，因此需要採取措施避免相關資料消失的風險。

6. 錯誤與處理方法

在 MySQL 中使用 INSERT 陳述式或 UPDATE 陳述式時，可能會發生各種錯誤。本節將說明常見錯誤的範例、其原因，以及具體的處理方法。

常見錯誤範例

1. 重複項目錯誤

錯誤內容：

Error: Duplicate entry '1' for key 'PRIMARY'

原因：

• 當試圖插入已存在的值到設定了主鍵或唯一約束（UNIQUE）的欄位時，就會發生此情況。

處理方法：

• 使用 ON DUPLICATE KEY UPDATE：
  當重複項目存在時，執行更新處理。

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

• 在資料插入前進行存在確認：
  為了防止重複，事先確認該資料是否存在。

SELECT COUNT(*) FROM users WHERE user_id = 1;

2. 外部鍵約束錯誤

錯誤內容：

Error: Cannot add or update a child row: a foreign key constraint fails

原因：

• 由於外部鍵約束（FOREIGN KEY），當父表格的資料不存在時，就會發生。

處理方法：

• 在父表格插入相關資料。

INSERT INTO parent_table (id, name) VALUES (1, '父資料');

• 停用外部鍵約束來進行操作（但不推薦）。

SET FOREIGN_KEY_CHECKS = 0;
-- 資料操作
SET FOREIGN_KEY_CHECKS = 1;

3. 關於 NULL 值的錯誤

錯誤內容：

Error: Column 'name' cannot be null

原因：

• 儘管欄位設定了NOT NULL約束，但仍試圖插入 NULL 值時，就會發生。

處理方法：

• 設定預設值。

ALTER TABLE users MODIFY name VARCHAR(255) NOT NULL DEFAULT '未設定';

• 在 INSERT 陳述式中插入適當的值。

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

4. 資料類型錯誤

錯誤內容：

Error: Data truncated for column 'age' at row 1

原因：

• 當試圖插入或更新不符合欄位指定資料類型的值時，就會發生。

處理方法：

• 確認資料類型，並使用適當的值。

INSERT INTO users (age) VALUES (30); -- INT 型的情況

• 變更欄位的資料類型（視需要而定）。

ALTER TABLE users MODIFY age VARCHAR(10);

5. 與表格鎖定相關的錯誤

錯誤內容：

Error: Lock wait timeout exceeded; try restarting transaction

原因：

• 其他交易鎖定了表格，且等待一定時間後鎖定仍未解除時，就會發生。

處理方法：

• 為了避免交易衝突，執行以下類似操作。
• 將查詢分割以減少表格鎖定。
• 建立適當的索引以提升查詢執行速度。

效能與錯誤對策的最佳實務

1. 交易管理的活用

• 當一次執行多個 INSERT 或 UPDATE 時，使用交易來確實管理操作。

START TRANSACTION;
INSERT INTO orders (order_id, user_id) VALUES (1, 1);
UPDATE users SET last_order = NOW() WHERE user_id = 1;
COMMIT;

2. 索引的最佳化

• 透過為主鍵或外部鍵設定適當的索引，減少錯誤風險並提升效能。

ALTER TABLE users ADD INDEX (email);

3. 錯誤發生時的回滾

• 當錯誤發生時，執行回滾以維持資料的完整性。

START TRANSACTION;
-- 幾個操作
ROLLBACK; -- 錯誤時

7. FAQ

使用 MySQL 的 INSERT 陳述式或 UPDATE 陳述式時，許多人會有共同的疑問點。本節透過常見問題及其解答，來進一步加深讀者的理解。

Q1: 應該使用 INSERT 還是 UPDATE？

回答:

INSERT 用於新增資料，UPDATE 用於修改現有資料。不過，如果新增資料和更新現有資料混雜的情況，使用「INSERT … ON DUPLICATE KEY UPDATE」是最適合的。範例:

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

此語法可以在一個查詢中處理新增資料和更新現有資料。

Q2: ON DUPLICATE KEY UPDATE 可以用在所有使用案例嗎？

回答:

否，ON DUPLICATE KEY UPDATE 有以下限制。

1. 僅在設定主鍵或唯一鍵時才會運作。如果沒有設定，不會發生錯誤，但不會如預期運作。
2. 在大規模資料更新時，可能發生效能降低。此時，請考慮使用交易或資料分割。

Q3: REPLACE 陳述式和 ON DUPLICATE KEY UPDATE 的差異是什麼？

回答:

兩者相似，但動作上有很大的差異。

作為選擇標準，如果想不刪除資料僅更新時，使用 ON DUPLICATE KEY UPDATE；如果想完全取代時，使用 REPLACE。

Q4: 如果忘記 WHERE 子句，會怎麼樣？

回答:

如果不指定 WHERE 子句執行 UPDATE 陳述式，表格內所有記錄都會被更新。這非常危險，會造成非預期的資料變更。範例:

-- 所有記錄的 age 會更新為 30
UPDATE users
SET age = 30;

對策:

• 務必指定 WHERE 子句，僅更新符合特定條件的資料。
• 為了在執行前確認目標資料，以下方式先執行 SELECT 陳述式是最佳實務。

SELECT * FROM users WHERE name = 'Yamada Tarō';
UPDATE users SET age = 35 WHERE name = 'Yamada Tarō';

Q5: 有方法可以加速 INSERT 陳述式或 UPDATE 陳述式嗎？

回答:

可以透過以下方法提升效能。

1. 索引的最佳化:
   透過在必要欄位設定適當索引，可以加速搜尋或更新處理。

CREATE INDEX idx_email ON users(email);

2. 多行操作:
   比起逐行處理，將多行彙整插入或更新更有效率。

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

3. 交易的活用:
   透過在一個交易中處理多個操作，可以減少鎖定競爭。

START TRANSACTION;
INSERT INTO orders (order_id, user_id) VALUES (1, 1);
UPDATE users SET last_order = NOW() WHERE user_id = 1;
COMMIT;

4. 避免不必要操作:
   為了不執行不必要的更新或插入，事先確認資料。

SELECT COUNT(*) FROM users WHERE user_id = 1;
-- 避免不必要的插入或更新

Q6: 如何防止 INSERT 或 UPDATE 的錯誤？

回答:

為了防止錯誤，可以活用以下方法。

• 資料型的確認:
  確認插入・更新的資料是否適合欄位的資料型。
• 約束的適當設定:
  透過正確設定主鍵、唯一鍵、外部鍵約束，來維持資料的一致性。
• 錯誤處理:
  在程式中實作錯誤發生時的對処。

-- 錯誤發生時回滾
START TRANSACTION;
INSERT INTO users (user_id, name, email) VALUES (1, 'Yamada Tarō', 'taro@example.com');
ROLLBACK; -- 視需要

8. 總結

這篇文章中，我們從 MySQL 中的 INSERT 語句和 UPDATE 語句的基本知識，到進階操作、實際應用案例、錯誤處理方法，以及常見問題的解答，都進行了廣泛的說明。以下，讓我們回顧重要的要點。

本文章的重要要點

1. INSERT 語句的基本

• INSERT 語句用於將新資料插入表格中。
• 可以插入多行資料，從而實現高效的操作。
• 需要注意 NULL 值和 NOT NULL 約束。

2. UPDATE 語句的基本

• UPDATE 語句用於根據條件更新現有資料。
• 必須指定 WHERE 子句，以避免意外的全行更新。

3. INSERT 和 UPDATE 的組合

• INSERT ... ON DUPLICATE KEY UPDATE 可以用一個操作實現新資料的插入和現有資料的更新。
• REPLACE 語句會刪除資料並重新插入，因此需要注意對觸發器和外鍵的影響。

4. 實踐範例

• 在庫管理或使用者資訊管理等應用案例中，學習了如何活用 INSERT 語句和 UPDATE 語句。
• 展示了多個操作的效率處理的最佳實務。

5. 錯誤和對策

• 解釋了重複條目、外鍵約束、NULL 值插入錯誤等常見問題的原因和解決方法。
• 交易和索引設計的活用很重要。

6. FAQ

• 回答了 INSERT 和 UPDATE 的使用區分、ON DUPLICATE KEY UPDATE 的適用範圍、效能最佳化方法等常見疑問。

今後步驟

MySQL 的 INSERT 語句和 UPDATE 語句是資料庫操作的基礎，在所有應用程式開發中都是不可或缺的技能。基於這篇文章學到的內容，請考慮以下作為下一步。

1. 學習交易管理：
   為了進行更進階的資料庫操作，讓我們深入理解交易的活用方法。
2. 索引設計的最佳化：
   當資料量增加時，習得維持查詢效能的索引設計。
3. 錯誤發生時的日誌管理：
   當錯誤發生時，能夠快速特定原因，讓我們導入日誌的記錄和分析方法。
4. 活用 MySQL 官方文件：
   有關更詳細的資訊或最新功能，請參考 MySQL 官方文件。

最後

希望這篇文章能成為理解 INSERT 語句和 UPDATE 語句並有效率使用它的參考。透過掌握基本的資料操作，資料庫管理的技能將提升，並能夠應對更進階的應用程式開發。

今後也請繼續深化 MySQL 相關的知識！