MySQL & MariaDB的世界

使用 MySQL UPDATE 與 SELECT 語句進行資料更新的完整指南【適合新手到中級者】

SQL基礎
目次

1. 簡介

MySQL 是許多網頁應用程式和系統中所使用的首要資料庫管理系統。其中，「資料的更新」是日常運作中不可避免的操作。特別是，基於其他表格或計算結果來更新現有資料的場合，需要結合 UPDATE 語句和 SELECT 語句的方法。

本文將詳細說明 MySQL 中結合 UPDATE 語句和 SELECT 語句的高級資料操作。為了讓初學者也能輕鬆理解，從基礎開始，廣泛介紹在實務中實用的應用範例。學習資料庫高效更新方法，或是提升 SQL 技能人士的最佳指南。

2. UPDATE 語句的基本語法

首先，讓我們從UPDATE語句的基本開始確認。UPDATE語句是用來變更資料表中特定的一行或多行資料。

基本語法

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

UPDATE 資料表名稱
SET 欄位名稱 = 新值
WHERE 條件;
  • 資料表名稱: 要更新的資料表的名称。
  • 欄位名稱: 要更新的欄位名稱。
  • 新值: 設定到欄位的值。
  • 條件: 限制更新目標行的條件式。

簡單範例

以更新商品價格為例。

UPDATE products
SET price = 100
WHERE id = 1;

此查詢會在products資料表中，將id為1的商品價格更新為100。

多個欄位的更新

也可以同時更新多個欄位。

UPDATE employees
SET salary = 5000, position = 'Manager'
WHERE id = 2;

此例中，在employees資料表內，將id為2的員工的salary和position同時更新。

WHERE 子句的重要性

如果省略WHERE子句，則資料表中的所有行都會被更新。這可能導致資料意外變更，因此請務必注意。

UPDATE products
SET price = 200;

此查詢會將products資料表中所有商品的價格設定為200。

3. 使用 SELECT 陳述式的 UPDATE 應用

MySQL 中，透過結合 UPDATE 陳述式和 SELECT 陳述式，可以根據其他表格或特定條件取得的資料來更新記錄。本節將說明利用 SELECT 陳述式的兩種主要方法，即「子查詢」和使用「JOIN」的方法。

3.1 使用子查詢的 UPDATE

使用子查詢，可以用 SELECT 陳述式取得符合特定條件的資料，並以此為基礎進行更新。此方法結構相對簡單，且使用上靈活。

基本語法

UPDATE 表格名稱
SET 欄位名稱 = (SELECT 欄位名稱 FROM 其他表格 WHERE 條件)
WHERE 條件;

具體範例

例如，考慮將 products 表格的價格，以 product_stats 表格的平均價格來更新的情況。

UPDATE products
SET price = (SELECT average_price FROM product_stats WHERE product_stats.product_id = products.id)
WHERE EXISTS (SELECT * FROM product_stats WHERE product_stats.product_id = products.id);
  • 要點:
  • 子查詢負責回傳要更新的目標值。
  • 透過使用 EXISTS，僅在子查詢結果存在時才執行更新。

注意事項

  • 子查詢必須回傳單一值:
    若使用回傳多行結果的子查詢，將發生 Subquery returns more than one row 錯誤。要避免此問題，需使用 LIMIT 或聚合函數（例如 MAX 或 AVG）將結果限制為一行。

3.2 使用 JOIN 的 UPDATE

比起子查詢，使用 JOIN 的 UPDATE 在許多情況下效能更高。特別適合更新大量資料時。

基本語法

UPDATE 表格A
JOIN 表格B ON 條件
SET 表格A.欄位名稱 = 表格B.欄位名稱
WHERE 條件;

具體範例

接著，顯示將 orders 表格的折扣率，以相關客戶的 default_discount 來更新的範例。

UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.id
SET o.discount = c.default_discount
WHERE c.vip_status = 1;
  • 要點:
  • 透過 JOIN，可以在結合多個表格的同時有效率地進行更新。
  • 在本範例中，僅針對 customers 表格的 VIP 客戶更新 orders 表格的折扣率。

注意事項

  • 效能:
    使用 JOIN 的 UPDATE 在大型資料集特別有效率，但若未在結合條件設定適當的索引，效能可能會降低。

子查詢與 JOIN 的差異

項目子查詢JOIN
便利性簡單且靈活複雜但有效率
效能適合小規模資料適合大規模資料或多表格更新
實作難易度對初學者易懂條件設定稍複雜

4. 高效的 UPDATE 技巧

MySQL 中的資料更新雖然可以使用簡單的語法來執行，但是在處理大量資料或頻繁進行更新的情況下，需要考慮效能和安全性的高效方法。本節將介紹用來最佳化 UPDATE 語句的實用技巧。

4.1 僅在有變更時更新

在更新資料時，僅更新實際需要變更的列，即可減少無謂的寫入，並提升效能。

基本語法

UPDATE 表格名稱
SET 欄位名稱 = 新值
WHERE 欄位名稱 != 新值;

