個人主頁：Guiat
歸屬專欄：MySQL

正文

1. 數據更新基礎

在MySQL中，更新數據是日常數據庫操作中至關重要的一部分。理解如何正確高效地更新數據可以顯著提升數據管理質量和應用性能。

1.1 更新操作的重要性

• 保持數據的準確性和時效性
• 響應業務變化的關鍵操作
• 數據校正和規范化的必要手段
• 系統狀態維護的基礎

1.2 更新語句基本結構

1.3 更新操作注意事項

• 始終使用WHERE子句限制范圍（除非確實需要更新所有行）
• 先用SELECT測試WHERE條件
• 考慮事務以確保數據一致性
• 注意約束和觸發器的影響
• 大規模更新時注意性能影響

2. 基本更新操作

2.1 基本UPDATE語法

-- 基本更新語法
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;-- 更新單個列
UPDATE customers
SET email = 'new.email@example.com'
WHERE customer_id = 101;-- 更新多個列
UPDATE products
SET price = 29.99, stock = stock - 5, last_updated = NOW()
WHERE product_id = 1001;-- 條件更新
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales' AND performance_rating > 4;

2.2 使用表達式更新數據

-- 使用算術表達式
UPDATE order_items
SET quantity = quantity + 2,subtotal = quantity * unit_price
WHERE order_id = 5001;-- 使用內置函數
UPDATE users
SET username = LOWER(username),full_name = CONCAT(first_name, ' ', last_name),last_login = NOW()
WHERE user_id BETWEEN 1000 AND 2000;-- 使用CASE表達式
UPDATE products
SET status = CASEWHEN stock = 0 THEN 'Out of Stock'WHEN stock < 10 THEN 'Low Stock'ELSE 'In Stock'END
WHERE category_id = 5;

2.3 使用LIMIT限制更新行數

-- 限制更新的行數
UPDATE large_table
SET processed = TRUE
WHERE processed = FALSE
ORDER BY priority DESC, created_at ASC
LIMIT 1000;-- 分批更新大表中的數據
UPDATE customers
SET status = 'inactive'
WHERE last_activity < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 5000;

2.4 NULL值處理

-- 將NULL值更新為實際值
UPDATE contacts
SET phone = '000-000-0000'
WHERE phone IS NULL;-- 將空值更新為NULL
UPDATE product_details
SET description = NULL
WHERE description = '' OR description = 'N/A';-- 使用COALESCE提供默認值
UPDATE orders
SET shipping_address = COALESCE(shipping_address, billing_address, 'No address provided')
WHERE order_id > 10000;

3. 高級更新技術

3.1 使用子查詢更新數據

-- 使用標量子查詢更新數據
UPDATE products
SET price = price * 1.1
WHERE category_id = (SELECT category_id FROM categories WHERE name = 'Electronics');-- 使用相關子查詢
UPDATE employees e
SET salary = salary * 1.03
WHERE salary < (SELECT AVG(salary) * 0.8FROM employeesWHERE department = e.department
);-- 使用EXISTS子查詢
UPDATE customers
SET status = 'premium'
WHERE EXISTS (SELECT 1 FROM ordersWHERE orders.customer_id = customers.customer_idGROUP BY customer_idHAVING SUM(total_amount) > 10000
);

3.2 多表更新

-- 使用JOIN更新數據
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.status = 'priority'
WHERE c.customer_tier = 'gold' AND o.status = 'pending';-- 使用多表更新處理復雜業務邏輯
UPDATE products p
JOIN inventory i ON p.product_id = i.product_id
JOIN suppliers s ON i.supplier_id = s.supplier_id
SET p.price = p.price * 1.05,p.last_updated = NOW(),i.last_checked = NOW()
WHERE s.country = 'China' AND p.category = 'Electronics';-- 使用多表更新同步數據
UPDATE users u
JOIN temp_user_updates tu ON u.user_id = tu.user_id
SET u.email = tu.email,u.phone = tu.phone,u.address = tu.address,u.updated_at = NOW();

3.3 條件更新與CASE表達式

