291 lines
10 KiB
Markdown
291 lines
10 KiB
Markdown
---
|
||
tags: [MySQL, DML, INSERT, UPDATE, DELETE]
|
||
create time: 2026-05-16 00:00
|
||
---
|
||
|
||
# DML — 增删改
|
||
|
||
## 概述
|
||
|
||
DML（Data Manipulation Language）是日常使用最频繁的 SQL 类别——**增、改、删**构成了应用和数据库之间的数据交互主线。
|
||
|
||
> [!QUESTION] 思考：为什么说「写」比「读」更复杂？
|
||
> SELECT 只需要找到匹配的数据，而 INSERT / UPDATE / DELETE 不仅要改变状态，还要处理**并发冲突、约束校验、外键级联、触发器副作用**。理解这些隐藏行为，才能写出既正确又高效的写入逻辑。
|
||
|
||
本节聚焦 MySQL 特有的高效写入技巧和常见陷阱。
|
||
|
||
## INSERT
|
||
|
||
### 基础插入
|
||
|
||
```sql
|
||
INSERT INTO users (username, email, status)
|
||
VALUES ('alice', 'alice@example.com', 1);
|
||
|
||
-- 批量插入（性能远高于逐条插入）
|
||
INSERT INTO users (username, email, status) VALUES
|
||
('bob', 'bob@example.com', 1),
|
||
('charlie','charlie@example.com', 0),
|
||
('david', 'david@example.com', 1);
|
||
```
|
||
|
||
> [!TIP] MySQL 8.0.19+：INSERT ... RETURNING
|
||
> 和 PostgreSQL 一样，MySQL 从 8.0.19 起支持 `RETURNING` 子句，免去二次查询：
|
||
> ```sql
|
||
> INSERT INTO users (username, email, status)
|
||
> VALUES ('eve', 'eve@example.com', 1)
|
||
> RETURNING id; -- 直接拿到自增 ID
|
||
> ```
|
||
> **典型场景**：插入后需要立即获取新记录的自增主键做级联操作。替代了老方案中 `INSERT ... ; SELECT LAST_INSERT_ID();` 两条语句的组合。
|
||
|
||
### INSERT ... ON DUPLICATE KEY UPDATE
|
||
|
||
处理"存在则更新，不存在则插入"的场景：
|
||
|
||
```sql
|
||
INSERT INTO user_stats (user_id, total_orders, total_spent)
|
||
VALUES (1001, 5, 299.50)
|
||
ON DUPLICATE KEY UPDATE
|
||
total_orders = total_orders + VALUES(total_orders),
|
||
total_spent = total_spent + VALUES(total_spent);
|
||
```
|
||
|
||
> [!TIP] 常见陷阱
|
||
> - **多唯一键冲突**：如果有多条唯一键匹配同一行，只更新一次（返回 Row Count = 2）
|
||
> - **LAST_INSERT_ID()**：INSERT 时返回新 ID；ON DUPLICATE KEY UPDATE 时自动改写为旧行的主键 ID（方便级联操作）
|
||
> - **VALUES() 废弃警告**：MySQL 8.0.20+ 对 `VALUES(col)` 发出 DEPRECATION WARNING。对于单行插入无需改动，**多行批量插入**需改用子查询或分段处理。
|
||
|
||
```mermaid
|
||
flowchart TD
|
||
A["INSERT 语句"] --> B{"主键或唯一键\n是否存在"}
|
||
B -->|不存在| C["执行 INSERT"]
|
||
B -->|已存在| D["执行 UPDATE\nON DUPLICATE KEY UPDATE 子句"]
|
||
C --> E["返回 Row Count = 1"]
|
||
D --> F["返回 Row Count = 2\n匹配行加实际更新"]
|
||
|
||
style B fill:#FF9F43,color:#000
|
||
style C fill:#00D866,color:#fff
|
||
style D fill:#00B6BC,color:#fff
|
||
```
|
||
|
||
> [!NOTE] Row Count 的含义
|
||
> - `Row Count = 1`：正常插入新记录
|
||
> - `Row Count = 2`：唯一键冲突，走了 UPDATE 路径（但 UPDATE 没有实际改变值也算 2）
|
||
> - `Row Count = 0`：唯一键冲突，且 UPDATE 后的值与原来相同
|
||
|
||
### REPLACE INTO
|
||
|
||
当唯一键冲突时，**先删除旧行再插入新行**。
|
||
|
||
```sql
|
||
REPLACE INTO users (id, username, email) VALUES (1, 'alice_new', 'new@email.com');
|
||
```
|
||
|
||
```mermaid
|
||
sequenceDiagram
|
||
participant S as Server
|
||
participant DB as Database
|
||
|
||
S->>DB: REPLACE INTO users ...
|
||
Note over S,DB: Step 1: DELETE 已有记录
|
||
S->>DB: DELETE FROM users WHERE pk = 1
|
||
Note over S,DB: Step 2: INSERT 新记录
|
||
S->>DB: INSERT INTO users ...
|
||
|
||
Note over S,DB: 如果外键引用了被删行\n会触发 ON DELETE CASCADE
|
||
```
|
||
|
||
> [!WARNING] REPLACE vs ON DUPLICATE KEY UPDATE
|
||
> - **REPLACE**：本质是 DELETE + INSERT，会导致自增 ID 变化、触发 BEFORE/AFTER DELETE 钩子、级联外键删除
|
||
> - **ON DUPLICATE KEY UPDATE**：原地更新，不影响其他字段和自增值
|
||
>
|
||
> **优先用 `ON DUPLICATE KEY UPDATE`**，除非你确实需要完整的「删+插」语义。
|
||
|
||
### INSERT ... SELECT
|
||
|
||
从另一张表或查询结果批量插入数据——这是日常开发中比 `VALUES` 批量插入更高频的用法。
|
||
|
||
```sql
|
||
-- 基础：从查询结果插入
|
||
INSERT INTO user_archive (id, username, email, archived_at)
|
||
SELECT id, username, email, NOW()
|
||
FROM users
|
||
WHERE status = 0 AND updated_at < '2025-01-01';
|
||
|
||
-- 搭配聚合：插入每日统计快照
|
||
INSERT INTO daily_stats (stat_date, order_count, total_amount)
|
||
SELECT CURDATE(), COUNT(*), SUM(amount)
|
||
FROM orders
|
||
WHERE DATE(created_at) = CURDATE();
|
||
```
|
||
|
||
> [!QUESTION] INSERT ... SELECT 会锁表吗？
|
||
> 这取决于事务隔离级别和索引情况：
|
||
> - 在 **RC（Read Committed）** 下：只锁定被插入的目标表，对源表加短暂的共享锁
|
||
> - 在 **RR（Repeatable Read）** 下：源表的读取走一致性快照，**不会阻塞源表的写入**
|
||
> - 目标表：插入的行会加行锁，大批量插入时注意不要超长事务
|
||
|
||
> [!CAUTION] INSERT ... SELECT 的两个常见坑
|
||
> 1. **SELECT 中不能引用正在被插入的目标表**（MySQL 会报 `Table is specified twice`）。解决办法：嵌套一层子查询
|
||
> ```sql
|
||
> -- ❌ 报错：直接引用目标表
|
||
> INSERT INTO orders_backup SELECT * FROM orders WHERE id IN (SELECT id FROM orders WHERE ...);
|
||
> -- ✅ 正确：用子查询隔离
|
||
> INSERT INTO orders_backup SELECT * FROM orders WHERE id IN (SELECT t.id FROM (SELECT id FROM orders WHERE ...) t);
|
||
> ```
|
||
> 2. **大批量插入时分批执行**，避免长事务持有过多锁。可以用 `LIMIT` + 循环分批：
|
||
> ```sql
|
||
> -- 每次只插入 5000 条，循环直到 affected_rows = 0
|
||
> INSERT INTO user_archive
|
||
> SELECT * FROM users WHERE status = 0 LIMIT 5000;
|
||
> ```
|
||
|
||
## UPDATE
|
||
|
||
UPDATE 的核心原则只有一条：**精准定位、最小影响**。先思考「哪些行需要改」，再写 SET 子句。
|
||
|
||
```sql
|
||
-- 基础更新：定位到单行
|
||
UPDATE users SET status = 0 WHERE id = 100;
|
||
|
||
-- 多字段同时更新
|
||
UPDATE users SET
|
||
email = 'new@email.com',
|
||
updated_at = NOW()
|
||
WHERE email = 'old@email.com' AND status = 1;
|
||
|
||
-- JOIN 批量更新（从关联表同步数据）
|
||
UPDATE articles a
|
||
JOIN categories c ON a.category_id = c.id
|
||
SET a.category_name = c.name
|
||
WHERE a.category_name IS NULL;
|
||
```
|
||
|
||
> [!QUESTION] 为什么 UPDATE 忘记带 WHERE 这么危险？
|
||
> `UPDATE users SET status = 0;` 会把所有用户的状态设为禁用。MySQL 有一个安全措施叫 `sql_safe_updates`：
|
||
> ```sql
|
||
> SET sql_safe_updates = 1;
|
||
> -- 此时不带 WHERE 或不带主键条件的 UPDATE 会被拒绝
|
||
> -- 生产环境建议在连接层开启此选项
|
||
> ```
|
||
|
||
### LIMIT 与 ORDER BY
|
||
|
||
MySQL 特有的扩展：**UPDATE 可以加 LIMIT 控制影响行数**。
|
||
|
||
```sql
|
||
-- 只更新符合条件的前 100 条（配合 ORDER BY 可控）
|
||
UPDATE users SET status = 0
|
||
WHERE status = 1 AND created_at < '2024-01-01'
|
||
ORDER BY created_at ASC
|
||
LIMIT 100;
|
||
```
|
||
|
||
## DELETE vs TRUNCATE
|
||
|
||
DELETE 和 TRUNCATE 都能清空或减少表数据，但**底层机制完全不同**。选错可能导致性能灾难或数据意外丢失。
|
||
|
||
| 特性 | DELETE | TRUNCATE |
|
||
|------|--------|----------|
|
||
| **类型** | DML | DDL |
|
||
| **可回滚** | ✅ 事务内可 ROLLBACK | ❌ 隐式提交，不可回滚 |
|
||
| **WHERE 过滤** | ✅ 可以 | ❌ 全删 |
|
||
| **AUTO_INCREMENT** | 保留当前值 | 重置为 1 |
|
||
| **触发器** | 触发 DELETE 触发器 | 不触发 |
|
||
| **速度** | 逐行删除较慢 | 直接重建表极快 |
|
||
| **返回值** | 影响的行数 | 无返回值 |
|
||
| **锁粒度** | 行锁（可带 WHERE） | 表级元数据锁 |
|
||
|
||
### DELETE 进阶用法
|
||
|
||
```sql
|
||
-- DELETE 带 JOIN（MySQL 特有语法）
|
||
DELETE u FROM users u
|
||
LEFT JOIN orders o ON u.id = o.user_id
|
||
WHERE o.id IS NULL;
|
||
-- 删除所有没有订单的用户
|
||
|
||
-- 多表联合删除（同时删用户 + 订单）
|
||
DELETE t1, t2 FROM users t1
|
||
INNER JOIN orders t2 ON t1.id = t2.user_id
|
||
WHERE t1.status = 0;
|
||
```
|
||
|
||
> [!NOTE] ORDER BY + LIMIT 在 DELETE 中同样可用
|
||
> ```sql
|
||
> -- 只删除符合条件的前 50 条（按创建时间最早的优先）
|
||
> DELETE FROM users WHERE status = 0
|
||
> ORDER BY created_at ASC
|
||
> LIMIT 50;
|
||
> ```
|
||
|
||
---
|
||
|
||
## 高效写入策略
|
||
|
||
当需要处理大量数据时，**写入方式的选择直接影响性能和稳定性**。下面按数据量级给出分级方案：
|
||
|
||
```mermaid
|
||
flowchart TD
|
||
A["大量数据写入"] --> B{数据量}
|
||
B -->|小于 1 万条| C["单条 INSERT + 事务包裹"]
|
||
B -->|1 万至 100 万条| D["分批 INSERT\n每批 500 到 2000 条"]
|
||
B -->|大于 100 万条| E["LOAD DATA INFILE"]
|
||
|
||
C --> F["BEGIN; INSERT... COMMIT;"]
|
||
D --> G["每批独立事务\n降低锁竞争"]
|
||
E --> H["绕过 SQL 解析\n直接写数据文件"]
|
||
|
||
style E fill:#00D866,color:#fff
|
||
style D fill:#FF9F43,color:#000
|
||
```
|
||
|
||
```sql
|
||
-- LOAD DATA INFILE 示例（最快的批量导入方式）
|
||
LOAD DATA LOCAL INFILE '/tmp/users.csv'
|
||
INTO TABLE users
|
||
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
|
||
LINES TERMINATED BY '\n'
|
||
(username, email, @status) -- @variable 用于预处理
|
||
SET status = CASE @status WHEN 'active' THEN 1 ELSE 0 END;
|
||
```
|
||
|
||
### 分批 INSERT 代码示例
|
||
|
||
对于中等规模数据，**事务 + 分批次** 是最实用的方案。以下 Go 伪代码展示了核心思路：
|
||
|
||
```go
|
||
const batchSize = 1000
|
||
|
||
rows := generateRecords() // 假设返回 50000 条记录
|
||
tx := db.Begin() // 外层可开启大事务
|
||
|
||
for i := 0; i < len(rows); i += batchSize {
|
||
end := min(i+batchSize, len(rows))
|
||
batch := rows[i:end]
|
||
|
||
// 构建动态 INSERT
|
||
cols := "(username, email, status)"
|
||
placeholders := strings.Repeat("(?, ?, ?),", len(batch))
|
||
sql := fmt.Sprintf("INSERT INTO users %s VALUES %s", cols, placeholders[:len(placeholders)-1])
|
||
|
||
var args []any
|
||
for _, r := range batch {
|
||
args = append(args, r.Username, r.Email, r.Status)
|
||
}
|
||
|
||
tx.Exec(sql, args...) // 单批提交
|
||
}
|
||
tx.Commit()
|
||
```
|
||
|
||
> [!CAUTION] 批量插入注意事项
|
||
> - **包大小限制**：MySQL 默认 `max_allowed_packet` 为 64MB，超大批次会报 `Packet too large` 错误
|
||
> - **长事务锁表**：单个大事务持有锁的时间越长，死锁概率越高 → **每批独立 commit** 更安全
|
||
> - **自增 ID 碎片**：大批量 INSERT 会导致自增 ID 跳跃，不影响功能，但会影响 `AUTO_INCREMENT` 当前值的准确性
|
||
|
||
## 关联笔记
|
||
|
||
- [[hhs/GORM/03-CRUD 操作]] — GORM 的 Create / First / Find 等方法的 SQL 生成机制
|
||
- [[hhs/GORM/11-批量操作]] — GORM 中 CreateInBatches 等批量优化手段
|