一、INSERT 與 REPLACE
INSERT 兩種語法
-- 標準語法：欄位與值分離
INSERT INTO users (id, name, age) VALUES (123, '姚明', 25);
-- MySQL 專屬 SET 語法：欄值成對
INSERT INTO users SET id = 123, name = '姚明', age = 25;
- 自增欄位可省略；
VALUES() 為空則全用預設值；省略欄位清單時值須與全表欄位順序數量完全一致。
批次 INSERT (MySQL 擴展)
INSERT INTO users (name, age) VALUES
('姚明', 25), ('比爾·蓋茲', 50), ('火星人', 600);
- 單條 SQL 插入多列，**大幅降低解析/優化開銷**，效能遠優於循環單條執行。
- 每組值必須用括號包裹，逗號分隔；欄位數與值數必須一致。
REPLACE：主鍵衝突時「刪舊插新」
REPLACE INTO users (id, name, age) VALUES (123, '趙本山', 50);
- 表**必須有唯一索引且不允許 NULL**，否則退化為 INSERT。
- 返回影響行數：1=純插入，2=一筆衝突 (刪1插1)，>2=多唯一索引多衝突。
- **原子操作** = DELETE + INSERT，無需顯式事務。
- SET 語法同樣支援：
REPLACE INTO users SET id=123, name='趙本山', age=50;
二、UPDATE
UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
- 無
WHERE 則更新**全表** (極度危險)。
- 型別不匹配自動轉換：數值超範圍截取邊界值、字串過長截斷、非空欄位設 NULL 轉為預設值 (數值 0、字串空串)。
- **不更新情況**：WHERE 無匹配行、或新值與舊值完全相同。
- 返回**實際修改行數** (不含匹配但未修改行)；C API/JDBC 可取得匹配行數。
TIMESTAMP 欄位在其他欄位更新時自動刷新。
UPDATE vs REPLACE 差異
| 特性 | UPDATE | REPLACE |
|---|
| 無匹配行 | 不做任何事 | 插入新行 |
| 部分欄位更新 | 支援 | 全行刪除重插 (所有欄位更新) |
| 適用場景 | 修改現有資料 | Upsert (主鍵存在則更新，否則插入) |
三、DELETE 與 TRUNCATE TABLE
| 特性 | DELETE | TRUNCATE TABLE |
|---|
| 條件篩選 | WHERE 任意條件 | 僅清空全表 |
| 速度 | 逐行刪除，較慢 | 釋放資料頁，**極快** |
| 返回值 | 刪除行數 | 固定 0 |
| 自增計數器 | 保留 (除非 WHERE 1) | **重置為 1** |
| 觸發器 | 觸發 BEFORE/AFTER DELETE | **不觸發** |
| ROLLBACK | 支援 (事務中) | **不支援** (隱式提交) |
| ORDER BY / LIMIT | 支援 (MySQL 擴展) | 不支援 |
保留自增值技巧
DELETE FROM table WHERE 1; -- 掃描全表刪除，保留自增最大值，但效能極差
-- 建議：TRUNCATE 後手動 ALTER TABLE AUTO_INCREMENT = 下一值
DELETE 進階用法
-- 刪除前 6 筆 name='Mike' (配合 ORDER BY 確定順序)
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;
效能與最佳實踐摘要
- **批次寫入** 一律用多值
INSERT / REPLACE，避免循環單條。
- **Upsert** 現代語法：
INSERT ... ON DUPLICATE KEY UPDATE ... (比 REPLACE 更靈活、不觸發刪除觸發器、保留自增值)。
- **大量清表** 必用
TRUNCATE；需保留自增值則 TRUNCATE + ALTER AUTO_INCREMENT。
- **條件刪除大量資料** 分批
DELETE ... LIMIT 10000 循環，避免長鎖表與日誌暴漲。
- **索引維護成本**：每個索引增加寫入開銷，定期
ANALYZE TABLE 更新統計資訊。