具體範例

變更商品價格時，僅在現有價格與新價格不同時更新的範例。

UPDATE products
SET price = 150
WHERE price != 150;
  • 優點:
  • 避免不必要的寫入。
  • 縮短資料庫的鎖定期間。

4.2 使用條件分歧的 CASE 語句

若要根據特定條件設定不同的值，活用 CASE 語句會很方便。

基本語法

UPDATE 表格名稱
SET 欄位名稱 = CASE
    WHEN 條件1 THEN 值1
    WHEN 條件2 THEN 值2
    ELSE 預設值
END;

具體範例

根據績效評價更新員工薪資的範例。

UPDATE employees
SET salary = CASE
    WHEN performance = 'high' THEN salary * 1.1
    WHEN performance = 'low' THEN salary * 0.9
    ELSE salary
END;
  • 重點:
  • 可根據條件進行彈性的更新。
  • 這是實務上經常使用的方法。

4.3 使用交易確保安全性

在進行多個更新時，使用交易將一連串的操作彙總，即可確保安全性和一致性。

基本語法

START TRANSACTION;
UPDATE 表格名稱1 SET ... WHERE 條件;
UPDATE 表格名稱2 SET ... WHERE 條件;
COMMIT;

具體範例

在兩個帳戶之間移動金額的操作，使用交易來管理的範例。

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
  • 重點:
  • 若中途發生錯誤，可使用 ROLLBACK 來取消變更。
  • 保證資料的一致性。

4.4 活用索引來提升效率

在 UPDATE 語句中指定的條件所使用的欄位設定索引，即可提升搜尋速度，並改善整體效能。

基本範例

CREATE INDEX idx_price ON products(price);

藉此，price 作為條件的 UPDATE 語句處理將會加速。

4.5 使用批次處理來更新大量資料

若一次更新大量資料，資料庫的負載會增加，導致效能下降。此時，使用批次處理逐少量更新會很有效。

基本語法

UPDATE 表格名稱
SET 欄位名稱 = 新值
WHERE 條件
LIMIT 1000;
  • 具體範例:
  • 一次處理 1000 列，並使用腳本進行迴圈。

5. 注意事項與最佳實務

MySQL 的 UPDATE 陳述式雖然是便利的功能，但若使用不當，可能導致效能降低或資料不一致。本節將說明使用 UPDATE 陳述式時的注意事項，以及實務中的最佳實務。

5.1 交易的運用

為了安全地執行多個 UPDATE 陳述式，建議活用交易。這樣，即使在操作途中發生錯誤，也能維持資料的完整性。

注意事項

  • 開始交易的遺漏：
    如果不明確記述 START TRANSACTION，交易就不會生效。
  • 提交與回滾：
    正常結束時，請確實使用 COMMIT，發生錯誤時使用 ROLLBACK。

最佳實務範例

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

在這個範例中，即使途中發生錯誤，也能使用 ROLLBACK 將資料恢復到原始狀態。

5.2 索引的適當設定

透過在 UPDATE 陳述式的條件中使用的欄位設定索引，能提升搜尋速度，並改善整體效能。

注意事項

  • 過度索引：
    過度使用索引會增加資料更新時的負荷。請注意只設定必要的最低限度索引。

最佳實務範例

更新商品價格時，在 price 或 id 欄位設定索引會很有效。

CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_id ON products(id);

藉此，使用 price 或 id 的 WHERE 子句的更新查詢會加速。

5.3 鎖定的管理

在 MySQL 執行 UPDATE 時，會對對應的列加鎖。特別是大量資料一次更新時，可能影響其他查詢。

注意事項

  • 長時間鎖定：
    鎖定持續長時間時，其他交易會處於等待狀態，導致系統整體效能下降。

最佳實務範例

  • 限制更新的列數（利用批次處理）。
  • 使用 WHERE 子句限定特定範圍。
UPDATE orders
SET status = 'completed'
WHERE status = 'pending'
LIMIT 1000;

5.4 使用子查詢時的注意事項

使用 SELECT 陳述式的 UPDATE 中，若子查詢返回多列，會發生錯誤。此外，若子查詢處理大規模資料，可能導致效能下降。

注意事項

  • 限制結果不為多列：
    需要使用聚合函數（例如：MAX、AVG）或 LIMIT 將結果限定為一列。

最佳實務範例

UPDATE products
SET price = (
  SELECT AVG(price)
  FROM product_stats
  WHERE product_stats.category_id = products.category_id
)
WHERE EXISTS (
  SELECT * FROM product_stats WHERE product_stats.category_id = products.category_id
);

5.5 確認執行計劃

在執行複雜的 UPDATE 查詢前，使用 EXPLAIN 確認執行計劃，能事先特定效能問題。

最佳實務範例

EXPLAIN UPDATE products
SET price = 200
WHERE category_id = 1;

藉此，能確認索引是否適當使用，以及是否發生全表掃描（Full Table Scan）。