-- 使用CASE表達式進行復雜條件更新
UPDATE orders
SET shipping_fee = CASEWHEN total_amount > 100 THEN 0WHEN shipping_method = 'express' THEN 15WHEN shipping_method = 'standard' THEN 5ELSE shipping_feeEND,status = CASEWHEN payment_status = 'failed' THEN 'cancelled'WHEN inventory_status = 'backorder' THEN 'on hold'ELSE statusEND
WHERE order_date > '2023-01-01';-- 按照不同條件進行分組更新
UPDATE employees
SET bonus = CASEWHEN performance_rating = 5 THEN salary * 0.15WHEN performance_rating = 4 THEN salary * 0.10WHEN performance_rating = 3 THEN salary * 0.05ELSE 0
END
WHERE department = 'Sales' AND YEAR(hire_date) < 2023;

3.4 使用ORDER BY控制更新順序

-- 按照指定順序更新
UPDATE tasks
SET status = 'in_progress'
WHERE status = 'pending'
ORDER BY priority DESC, created_at ASC
LIMIT 5;-- 組合ORDER BY和LIMIT進行優先級更新
UPDATE support_tickets
SET assigned_to = 101
WHERE status = 'open' AND assigned_to IS NULL
ORDER BY CASE priorityWHEN 'high' THEN 1WHEN 'medium' THEN 2WHEN 'low' THEN 3END,created_at ASC
LIMIT 10;

4. 批量更新策略

4.1 分批處理大規模更新

-- 使用存儲過程進行分批更新
DELIMITER //
CREATE PROCEDURE batch_update_records()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE batch_size INT DEFAULT 5000;DECLARE total_updated INT DEFAULT 0;-- 開始事務START TRANSACTION;WHILE NOT done DO-- 更新一批數據UPDATE large_tableSET processed = TRUEWHERE processed = FALSELIMIT batch_size;-- 檢查更新的行數SET total_updated = total_updated + ROW_COUNT();-- 如果沒有行被更新，設置done為TRUEIF ROW_COUNT() = 0 THENSET done = TRUE;END IF;-- 提交當前批次COMMIT;-- 如果還有更多要處理，開始新事務IF NOT done THENSTART TRANSACTION;-- 可選：添加短暫延遲讓系統呼吸DO SLEEP(0.1);END IF;END WHILE;SELECT CONCAT('Total updated: ', total_updated) AS result;
END //
DELIMITER ;-- 調用存儲過程
CALL batch_update_records();

4.2 使用臨時表進行復雜更新

-- 創建臨時表存儲要更新的記錄ID
CREATE TEMPORARY TABLE temp_updates (id INT PRIMARY KEY,new_value VARCHAR(100)
);-- 填充臨時表
INSERT INTO temp_updates (id, new_value)
SELECT id,CONCAT('Updated: ', some_field)
FROM main_table
WHERE complex_condition = TRUE
AND other_condition > 100;-- 使用臨時表進行批量更新
UPDATE main_table m
JOIN temp_updates t ON m.id = t.id
SET m.some_field = t.new_value,m.updated_at = NOW();-- 清理
DROP TEMPORARY TABLE temp_updates;

4.3 使用事務確保一致性

-- 使用事務進行相關表的多個更新
START TRANSACTION;-- 更新訂單狀態
UPDATE orders
SET status = 'shipped',shipped_date = CURRENT_DATE
WHERE order_id = 12345;-- 更新庫存
UPDATE inventory
SET stock_quantity = stock_quantity - (SELECT quantity FROM order_items WHERE order_id = 12345 AND product_id = inventory.product_id
)
WHERE product_id IN (SELECT product_id FROM order_items WHERE order_id = 12345);-- 添加發貨記錄
INSERT INTO shipments (order_id, tracking_number, carrier, shipped_date)
VALUES (12345, 'TRK123456789', 'FedEx', CURRENT_DATE);-- 如果一切正常，提交事務
COMMIT;-- 如果有錯誤，回滾
-- ROLLBACK;

5. 高級條件更新

5.1 基于聚合結果更新

-- 基于聚合結果更新客戶等級
UPDATE customers c
JOIN (SELECT customer_id,SUM(total_amount) AS total_spentFROM ordersWHERE order_date > DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)GROUP BY customer_id
) o ON c.customer_id = o.customer_id
SET c.customer_tier = CASEWHEN o.total_spent > 10000 THEN 'platinum'WHEN o.total_spent > 5000 THEN 'gold'WHEN o.total_spent > 1000 THEN 'silver'ELSE 'bronze'
END;-- 更新產品評級
UPDATE products p
JOIN (SELECT product_id,AVG(rating) AS avg_rating,COUNT(*) AS review_countFROM product_reviewsGROUP BY product_id
) r ON p.product_id = r.product_id
SET p.average_rating = r.avg_rating,p.review_count = r.review_count,p.featured = (r.avg_rating > 4.5 AND r.review_count > 10);

