MySQL UPDATE 實戰大全：單表、關聯、子查詢更新與避坑指南

測試環境與建表

CREATE TABLE `product` (
  `proID` INT AUTO_INCREMENT PRIMARY KEY,
  `price` DECIMAL(10,2) NOT NULL,
  `type` INT NOT NULL COMMENT '0生鮮,1食品,2生活',
  `dtime` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `producttype` (
  `ID` INT PRIMARY KEY,
  `amount` INT COMMENT '類別總金額'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO product(price,type,dtime) VALUES
(10.00,0,NOW()),(10.00,1,NOW()),(10.00,1,NOW()),(20.00,2,NOW()),(30.00,3,NOW());
INSERT INTO producttype(ID) VALUES(1),(2),(3);

1. 單表更新

UPDATE product SET price=20.00, type=0 WHERE proID=2;

2. 關聯更新 (多表 JOIN)

-- MySQL 特有語法：UPDATE 多表 + WHERE 連接條件
UPDATE producttype pt
JOIN product p ON p.type = pt.ID
SET pt.amount = p.price
WHERE p.type = 1;

等價於舊式 UPDATE producttype, product SET ... WHERE ...，建議使用 JOIN 可讀性更強。

3. 子查詢更新 (聚合回寫)

寫法 A：全表掃描 (Rows matched = 4，含無匹配行)

UPDATE producttype SET amount = (
  SELECT IFNULL(SUM(p.price),0)
  FROM product p
  WHERE p.type = producttype.ID
  GROUP BY p.type
);

問題：producttype 全表掃描，無匹配的 ID=3,4 也會被更新為 NULL/0，觸發不必要的行鎖與 Binlog。

寫法 B：精準關聯 (Rows matched = 3，僅匹配行)

UPDATE producttype pt
JOIN (
  SELECT type, IFNULL(SUM(price),0) AS total
  FROM product
  GROUP BY type
) p ON pt.ID = p.type
SET pt.amount = p.total;

優勢：只掃描有匹配的行，Rows matched = 3，無多餘鎖與日誌。

4. MySQL 5.6+ 關聯更新語法限制

**不支援** UPDATE ... FROM ... (SQL Server/PostgreSQL 風格)。**必須** 使用 UPDATE t1 JOIN t2 ... SET ... WHERE ...。

避坑清單

• 不帶 WHERE 的 UPDATE：全表更新，鎖表、Binlog 暴增，生產嚴禁。
• 子查詢返回多行：Subquery returns more than 1 row，必須聚合或 LIMIT 1。
• 更新主鍵/唯一鍵：可能觸發重複鍵錯誤或外鍵級聯。
• 大批量更新：分批 (每批 1000~5000 行)，避免長事務、鎖升級、Undo 膨脹。
• 字符集不匹配：連接條件欄位字符集/校對規則不一致導致索引失效、全表掃描。

效能優化建議

1. 關聯欄位建立索引 (product.type, producttype.ID)
2. 使用 JOIN 替代舊式逗號連接，優化器更易生成好計劃
3. 子查詢改寫為 JOIN 衍生表 (如寫法 B)，減少重複掃描
4. 大表更新考慮 pt-online-schema-change 或分批腳本

掌握 MySQL UPDATE 多種模式，精準高效完成資料修改。