5.6 確保備份

UPDATE 陳述式若錯誤執行，可能會遺失大量資料。因此，建議在進行重要操作前，先取得資料庫的備份。

最佳實務範例

使用 MySQL 的傾印工具來建立備份。

mysqldump -u 使用者名稱 -p 資料庫名稱 > backup.sql

6. 常見問題 (FAQ)

在這裡，我們彙整了與 MySQL 的UPDATE語句相關的常見問題及其解答。這些資訊有助於解決實際工作中的疑問，並支援高效的資料更新。

Q1: UPDATE語句可以同時更新多個表格嗎？

A1:在 MySQL 中，一個UPDATE語句無法同時更新多個表格。不過，可以透過結合多個表格（JOIN）來更新單一表格的資料。

範例：使用 JOIN 進行表格更新

UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.id
SET o.discount = c.default_discount
WHERE c.vip_status = 1;

Q2: 如何提升UPDATE語句的效能？

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

  • 適當設定索引： 為 WHERE 子句中使用的欄位建立索引。
  • 避免不必要的更新： 指定僅針對需要更新的資料列的條件。
  • 利用批次處理： 將大量資料分批更新，以減輕鎖定的影響。

批次處理的範例

UPDATE products
SET stock = stock - 1
WHERE stock > 0
LIMIT 1000;

Q3: 使用子查詢於UPDATE語句時，需要注意哪些事項？

A3:在UPDATE語句中使用子查詢時，需要注意以下事項。

  • 子查詢的結果必須為單一行： 如果子查詢返回多行，會發生錯誤。
  • 效能： 過度使用子查詢可能會導致效能降低，特別是在大規模資料的情況下。

子查詢的範例

UPDATE employees
SET salary = (SELECT AVG(salary) FROM department_salaries WHERE employees.department_id = department_salaries.department_id)
WHERE EXISTS (SELECT * FROM department_salaries WHERE employees.department_id = department_salaries.department_id);

Q4: 不使用交易執行UPDATE會發生什麼情況？

A4:如果不使用交易，在UPDATE過程中發生錯誤時，先前執行的操作會被確定，導致資料一致性可能喪失。特別是在包含多個UPDATE的處理中，建議使用交易來維持資料的一致性。

使用交易的範例

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Q5: 如果未指定條件就執行UPDATE語句，該如何處理？

A5:未指定條件的UPDATE會更新表格中的所有資料列。為了防止此情況，操作前取得資料庫備份非常重要。如果受影響的資料列較少，可以手動修正，或從備份中還原資料。

Q6: 在 MySQL 中使用UPDATE語句時發生Deadlock，該怎麼辦？

A6:Deadlock（死鎖）是多個交易互相等待鎖定時發生的情況。可以透過以下方法來處理。

  • 統一更新的順序： 在所有交易中以相同的順序更新資料列。
  • 分割交易： 減少一次更新的資料列數量，使交易的粒度變小。

7. 總結

在這篇文章中，我們詳細說明了如何有效使用 MySQL 的UPDATE語句，從基礎到應用。以下回顧各章節的重點。

1. 介紹

  • MySQL 的UPDATE語句是資料庫更新不可或缺的工具。
  • 結合SELECT語句，可以基於其他表格或計算結果進行高效的資料更新。

2. UPDATE 語句的基本語法

  • 說明了UPDATE語句的基本形式和簡單的使用範例。
  • 透過指定條件（WHERE子句），可以避免意外更新所有資料列。

3. 使用 SELECT 語句的 UPDATE 應用

  • 使用子查詢的靈活更新方法。
  • 利用 JOIN 在多個表格之間高效更新資料的方法。
  • 也確認了子查詢與 JOIN 的差異及使用時機。

4. 高效 UPDATE 的技巧

  • 為了避免不必要的更新，僅在有變更時才執行的技巧。
  • 使用CASE語句的條件分支範例。
  • 透過交易的運用、索引設定及批次處理來提升效能的方法。

5. 注意事項與最佳實務

  • 運用交易來維持資料一致性的重要性。
  • 適當管理索引與鎖定。
  • 使用子查詢時可能發生的錯誤應對方法，以及執行計劃的確認技巧。

6. FAQ

  • 以實務中常見疑問的形式，介紹UPDATE語句的具體使用範例與問題解決方法。
  • 多個表格的更新、交易的重要性、死鎖的應對方法等。

下一個步驟

基於這篇文章學到的內容，請試試以下步驟。

  1. 執行基本的UPDATE語句，確認語法。
  2. 根據實務情境，試試與SELECT語句的組合或 JOIN。
  3. 在更新大規模資料時，運用交易或索引來評估效能。

此外，如果想進一步提升 SQL 技能，建議學習以下主題。

  • MySQL 的索引最佳化
  • 交易管理的詳細內容
  • SQL 效能調整

MySQL 的UPDATE語句是資料庫操作中重要的技能。請參考這篇文章，在實務中有效運用。實際動手試試查詢，磨練技能吧！