CRUD 四条语句
库表建好之后，日常打交道的就是增删改查。拿用户表和余额字段举例：
-- 新增
insert into t_user (username, mobile, balance)
values ('alice', '13800000000', 100.00);
-- 查询
select id, username, balance
from t_user
where id = 1;
-- 修改：带条件，防止余额被扣成负数
update t_user
set balance = balance - 50.00
where id = 1 and balance >= 50.00;
-- 删除
delete from t_user
where id = 1;
update 和 delete 必须带 where，这是写 SQL 的底线。真要在测试环境清空表，用 truncate，不要用不带条件的 delete，后者会逐行删除、写大量 undo log，还可能锁住整张表。
事务：要么全成功
转账是最典型的例子：A 扣 100，B 加 100，两步必须一起成功或一起失败。
start transaction;
update t_account set balance = balance - 100.00 where id = 1 and balance >= 100.00;
update t_account set balance = balance + 100.00 where id = 2;
-- 两条都影响 1 行才提交，否则回滚
commit;
-- 出错时执行 rollback;
MySQL 默认 autocommit = 1，每条 SQL 自动提交。执行 start transaction 之后自动提交暂时关闭，直到 commit 或 rollback。如果客户端连接中途断开，未提交的事务会自动回滚。
在 JDBC 里对应的是 connection.setAutoCommit(false)，业务代码执行完再 commit()，捕获异常时 rollback()。Spring 的 @Transactional 本质上也是在帮你做这件事。
隔离级别与并发问题
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不会 | 可能 | 可能 |
| REPEATABLE READ（默认） | 不会 | 不会 | InnoDB 下基本不会 |
| SERIALIZABLE | 不会 | 不会 | 不会 |
MySQL 8 默认是 REPEATABLE READ，靠 MVCC 保证同一事务内多次读取结果一致。查看和修改：
select @@transaction_isolation;
set session transaction isolation level read committed;
transaction_isolation 是 8.0 的变量名，5.7 里叫 tx_isolation，照抄老文章会报未知变量。
分页与批量操作
列表接口离不开分页，最常见的写法是 limit offset, size：
select id, username, balance
from t_user
order by id
limit 20 offset 40;
offset 越大越慢，因为数据库要先把前面 40 行扫出来再丢掉。翻到几百页之后接口明显变慢，就是深分页的典型表现。优化的方向是用“上一页最后一条的 id”做游标：where id > 上一页最大 id order by id limit 20，前提是排序字段有索引且不会重复。
批量插入用一条 SQL 带多个 values，比循环单条插入快一个数量级：
insert into t_user (username, mobile)
values ('a', '13800000001'), ('b', '13800000002');
一次别塞太多，几百到一千条一批比较稳，太多会超过 max_allowed_packet，也会让事务过大。批量更新可以按主键 case when，或者拆成多条在同一个事务里执行。
分页查询一定要带稳定的排序。只写 limit 不写 order by，数据库返回的顺序没有保证，翻页时可能出现重复或漏数据。
常见坑
1. update 忘了 where
这是最危险的一类事故：一条 update t_user set status = 0 把全表用户都禁用了。执行前先把 where 条件单独写成 select 验证影响范围，再放进事务里执行，确认行数符合预期才 commit。
2. 以为改了就会提交
命令行客户端和图形工具默认自动提交，但 JDBC 关了自动提交之后忘了 commit，数据在连接关闭时全部回滚。现象是“程序没报错，数据就是没有”。排查时先看当前连接的 autocommit 值。
3. 事务里混了 DDL
create、alter、drop、truncate 会隐式提交当前事务。把 DDL 和业务 DML 放在同一个事务里，前面做的修改会提前落库，回滚不回去。
4. 长事务拖垮数据库
事务里调远程接口、发消息、等用户输入，很容易把事务拉长到几十秒。长事务会持有锁、撑大 undo log、拖慢 purge。事务里只放数据库操作，外部调用放到事务提交之后。
5. 并发扣减丢更新
两个请求同时读到余额 100，各自减 10 再写回 90，实际只扣了一次。正确写法是把判断放进 SQL：update ... set balance = balance - 10 where id = ? and balance >= 10，看影响行数判断是否成功；或者加 version 字段做乐观锁。
排查步骤
- 数据没改成功：确认
autocommit、有没有执行commit、update的影响行数是不是 0； - 锁等待超时：
show processlist找到阻塞的会话，再看information_schema.innodb_trx里的事务状态； - 死锁：
show engine innodb status查看LATEST DETECTED DEADLOCK段，按主键顺序访问能减少大部分死锁； - 影响行数异常：先把
where条件改成select count(*)验证范围； - 事务没回滚：确认异常有没有被吞掉，Spring 里默认只对
RuntimeException回滚。
评论