MySQL: MySQL数据增删改操作详解
最后更新：2026-08-26
数据的增删改是数据库的基本操作——INSERT、UPDATE、DELETE 是 DML 的核心。
本课系统讲解数据的写入、修改和删除。
graph TB
A[DML 操作] --> B[INSERT 插入]
A --> C[UPDATE 更新]
A --> D[DELETE 删除]
B --> B1[单行插入]
B --> B2[多行插入]
B --> B3[子查询插入]
B --> B4[UPSERT]
C --> C1[条件更新]
C --> C2[JOIN 更新]
D --> D1[条件删除]
D --> D2[TRUNCATE 清空]
1. 你将学到
- INSERT 单行/多行/子查询插入
- UPDATE 条件更新
- DELETE 条件删除
- TRUNCATE 清空表
- INSERT ON DUPLICATE KEY UPDATE
2. 一个真实的故事
(1) 痛点：手动逐条写入效率极低
库存管理系统每天处理 10,000+ 条数据写入，运维人员手动逐条 INSERT/UPDATE，不仅效率极低，还经常因为复制粘贴出错导致数据错乱。一次批量调价操作，逐条 UPDATE 执行了 3 个小时，期间还因为手滑写错了 WHERE 条件，把整张表的价格都改成了 0。
(2) 批量操作+事务的解法
用批量 INSERT + 事务 UPDATE + 条件 DELETE，配合 INSERT ON DUPLICATE KEY UPDATE，一次操作处理所有数据。
| 维度 | 逐条操作 | 批量操作+事务 |
|---|---|---|
| 10,000条写入 | ~3小时 | ~3分钟 |
| 出错风险 | 高（手动易错） | 低（事务保护） |
| 网络往返 | 10,000次 | 1次 |
| 速度提升 | — | 50倍 |
3. INSERT 插入数据
▶ 示例：单行插入
SQL
-- 完整插入
INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@email.com', 25);
-- 省略字段名（需按顺序）
INSERT INTO users VALUES (1, 'bob', 'bob@email.com', 30);
▶ 示例：多行插入
SQL
INSERT INTO products (name, price, category) VALUES
('iPhone', 999, 'Phone'),
('MacBook', 1999, 'Laptop'),
('iPad', 599, 'Tablet');
▶ 示例：子查询插入
SQL
-- 从查询结果插入
INSERT INTO user_archive (id, name, email)
SELECT id, name, email FROM users WHERE status = 'deleted';
4. UPDATE 更新数据
▶ 示例：条件更新
SQL
-- 更新单条记录
UPDATE users SET email = 'new@email.com' WHERE id = 1;
-- 更新多条记录
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';
-- 多字段更新
UPDATE users SET age = age + 1, status = 'active' WHERE id = 1;
▶ 示例：UPDATE + JOIN
SQL
-- 关联更新
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.id
SET o.status = 'priority'
WHERE c.level = 'VIP';
5. DELETE 删除数据
▶ 示例：条件删除
SQL
-- 删除单条记录
DELETE FROM users WHERE id = 1;
-- 删除多条记录
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2025-01-01';
-- 关联删除
DELETE o FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'banned';
(1) TRUNCATE vs DELETE
| 维度 | TRUNCATE | DELETE |
|---|---|---|
| 速度 | 极快 | 慢 |
| 事务 | 不可回滚 | 可回滚 |
| 触发器 | 不触发 | 触发 |
| AUTO_INCREMENT | 重置 | 不重置 |
| WHERE | 不支持 | 支持 |
SQL
-- 清空表（不可回滚）
TRUNCATE TABLE temp_data;
6. INSERT ON DUPLICATE KEY UPDATE
主键或唯一键冲突时执行更新。
▶ 示例：UPSERT 操作
SQL
-- 插入或更新
INSERT INTO product_stats (product_id, view_count, last_viewed)
VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE
view_count = view_count + 1,
last_viewed = NOW();
-- 批量 UPSERT
INSERT INTO user_scores (user_id, score) VALUES
(1, 100), (2, 200), (3, 300)
ON DUPLICATE KEY UPDATE
score = VALUES(score);
7. REPLACE INTO
先删除再插入（冲突时）。
▶ 示例：REPLACE
SQL
-- 存在则替换，不存在则插入
REPLACE INTO users (id, username, email)
VALUES (1, 'alice_updated', 'alice_new@email.com');
⚠️ 注意： REPLACE 会删除旧行再插入，触发 DELETE 和 INSERT 触发器，AUTO_INCREMENT 会变。
8. 安全操作建议
| 操作 | 建议 |
|---|---|
| UPDATE/DELETE | 必须带 WHERE，先 SELECT 确认影响范围 |
| 大批量操作 | 分批执行，每批 1000-5000 行 |
| 生产环境 | 开启事务，确认后再 COMMIT |
| 备份 | 操作前 mysqldump 备份 |
▶ 示例：安全更新流程
SQL
-- 1. 先查询确认
SELECT COUNT(*) FROM orders WHERE status = 'cancelled' AND created_at < '2025-01-01';
-- 2. 开启事务
START TRANSACTION;
-- 3. 执行删除
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2025-01-01';
-- 4. 确认无误后提交
COMMIT;
-- 或发现问题回滚
-- ROLLBACK;
❓ 常见问题
Q INSERT 时字段顺序必须和表定义一致吗？
A 只有省略字段名时才需一致。推荐明确写出字段名，不依赖顺序。
Q DELETE 后能恢复吗？
A 事务内可以 ROLLBACK。已 COMMIT 的需要从备份恢复。TRUNCATE 无法回滚。
Q 大批量 INSERT 怎么优化？
A 多行 VALUES、关闭自动提交、禁用索引、用 LOAD DATA INFILE。
Q INSERT 和 REPLACE 区别？
A INSERT 遇到唯一键冲突报错，REPLACE 先删再插（id 会变），推荐用 ON DUPLICATE KEY UPDATE。
Q DELETE 和 TRUNCATE 区别？
A DELETE 逐行删除可回滚，TRUNCATE 清空表不可回滚但极快。
📖 小节
- INSERT 插入数据，支持单行/多行/子查询
- UPDATE 更新数据，必须带 WHERE
- DELETE 删除数据，必须带 WHERE
- TRUNCATE 清空表，速度快但不可回滚
- INSERT ON DUPLICATE KEY UPDATE 实现 UPSERT
- 操作前先查询确认，生产环境用事务保护
📝 作业
-
基础题（难度⭐）：向
users表插入 5 条测试数据。 -
进阶题（难度⭐⭐）：用 INSERT ON DUPLICATE KEY UPDATE 实现访问计数器。
-
挑战题（难度⭐⭐⭐）：编写安全删除脚本：删除 30 天前的临时数据，使用事务保护。
