建表、约束、写入与事务

本章目录

本章学习目标

用类型与约束保护数据含义
在独立教学表执行有条件写入
区分事务回滚与 DDL 隐式提交

表结构就是最小数据合同

CREATE TABLE 定义列、类型与约束。主键保护唯一身份，NOT NULL 保护必填，CHECK 表达合法范围，外键表达引用关系。DECIMAL(10,2) 适合课程金额，允许固定小数位；FLOAT 的二进制表示不适合要求分毫精确的结算。类型不能取代业务校验，例如合法正数金额依然可能属于错误订单。

本章创建 TEMPORARY TABLE scratch_wallet，只存在当前数据库连接中，连接结束自动消失。它不覆盖共享教学表，所以可以反复演练。两个账户余额分别一百与五十，约束余额不能负。临时表仍使用 InnoDB，才能演示事务写入回滚；临时不代表其中任何操作都天然可回滚。

写入之前先读同一条件

INSERT 明确列名，避免依赖列顺序。UPDATE 与 DELETE 的 WHERE 决定修改范围，省略条件可能处理整张表。操作前先 SELECT 同样的条件，确认键和行数，再写入。参数输入来自应用时用绑定参数，不能把用户文本直接拼接为 SQL；字符串引号中的转义并不能代替参数化接口。

示例从账户一转二十到二，先扣减再增加。两条单独自动提交会产生中间状态，第二步失败时第一步已经生效。事务将多条相关写入组成整体，成功 COMMIT，失败 ROLLBACK。真实转账还需检查账户存在、余额足够、受影响行数与并发控制；小例子只是事务原子性的演示。

提交与回滚的逐步观察

第一次 START TRANSACTION 后，一扣二十、二加二十，查询看到八十与七十，总额仍一百五十。随后 ROLLBACK，重新查询恢复一百与五十。第二次同样写入后 COMMIT，余额八十与七十保留。事务内的 SELECT 能看到本连接尚未提交的改动，不代表其他连接一定也能看到。

回滚不是万能撤销。MySQL 的许多 DDL，例如普通 CREATE、ALTER、DROP，会隐式提交，不能把先创建表后回滚当作删除表。CREATE TEMPORARY TABLE 不触发同样的隐式提交，但创建表本身也不可回滚。本章在事务外创建与清理临时表，把事务范围限定为余额 DML，减少概念混淆。

约束、隔离与失败处理

主键冲突、负余额或类型不合法应视为失败，不要关闭严格模式让错误悄悄变成警告。应用接到异常后显式回滚、记录诊断并关闭连接；是否重试取决于是否能保证幂等。插入订单时仅用重复重试可能生成两笔，因此需要稳定业务键。自动提交、事务边界与连接池行为必须一起理解。

多连接并发可能让两个操作者同时读到余额一百并都想扣八十。可用行锁 SELECT ... FOR UPDATE 或带余额条件的原子 UPDATE，并检查受影响行数；锁顺序不一致还可能死锁。数据分析岗位不一定开发支付系统，但应理解为什么读到的中间数据与最终提交状态可能不同，以及为何不能随意执行写入清洗。

从结果到验证

阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。

先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。

完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。

完整示例与实际输出

DROP TEMPORARY TABLE IF EXISTS scratch_wallet;
CREATE TEMPORARY TABLE scratch_wallet (
  account_id INT PRIMARY KEY,balance DECIMAL(10,2) NOT NULL CHECK(balance>=0)
) ENGINE=InnoDB;
INSERT INTO scratch_wallet(account_id,balance) VALUES(1,100),(2,50);
START TRANSACTION;
UPDATE scratch_wallet SET balance=balance-20 WHERE account_id=1 AND balance>=20;
UPDATE scratch_wallet SET balance=balance+20 WHERE account_id=2;
SELECT * FROM scratch_wallet ORDER BY account_id;
ROLLBACK;
SELECT * FROM scratch_wallet ORDER BY account_id;
START TRANSACTION;
UPDATE scratch_wallet SET balance=balance-20 WHERE account_id=1 AND balance>=20;
UPDATE scratch_wallet SET balance=balance+20 WHERE account_id=2;
COMMIT;
SELECT * FROM scratch_wallet ORDER BY account_id;
DROP TEMPORARY TABLE scratch_wallet;

实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.

account_id	balance
1	80.00
2	70.00

account_id	balance
1	100.00
2	50.00

account_id	balance
1	80.00
2	70.00

没有，还需约束、条件更新、锁与受影响行数检查。

两项动手任务

任务 1 · 创建独立临时库存表，减库存三件后回滚并确认恢复。

提示

从十件开始，约束非负。

完整参考解答与检查方式

CREATE TEMPORARY TABLE scratch_stock(id INT PRIMARY KEY,qty INT NOT NULL CHECK(qty>=0)) ENGINE=InnoDB; INSERT INTO scratch_stock VALUES(1,10); START TRANSACTION; UPDATE scratch_stock SET qty=qty-3 WHERE id=1 AND qty>=3; SELECT qty FROM scratch_stock; ROLLBACK; SELECT qty FROM scratch_stock; DROP TEMPORARY TABLE scratch_stock; 事务内为七，回滚后十。

任务 2 · 演示只删除一个指定键，并保持其他记录。

提示

仅使用新临时表。

完整参考解答与检查方式

CREATE TEMPORARY TABLE scratch_delete(id INT PRIMARY KEY); INSERT INTO scratch_delete VALUES(1),(2); SELECT * FROM scratch_delete WHERE id=1; START TRANSACTION; DELETE FROM scratch_delete WHERE id=1; COMMIT; SELECT * FROM scratch_delete ORDER BY id; DROP TEMPORARY TABLE scratch_delete; 最终仅键二，原业务表不变。

官方资料与掌握状态

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

记录两项任务的方法、检查结果和卡点

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。