5.2 使用窗口函數（MySQL 8.0+）

-- 使用窗口函數更新員工排名
UPDATE employees e
JOIN (SELECT employee_id,RANK() OVER (PARTITION BY department ORDER BY performance_score DESC) AS dept_rankFROM employees
) r ON e.employee_id = r.employee_id
SET e.department_rank = r.dept_rank;-- 基于移動平均值更新產品價格波動
UPDATE product_price_history pph
JOIN (SELECT id,price,AVG(price) OVER (PARTITION BY product_id ORDER BY price_date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS avg_priceFROM product_price_history
) t ON pph.id = t.id
SET pph.price_volatility = ABS(pph.price - t.avg_price) / t.avg_price * 100
WHERE pph.price_date > '2023-01-01';

5.3 基于數據模式和異常值的更新

-- 更新異常值
UPDATE sensor_readings
SET reading_value = NULL, is_error = TRUE
WHERE reading_value < (SELECT AVG(reading_value) - (3 * STD(reading_value))FROM sensor_readingsWHERE sensor_id = sensor_readings.sensor_id)OR reading_value > (SELECT AVG(reading_value) + (3 * STD(reading_value))FROM sensor_readingsWHERE sensor_id = sensor_readings.sensor_id);-- 標記可疑交易
UPDATE transactions
SET flag_for_review = TRUE
WHERE amount > (SELECT AVG(amount) + (2 * STD(amount))FROM transactions AS tWHERE t.user_id = transactions.user_idAND t.transaction_date > DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
);

6. 特殊數據類型的更新

6.1 JSON數據更新（MySQL 8.0+）

-- 更新JSON對象中的特定屬性
UPDATE user_preferences
SET preferences = JSON_SET(preferences,'$.theme', 'dark','$.notifications.email', TRUE,'$.notifications.push', FALSE
)
WHERE user_id = 1001;-- 從JSON對象中移除屬性
UPDATE product_meta
SET metadata = JSON_REMOVE(metadata,'$.deprecated_feature','$.old_pricing'
)
WHERE product_id IN (101, 102, 103);-- 向JSON數組追加值
UPDATE article_tags
SET tags = JSON_ARRAY_APPEND(tags,'$', 'new_feature'
)
WHERE article_id = 5001;-- 在JSON路徑下替換數組元素
UPDATE user_settings
SET config = JSON_REPLACE(config,'$.favorites[0]', 'new_favorite'
)
WHERE user_id = 2001;

6.2 地理空間數據更新

-- 更新點位置
UPDATE locations
SET position = ST_GeomFromText('POINT(40.7128 -74.0060)')
WHERE location_id = 101;-- 更新區域邊界
UPDATE territories
SET boundary = ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')
WHERE territory_id = 5;-- 基于距離計算更新區域
UPDATE customer_locations
SET service_area = 'downtown'
WHERE ST_Distance(position,ST_GeomFromText('POINT(34.0522 -118.2437)')  -- Los Angeles downtown
) < 5000;  -- 5000米半徑內

6.3 文本和BLOB類型更新

-- 更新長文本內容
UPDATE articles
SET content = CONCAT(content, '\n\nUpdated on ', NOW(), ': Additional information...'),word_count = LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1
WHERE article_id = 1001;-- 使用正則表達式替換文本內容
UPDATE product_descriptions
SET description = REGEXP_REPLACE(description,'old version|previous model','new version'
)
WHERE category = 'Electronics';-- 更新BLOB數據（通常通過編程語言API完成）
-- 示例偽代碼：
/*
PREPARE stmt FROM "UPDATE documents SET file_data = ? WHERE document_id = ?";
EXECUTE stmt USING binary_data, document_id;
DEALLOCATE PREPARE stmt;
*/

6.4 加密數據更新

-- 使用MySQL內置加密函數更新敏感數據
UPDATE users
SET password_hash = SHA2(CONCAT('new_password', salt), 256),password_updated_at = NOW()
WHERE user_id = 101;-- 更新加密的信用卡信息
UPDATE payment_methods
SET card_number = AES_ENCRYPT('4111111111111111', @encryption_key),card_holder = 'John Smith',expiry_date = '2026-04'
WHERE payment_id = 501;-- 更新并使用內置函數加密敏感數據
UPDATE customers
SET ssn = TO_BASE64(AES_ENCRYPT('123-45-6789', @encryption_key)),data_encrypted = TRUE
WHERE customer_id = 1001 AND data_encrypted = FALSE;

7. 條件更新與驗證

7.1 使用條件邏輯防止不必要的更新

-- 只在數據實際變化時更新
UPDATE products
SET name = 'New Product Name',price = 25.99,updated_at = NOW()
WHERE product_id = 101AND (name != 'New Product Name' OR price != 25.99);-- 使用行比較確保有變化
UPDATE customers
SET email = 'new.email@example.com',updated_at = NOW()
WHERE customer_id = 501AND (email, updated_at) != ('new.email@example.com', updated_at);

7.2 基于當前值的條件更新

-- 只更新滿足特定條件的記錄
UPDATE inventory
SET status = 'low_stock',needs_reorder = TRUE
WHERE product_id IN (101, 102, 103)AND quantity < reorder_thresholdAND status != 'discontinued';-- 基于計算結果的更新
UPDATE orders
SET total = subtotal + shipping_fee - discount,tax = (subtotal * tax_rate / 100),grand_total = (subtotal + shipping_fee - discount) + ((subtotal * tax_rate) / 100)
WHEREorder_status = 'pending'AND (total != subtotal + shipping_fee - discountOR tax != (subtotal * tax_rate / 100));

7.3 使用觸發器確保數據完整性

-- 創建觸發器驗證更新數據
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN-- 檢查薪資變化不超過50%IF NEW.salary > OLD.salary * 1.5 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Salary increase cannot exceed 50%';END IF;-- 不允許降低薪資IF NEW.salary < OLD.salary THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Salary cannot be decreased';END IF;-- 自動設置更新時間SET NEW.updated_at = NOW();
END //
DELIMITER ;-- 創建審計記錄觸發器
DELIMITER //
CREATE TRIGGER after_customer_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGININSERT INTO customer_audit_log (customer_id,action,field_name,old_value,new_value,updated_by,update_time)SELECTNEW.customer_id,'UPDATE',column_name,old_value,new_value,CURRENT_USER(),NOW()FROM (-- 檢查哪些字段發生了變化SELECT 'name' AS column_name, OLD.name AS old_value, NEW.name AS new_valueWHERE OLD.name <> NEW.name OR (OLD.name IS NULL AND NEW.name IS NOT NULL) OR (OLD.name IS NOT NULL AND NEW.name IS NULL)UNION ALLSELECT 'email', OLD.email, NEW.emailWHERE OLD.email <> NEW.email OR (OLD.email IS NULL AND NEW.email IS NOT NULL) OR (OLD.email IS NOT NULL AND NEW.email IS NULL)UNION ALLSELECT 'phone', OLD.phone, NEW.phoneWHERE OLD.phone <> NEW.phone OR (OLD.phone IS NULL AND NEW.phone IS NOT NULL) OR (OLD.phone IS NOT NULL AND NEW.phone IS NULL)) changed_columns;
END //
DELIMITER ;

8. 實際應用場景

8.1 電子商務場景

-- 更新產品庫存
UPDATE products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
SET p.stock_quantity = p.stock_quantity - oi.quantity,p.last_sold_date = NOW()
WHERE o.order_id = 10001AND o.status = 'confirmed';-- 標記熱門產品
UPDATE products p
JOIN (SELECT product_id, SUM(quantity) AS total_soldFROM order_itemsJOIN orders ON order_items.order_id = orders.order_idWHERE orders.order_date > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)GROUP BY product_idHAVING total_sold > 50
) hot ON p.product_id = hot.product_id
SET p.is_hot_seller = TRUE,p.featured = TRUE;-- 客戶升級
UPDATE customers c
JOIN (SELECT customer_id,COUNT(order_id) AS order_count,SUM(total_amount) AS total_spentFROM ordersWHERE order_date > DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)GROUP BY customer_id
) o ON c.customer_id = o.customer_id
SET c.customer_tier = CASEWHEN o.total_spent > 5000 THEN 'platinum'WHEN o.total_spent > 2000 THEN 'gold'WHEN o.total_spent > 1000 THEN 'silver'ELSE c.customer_tierEND,c.lifetime_value = c.lifetime_value + o.total_spent
WHERE o.order_count > 0;

