SQL 数据修改的回滚与恢复技巧

绝大多数情况下会自动回滚；mysql autocommit=1时单语句即提交，显式事务未提交断连则innodb回滚；postgresql断连即立刻回滚；drop table在mysql中隐式提交，不可回滚。

事务没提交就断开连接，数据到底回滚了吗

绝大多数情况下，会自动回滚——但前提是数据库驱动或客户端正确实现了连接中断时的清理逻辑。MySQL 默认启用 autocommit=1，此时每条语句自带事务边界，INSERT 或 UPDATE 执行完就落地，断连不影响；而显式开启 BEGIN 或 START TRANSACTION 后未 COMMIT 就断开，InnoDB 会检测到连接异常并回滚未提交的更改。

容易踩的坑：

• Python 的 pymysql 在连接意外关闭时不会主动发 ROLLBACK，依赖服务端超时机制（如 wait_timeout），可能让事务挂起数分钟才释放锁
• Java 的 HikariCP 连接池默认不校验连接有效性，断连后复用旧连接可能报 Connection reset，但之前未提交的事务早已丢失，无法恢复
• PostgreSQL 更严格：客户端断开即触发后端进程终止，未提交事务立刻回滚，无需等待

执行了 DROP TABLE，还能靠事务回滚吗

不能。DROP TABLE 是 DDL 操作，在 MySQL（InnoDB）中会隐式触发 COMMIT，执行后事务立即结束，后续 ROLLBACK 对它无效。PostgreSQL 稍有不同：在事务块内执行 DROP TABLE 是允许的，且可被 ROLLBACK 撤销——但仅限于表尚未被其他会话访问、且未触发 pg_class 元数据写入完成前。

实际建议：

• 生产环境执行 DROP 前，先用 CREATE TABLE ... AS select 备份关键数据
• MySQL 中想“安全删除”，改用 RENAME TABLE t TO t_bak_20240520，留出回退窗口
• 避免在事务中混用 DML 和 DDL，尤其跨引擎（如 InnoDB + MyISAM）时，DDL 可能导致部分回滚失败

误 UPDATE 全表没加 WHERE，怎么紧急止损

没有通用“撤销键”。UPDATE 一旦 COMMIT，原始值就从数据页上被覆盖（除非启用了行级闪回或日志归档）。真正可行的路径只有三条：从备份恢复、从 binlog 解析反向 SQL、或靠应用层冗余字段（如 updated_at 配合历史快照表）。

操作优先级：

• 立刻停止应用写入，防止 binlog 被轮转覆盖（检查 SHOW BINARY LOGS 和 expire_logs_days）
• 用 mysqlbinlog --base64-output=DECODE-ROWS -v 解析对应时间段的 binlog，过滤出该 UPDATE 的 Rows_query 事件，提取旧值生成 UPDATE ... SET col = old_val
• 若开启了 binlog_format=ROW 且未开启 binlog_row_image=MINIMAL，binlog 里会记录完整前镜像（before image），可直接还原

想用 SAVEPOINT 实现局部回滚，但嵌套太深容易失效

SAVEPOINT 不是函数调用栈，它是事务内的标记点，同名 SAVEPOINT a 会覆盖前一个，且所有子保存点在父 ROLLBACK TO 后自动失效。常见误判是以为 “回滚到中间点后还能继续用更早的点”，其实不行。

典型陷阱：

• MySQL 8.0+ 支持 RELEASE SAVEPOINT，但释放后不可再 ROLLBACK TO，错误提示是 Unknown savepoint
• 在存储过程中多次 DECLARE EXIT HANDLER，每个 handler 里的 ROLLBACK TO sp 可能作用于已被覆盖的 savepoint 名
• PHP pdo 使用 exec("SAVEPOINT sp1") 后，若下一条语句报错且未捕获，PDO 默认不自动 rollback，需手动调用 rollback() 或 exec("ROLLBACK TO sp1")

真正稳定的局部控制，不如拆成多个短事务，用应用层状态机管理步骤成败，比依赖 savepoint 更可控。