🗄️ 第05章 · 增删改与事务

INSERT UPDATE DELETE 事务 ACID

增删改与事务¶

查询是读表，这章是“写表”：INSERT 加数据、UPDATE 改数据、DELETE 删数据。更关键的是，这些写操作必须用事务（Transaction）包起来，否则一笔写一半崩了，账就对不上了。本章讲清楚三个 DML 语句和事务的 ACID 到底在防什么。

插入 INSERT¶

最基础的写入。单行插入、多行批量插入、从另一个查询结果插入，三种姿势：

-- 单行插入（列出所有需要的列）
INSERT INTO bands (name, cls, color)
VALUES ('Poppin\'Party', 'poppin', '#E91E63');

-- 多行批量插入（一条语句插多行，比循环插快得多）
INSERT INTO bands (name, cls, color) VALUES
  ('Afterglow',      'afterglow', '#FF9800'),
  ('Pastel*Palettes','pastel',    '#9C27B0'),
  ('Roselia',        'roselia',   '#9E9E9E');

-- 从另一张表查出来插进去（备份/导数据常用）
INSERT INTO bands_backup (id, name, cls)
SELECT id, name, cls FROM bands WHERE id <= 5;

-- 插入冲突时更新：主键或唯一键撞了就走 UPDATE 分支
INSERT INTO bands (id, name, cls, color) VALUES (1, 'Poppin\'Party', 'poppin', '#E91E63')
  ON DUPLICATE KEY UPDATE color = '#E91E63';

ON DUPLICATE KEY UPDATE 是“见缝插针”的利器：存在相同主键/唯一键就更新，不存在就插入，常用于“幂等写入”（比如定时任务刷数据，重复跑不产生重复行）。

更新 UPDATE¶

更新必须带 WHERE，不带就是全表改——这是数据库史上最著名的灾难现场之一。

-- 精确更新某一条
UPDATE bands SET color = '#00E676' WHERE id = 7;

-- 按条件批量更新（注意 WHERE 条件要能命中索引）
UPDATE characters SET height = '162cm' WHERE band_id = 1;

-- 自增更新（点赞数 +1）
UPDATE bands SET id = id;             -- 占位，实际不要这样
UPDATE stories SET like_count = like_count + 1 WHERE id = 10;

-- 用另一表的值更新（JOIN 更新）
UPDATE stories s
  JOIN bands b ON s.band_id = b.id
   SET s.title = CONCAT(b.name, ' - ', s.title)
 WHERE b.id = 1;

安全提示：UPDATE / DELETE 不带 WHERE = 全表清空/改差。习惯性地先 SELECT 一把确认要动的行，翻 WHERE 拼写无误，再改回 UPDATE。永远先带上 WHERE 能命中主键或唯一键的条件。

删除 DELETE¶

-- 删掉某一条
DELETE FROM bands WHERE id = 12;

-- 按条件删除
DELETE FROM characters WHERE band_id = 6;

-- 清空整张表（保留表结构，重置自增）
TRUNCATE TABLE bands;

-- 删表并重建结构（危险，连结构一起没了）
DROP TABLE bands;

TRUNCATE 和 DELETE FROM 的区别：前者清空表并重置自增计数器、很快且不可回滚；后者逐行删除、可搭配 WHERE、可在事务里回滚。选哪个视需求而定。

事务：ACID 是什么¶

现实世界里“转账十块”要同时完成“A 减十、B 加十”，只做了一半就是脏账。事务（Transaction）把这组操作打包，保证要么全部成功、要么全部失败。这就是 ACID：

特性	全称	含义
Atomicity	原子性	一组操作要么全成、要么全失败，不搞半吊子
Consistency	一致性	事务前后数据都满足约束（主键唯一、外键有效等）
Isolation	隔离性	事务之间互不干扰，看到的是一致快照
Durability	持久性	一旦提交，数据持久落盘，断电也不丢

InnoDB 是 MySQL 里支持事务的存储引擎，这也是生产环境几乎只用它的原因。MyISAM 不支持事务，一崩就可能数据损坏。

事务的三种操作¶

-- 开启事务
START TRANSACTION;

-- 原子操作：A 转 10 给 B
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- 一切正常 → 提交，永久生效
COMMIT;

-- 出问题了 → 回滚，当作什么都没发生
ROLLBACK;

默认提交模式：SET autocommit = 1 时每条语句自动提交，不开事务；SET autocommit = 0 时须手动 COMMIT。日常要保证原子性，就用显式 START TRANSACTION ... COMMIT/ROLLBACK。

# 在命令行里演示一个真实回滚
mysql -u bangdream -p bangdream -e "
  START TRANSACTION;
  INSERT INTO bands(name,cls) VALUES ('临时测试乐队','test');
  ROLLBACK;            -- 撤掉上面那条插入
  SELECT COUNT(*) AS 乐队数 FROM bands;   -- 发现还是原来的数量
"

事务隔离级别¶

多个事务并发改同一行时会互相影响，MySQL 提供了四种隔离级别，从松到紧：

隔离级别	会出现的问题
`READ UNCOMMITTED`	脏读、不可重复读、幻读
`READ COMMITTED`	不可重复读、幻读（最常用之一）
`REPEATABLE READ`	幻读（MySQL 默认级别，靠 MVCC 基本消除）
`SERIALIZABLE`	几乎无并发问题，但性能最差

-- 查看当前隔离级别（InnoDB 默认 REPEATABLE READ）
SELECT @@transaction_isolation;
-- 结果示例: REPEATABLE-READ

-- 会话级临时调整（通常不需要，了解即可）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

易错点：默认级别是 REPEATABLE READ，它用 MVCC（多版本并发控制）读旧快照，所以同一个事务里连续 SELECT 看到的数据是“开始事务那一刻”的一致快照。这不一定是 bug，但理解它能避免“为什么我改了但事务里读不到”的困惑。

案例场景：真实业务里的原子写入¶

假设要给 bangdream 库一次性插入一支新乐队和它的两条剧情，必须同生死——用事务包起来：

START TRANSACTION;

INSERT INTO bands (name, cls, color) VALUES ('MyGO!!!!!', 'mygo', '#4A148C');
SET @new_band_id = LAST_INSERT_ID();   -- 拿刚插入的乐队主键

INSERT INTO stories (band_id, title) VALUES
  (@new_band_id, '迷子の春'),
  (@new_band_id, '邂逅の夜');

COMMIT;   -- 乐队和剧情一起落库，要么全有，要么全无
-- 出错时执行 ROLLBACK; 即可撤销整件套

LAST_INSERT_ID() 返回当前连接最近一次 AUTO_INCREMENT 生成的值，是“插入主表后拿回子表的关联键”的标准做法。