8.2 CMS內容管理

-- 更新文章閱讀計數和熱門標記
UPDATE articles
SET view_count = view_count + 1,is_trending = (view_count + 1 > 1000),last_viewed = NOW()
WHERE article_id = 501;-- 批量更新文章狀態
UPDATE articles
SET status = 'published',published_date = NOW(),last_modified = NOW()
WHERE status = 'approved'AND scheduled_publish_date <= CURRENT_TIMESTAMP;-- 更新文章分類
UPDATE articles a
JOIN categories c ON a.primary_category_id = c.category_id
SET a.category_path = CONCAT(c.parent_path, '/', c.slug),a.last_modified = NOW()
WHERE c.parent_path != 'old_path';

8.3 用戶管理系統

-- 用戶登錄狀態更新
UPDATE users
SET last_login = NOW(),login_count = login_count + 1,last_ip = '192.168.1.100',status = 'active'
WHERE user_id = 1001;-- 批量禁用不活躍用戶
UPDATE users
SET status = 'inactive',deactivated_at = NOW(),deactivation_reason = 'inactivity'
WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)AND status = 'active';-- 用戶賬戶升級
UPDATE user_subscriptions us
JOIN subscription_plans sp ON us.plan_id = sp.plan_id
SET us.status = 'active',us.current_period_start = CURRENT_DATE,us.current_period_end = DATE_ADD(CURRENT_DATE, INTERVAL sp.duration DAY),us.updated_at = NOW()
WHERE us.user_id = 2001AND us.plan_id = 3;

