MySQL UPDATE/DELETE 資料修改刪除實戰：語法、條件構建、效能優化與安全防護

UPDATE 核心語法與陷阱

-- 基礎更新
UPDATE tbl SET col1 = val1, col2 = val2 WHERE condition;

-- 多表更新（MySQL 專屬）
UPDATE t1 JOIN t2 ON t1.id = t2.t1_id
SET t1.status = 'done', t2.updated = NOW()
WHERE t1.created < '2023-01-01';

-- 運算式更新
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- 子查詢更新
UPDATE t1 SET col = (SELECT MAX(col) FROM t2 WHERE t2.t1_id = t1.id);

關鍵陷阱

• 缺少 WHERE：全表更新，UPDATE tbl SET col=val 會鎖全表
• 隱性類型轉換：WHERE varchar_col = 123 導致全表掃描
• 子查詢效能：相關子查詢逐行執行，改用 JOIN
• 主鍵/唯一索引更新：觸發 DELETE+INSERT，影響 AUTO_INCREMENT、外鍵、觸發器

DELETE 核心語法與陷阱

-- 基礎刪除
DELETE FROM tbl WHERE condition;

-- 多表刪除
DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.t1_id WHERE t2.status = 'deleted';

-- 限制刪除行數
DELETE FROM tbl WHERE status = 'old' ORDER BY created LIMIT 1000;

-- 清空表（高效，重置 AUTO_INCREMENT）
TRUNCATE TABLE tbl;

DELETE vs TRUNCATE

效能優化策略

安全防護清單

1. 生產環境禁止無 WHERE 的 UPDATE/DELETE（工具攔截/審計）
2. 執行前先 SELECT 驗證影響行數
3. 使用只讀帳號日常查詢，寫入帳號單獨管理
4. 重要操作截圖/記錄 SQL，備份先行
5. 啟用 sql_safe_updates=1 強制要求 WHERE 條件

練習題

1. 建立 pet 表（定長 CHAR、ENUM、DATE），匯入資料檔（Windows \r\n）
2. 使用 LOAD DATA INFILE 匯入，指定分隔符與 NULL 處理
3. mysqlimport 命令列等效寫法
4. 設計分批刪除 100 萬筆舊資料腳本，控制鎖與複製延遲

1. 上一篇文章： MySQL INSERT/REPLACE/LOAD DATA 資料寫入全方位指南：語法、效能、批次策略與陷阱
2. 下一篇文章： MySQL 客戶端連線完整指南：mysql 命令參數、連線協議、選項檔與批次模式

MySQL 資料庫相關知識

phpMyAdmin 啟用 InnoDB 並設定外鍵：修改 my.ini、重啟驗證、關聯建立步驟

phpNow 整合環境預設關閉 InnoDB。本文記錄修改

MySQL 1067 錯誤機房斷電後恢復：刪除 ibdata1/ib_logfile 重建並導入備份 (含圖文步驟)

機房斷電導致 MySQL 1067 無法啟動：標準災難恢復流

MySQL 聯合索引實戰：員工-部門多對多關係防重複插入

業務場景：員工與部門多對多，中間表 employee_dep

徹底解決 MySQL 中文亂碼：Illegal mix of collations 錯誤完整排查指南

字符集層級總覽 MySQL 涉及多層字符集設定，常見層級如下