MySQL 基礎操作全解析：INSERT、UPDATE、DELETE 進階用法與避坑指南

MySQL 基礎操作全解析：INSERT、UPDATE、DELETE

數據操作語言（DML）是日常開發最高頻環節。MySQL 在標準 SQL 基礎上提供了批量寫入、REPLACE、ON DUPLICATE KEY UPDATE 等極具實用價值的擴展語法，掌握它們能大幅提升開發效率與系統效能。

一、INSERT 多樣化寫法

1. 標準單行插入

INSERT INTO mytable(col1, col2, col3) VALUES ('val1', 'val2', 4);

• 每句必須以分號結尾
• 自增列可顯式指定值（遷移數據時極便利）

2. 複製表數據

INSERT INTO mytable(col1, col2, col3) 
SELECT col1, col2, col3 FROM copytable;

3. 建表並複製結構/數據

-- 僅結構
CREATE TABLE newtable SELECT * FROM copytable WHERE 1=2;
-- 結構+數據
CREATE TABLE newtable SELECT * FROM copytable;

注意：索引、約束、觸發器不會複製，需手動補充。

4. 批量插入（核心高效寫法）

INSERT INTO mytable (col1, col2, col3) 
VALUES ('A','VAL1',1), ('B','VAL2',2), ('C','VAL3',3);

• 單語句萬行級僅需秒級
• 應用層拼接 SQL 字串極簡單，極大簡化批量導入邏輯

二、UPDATE 進階模式

1. 標準更新

UPDATE mytable SET col1='A', col2='val1', col3=1 WHERE id='0001';

2. 多表聯動更新

UPDATE newtable a, oldtable b 
SET a.col1=b.col1, a.col2=b.col2 
WHERE a.id=b.id;

MySQL 不支援標準 SQL 的 FROM 子句更新寫法。

3. REPLACE INTO：存在則替換

REPLACE INTO mytable (id, name) VALUES ('01','hello'), ('02','haha');

• 本質：DELETE + INSERT，主鍵/唯一鍵衝突時刪舊插新
• 受影響行數：插入 2 行 → 2；替換 2 行 → 4（2 刪 + 2 插）
• 缺點：未指定列會重置為默認值，慎用於寬表

4. ON DUPLICATE KEY UPDATE：精準增量更新（推薦）

INSERT INTO mytable(id, name, col) VALUES (78,'456','55'), (88,'456','d')
ON DUPLICATE KEY UPDATE name=VALUES(name), col=VALUES(col);

-- 僅更新部分列
INSERT INTO mytable(id, name, col) VALUES (78,'456','55')
ON DUPLICATE KEY UPDATE name=VALUES(name);

• 衝突時只更新指定列，其他列保持原值
• VALUES(col) 引用插入值，避免重複寫字面量
• 受影響行數：新增 1，更新 2

三、DELETE 與 TRUNCATE

1. 條件刪除

DELETE FROM mytable WHERE id='001';

2. 清空表

TRUNCATE TABLE mytable;

• DDL 操作，不可回滾，不觸發觸發器
• 自增計數器歸零
• 大表清空極快（釋放數據頁而非逐行刪除）

四、日期類型隱形大坑

MySQL 允許非法日期寫入（如 '2024-13-45'），不報錯但存為 '0000-00-00 00:00:00'。

• 應用層讀取時常報錯（JDBC 預設拋異常）
• 解決：JDBC URL 加 zeroDateTimeBehavior=convertToNull 或 =round
• 最佳實踐：應用層嚴格驗證，資料庫層設 sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE

五、總結速查