8.4 系統維護和清理

-- 清理舊日志
UPDATE system_logs
SET content = 'Content archived',archived = TRUE,archive_date = NOW()
WHERE log_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)AND archived = FALSE
LIMIT 10000;-- 批量刪除標記
UPDATE temp_files
SET marked_for_deletion = TRUE,deletion_scheduled_at = DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
WHERE created_at < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)AND accessed_at < DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY);-- 數據匿名化
UPDATE users
SET email = CONCAT('user_', user_id, '@anonymized.example'),phone = NULL,address = NULL,anonymized = TRUE,anonymized_at = NOW()
WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 3 YEAR)AND status = 'inactive'AND anonymized = FALSE;

9. 性能優化

9.1 更新性能優化技巧

-- 1. 確保WHERE子句中的列有索引
-- 創建索引提高WHERE條件性能
CREATE INDEX idx_last_activity ON users(last_activity);-- 2. 使用EXPLAIN分析更新語句
EXPLAIN UPDATE users
SET status = 'inactive'
WHERE last_activity < '2023-01-01';-- 3. 限制批量更新的記錄數
UPDATE large_table
SET processed = TRUE
WHERE processed = FALSE
ORDER BY id
LIMIT 5000;-- 4. 使用更高效的操作符
-- 更高效：使用BETWEEN代替多個比較
UPDATE orders
SET status = 'archived'
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';-- 5. 關閉約束檢查（謹慎使用）
SET FOREIGN_KEY_CHECKS = 0;
-- 執行批量更新
UPDATE large_table SET ...;
SET FOREIGN_KEY_CHECKS = 1;

9.2 索引與更新性能

9.3 批處理與大規模更新

-- 創建臨時表進行分析
CREATE TEMPORARY TABLE users_to_update AS
SELECT user_id
FROM users
WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
AND status = 'active';-- 創建索引加速后續更新
ALTER TABLE users_to_update ADD PRIMARY KEY (user_id);-- 分批處理更新
DELIMITER //
CREATE PROCEDURE batch_update_users()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE batch_size INT DEFAULT 1000;DECLARE rows_affected INT;REPEAT-- 使用JOIN基于臨時表更新UPDATE users uJOIN (SELECT user_idFROM users_to_updateLIMIT batch_size) AS batch ON u.user_id = batch.user_idSET u.status = 'inactive',u.updated_at = NOW();-- 獲取影響的行數SET rows_affected = ROW_COUNT();-- 刪除已處理的記錄DELETE FROM users_to_updateORDER BY user_idLIMIT batch_size;-- 如果有更新，則休息一下再繼續，避免長期鎖定IF rows_affected > 0 THENDO SLEEP(0.1);END IF;UNTIL rows_affected = 0 END REPEAT;-- 清理DROP TEMPORARY TABLE IF EXISTS users_to_update;
END //
DELIMITER ;-- 執行批處理過程
CALL batch_update_users();

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！