电子报纸订购系统数据库设计：从建表到事务与并发控制

简介数据库课程设计中的电子报纸订购系统是一份基于Java实现的完整课程设计源码包面向需要完成数据库实践作业的在校学生及初级开发者旨在帮助读者掌握Java后端逻辑、关系型数据库设计与SQL增删改查操作。包内共24个文件其中19个Java源文件按功能拆分出顾客管理、报纸信息、订购记录、登录菜单等模块4个SQL脚本分别对应custom、paper、order等核心数据表的建表与样例数据另有1个README说明文档整个压缩包仅33KB便于快速下载与部署。当前已有180人浏览学习适合参考项目分层、数据库范式设计及GUI事件监听等知识点。通过研读源码和脚本读者可以复用其中的登录验证、数据CRUD、结果查询等功能代码也能从中体会MVC设计模式在小型系统中的应用为课程报告或后续扩展打下基础。1. 电子报纸订购系统到底考什么先定边界再写表数据库课程设计电子报纸订购系统这个题目看起来比图书管理系统新潮骨子里考的还是同一件事能不能把一个真实业务拆成干净的表结构并且保证每个多步写操作不丢数据、不出错。电子报纸的订阅、支付、到期续费背后全是一连串的增删改查和状态流转稍不留神就在并发场景里翻车。这个题目最适合两类人正在找课程设计方案的在校学生和想快速补一遍 MySQL 事务、锁、范式实战的开发者。读完这篇你可以在自己的电脑上把注册、订阅、支付、订阅台账这条主链路完整跑通并且回答答辩老师最常问的那句“为什么这么建表”。2. 需求到 ER 建模把电子报纸订购拆成六张表拿到题目直接打开编辑器写代码是课程设计里最稳的翻车方式。我一般会先用半小时把业务边界画清楚谁在用系统、每个动作会动到哪些数据、哪些数据必须留历史。这个过程做完建表几乎是顺水推舟。2.1 三类角色与主流程注册、订阅、支付、查订单系统里主要有三类角色普通用户、管理员、系统本身。普通用户负责注册登录、浏览报纸目录、提交订阅订单、模拟支付、查看自己的订单和订阅状态管理员负责维护报纸信息包括上下架、调价、处理订单和查看销售报表系统角色指的是定时任务或触发器比如每天扫描 subscription 表把到期记录的 status 从 1 改为 0。主流程可以拆成五步用户注册时写入 user 表管理员发布报纸时写入 newspaper 表用户提交订阅订单时一次动作同时写 order、order_item、payment 三张表此时订单处于待支付状态用户模拟支付时payment 状态置为已支付order 状态同步置为已支付同时生成 subscription 记录并扣减报纸的虚拟库存之后每天由系统任务检查订阅是否到期。这里有个容易想偏的点要不要接入真实支付网关我的建议是不要。课程设计的核心是数据库设计不是第三方接口联调。用一张 payment 表记录“支付流水号、金额、状态、支付时间”模拟整个支付回调过程已经足够展示你对事务和数据一致性的理解。把支付做深反而会让表结构和业务逻辑失控。2.2 实体与联系订单明细为什么不能并进订单表实体一共有六个用户、报纸、订单、订单明细、支付记录、订阅台账。用户和报纸的关系是典型的多对多一个用户可以订阅多份报纸一份报纸也可以被多个用户订阅。这种多对多关系在关系型数据库里必须拆成中间表而这里实际上用订单明细和订阅台账两张表从不同视角表达了这层关系。实体清单可以先列出来用户表存账号、密码、昵称、状态报纸表存名称、分类、出版社、单价、虚拟库存、软删除标记订单表存用户、总额、状态订单明细表存订单、报纸、下单时的快照名称、数量、快照单价支付记录表存订单一对一关联、金额、方式、状态、支付时间订阅台账存用户、报纸、来源明细、起止日期、状态。订单明细为什么必须单独建表这是答辩时最值得展开的问题。如果在订单表里塞 newspaper1_id、newspaper1_name、newspaper2_id、newspaper2_name 这种列一次下单三份报纸就会出现大量空列查询某份报纸卖了多少要拆列甚至 UNION统计热销报纸基本做不了。拆成 order_item 后一个订单对应多行明细GROUP BY newspaper_id 就能拿到销量这才是第一范式的要求。还有一个细节容易被忽略order_item 里存了 item_name 和 price 两个“冗余字段”。这不是违反范式而是业务要求。订单生成后报纸可能改名、调价历史订单必须保留当时的成交价和名称。如果没有这两个快照字段查一年前的订单时单价可能早就变了对账都无从谈起。订阅台账独立成表的理由同样充分。order_item 表达的是“那次交易买了什么”subscription 表达的是“当前哪些订阅还在生效”。两者生命周期不同订单可能已完成或取消订阅则持续数月。续费时重新下单然后把 subscription 的 end_date 往后延长或者新增一条记录把旧的置为失效。如果只靠订单明细查“我哪些报纸还在有效期内”每张订单都要 JOIN 支付状态查询逻辑会非常别扭。2.3 范式检查第二、第三范式在哪里容易破三个范式在哪个环节最容易破课设答辩几乎必问。第一范式要求字段原子性表里不能出现“报纸1,报纸2,报纸3”这种逗号分隔字符串这条在拆出 order_item 时已经解决了。第二范式要求非主键列完全依赖主键如果 order_item 采用联合主键order_id, newspaper_id那么 item_name 其实只依赖 newspaper_id属于部分依赖会把报纸名称复制到几百行订单明细里改个名字要全表更新。我在这里选择了独立主键 id从根上绕开了这个问题。第三范式最容易翻车的地方在统计字段。有人会在 user 表里加一个 total_spent 累计消费金额理由是查询个人中心快。这个字段依赖订单汇总不直接依赖用户主键属于传递依赖。一旦某笔订单退款或状态修正total_spent 就和真实订单对不上。正确做法是查询时现场 SUM或者用视图提前算好表里不落这个字段。另外要留意订阅台账与订单明细的映射。subscription 引用了 order_item_id保证每一条订阅都能回溯到“哪一单、哪一份报纸、按什么价格成交”。很多人会在 subscription 表里直接放报纸名和价格这同样属于传递依赖而且报纸调价后历史订阅会显示错误信息。宁可多写一个关联字段也不要在多对多关系里复制主体表的业务字段。3. 建表落地MySQL 的建表语句与字段参数取舍表结构设计完了接下来就是把它翻译成 MySQL DDL。我一般把所有建表语句放在一个 schema.sql 里数据库统一 utf8mb4 字符集引擎统一 InnoDB。字符集用 utf8mb4 而不是 utf8是因为 utf8 在 MySQL 里最多三字节存中文没问题但遇到特殊符号或者将来要存 Emoji 就会报错。引擎用 InnoDB 是硬性要求MyISAM 不支持外键也不支持事务没法承担这个系统的核心链路。3.1 用户表与报纸表字符集、索引、类型怎么选先建数据库和两张基础表。CREATE DATABASE IF NOT EXISTS newspaper_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE newspaper_db; CREATE TABLE user ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(128) NOT NULL, password CHAR(64) NOT NULL, nickname VARCHAR(64) NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1 正常0 禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_email (email) ) ENGINEInnoDB; CREATE TABLE newspaper ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(128) NOT NULL, category VARCHAR(32) NOT NULL, publisher VARCHAR(128) NOT NULL DEFAULT , price DECIMAL(10,2) NOT NULL, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 虚拟库存, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0 未删除1 已删除, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_category (category) ) ENGINEInnoDB;id 用 INT UNSIGNED 而不是 INT是因为课程设计的数据量 INT 完全够用UNSIGNED 能把上限翻倍到 42 亿纯粹是养成好习惯。email 的 VARCHAR(128) 不是随便定的邮箱地址最长 254 字符128 是常见折中值不要所有字符串都 VARCHAR(255)索引性能和存储效率都会变差。password 用 CHAR(64) 是因为这里存的是 SHA2 哈希值固定 64 字符用定长 CHAR 避免额外存储开销。status 用 TINYINT 而不是 ENUM因为 ENUM 后期想增加状态值需要改表结构TINYINT 可以直接往里写数字。newspaper 表的 price 是 DECIMAL(10,2)整数部分 8 位小数部分 2 位处理人民币足够。金钱字段绝不能用 FLOAT 或 DOUBLE浮点数在二进制下无法精确表示累计对账时会出现 0.1 这种尾差。stock 字段是给并发扣库存演示留的口子后面第四章会用到。is_deleted 是软删除标记一份报纸一旦产生过订单物理删除会触发外键约束软删除是常规解法。category 建了普通索引因为列表页按分类筛选是高频查询name 没有建索引因为前端大概率用 LIKE 模糊搜索普通索引对前置百分号的模糊查询基本无效建了也是白建。3.2 订单、订单明细、支付与订阅表外键与唯一索引四张和交易相关的表是这套系统的核心建表时每一处约束都要能说出理由。CREATE TABLE order ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0 待支付1 已支付2 已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ) ENGINEInnoDB; CREATE TABLE order_item ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, newspaper_id INT UNSIGNED NOT NULL, item_name VARCHAR(128) NOT NULL COMMENT 下单时的报纸名快照, quantity INT UNSIGNED NOT NULL DEFAULT 1, price DECIMAL(10,2) NOT NULL COMMENT 下单时的单价快照, CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES order (id), CONSTRAINT fk_item_newspaper FOREIGN KEY (newspaper_id) REFERENCES newspaper (id), UNIQUE KEY uk_item (order_id, newspaper_id) ) ENGINEInnoDB; CREATE TABLE payment ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, pay_amount DECIMAL(10,2) NOT NULL, pay_method VARCHAR(16) NOT NULL DEFAULT mock, status TINYINT NOT NULL DEFAULT 0 COMMENT 0 待支付1 成功2 失败, paid_at DATETIME NULL, CONSTRAINT fk_pay_order FOREIGN KEY (order_id) REFERENCES order (id), UNIQUE KEY uk_pay_order (order_id) ) ENGINEInnoDB; CREATE TABLE subscription ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, newspaper_id INT UNSIGNED NOT NULL, order_item_id BIGINT UNSIGNED NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1 生效中0 已到期, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES user (id), CONSTRAINT fk_sub_newspaper FOREIGN KEY (newspaper_id) REFERENCES newspaper (id), CONSTRAINT fk_sub_item FOREIGN KEY (order_item_id) REFERENCES order_item (id) ) ENGINEInnoDB;order 是 MySQL 保留字必须用反引号包起来这是新手最容易踩的语法坑。订单 id 用了 BIGINT订单量的增长预期比用户高同时给后面造数据留足空间。order_item 里的 uk_item 唯一索引很关键它保证同一张订单不会重复添加同一份报纸想多订就在 quantity 上加数量而不是插入多行重复记录。item_name 和 price 的快照字段对应第二章讲的业务要求COMMENT 里写清楚是快照方便后面的人理解。payment 表的 uk_pay_order 唯一索引保证一笔订单最多只有一条支付流水这是“一次支付”约束的数据库实现。subscription 表同时持有 user_id 和 newspaper_id再加上 order_item_id三条外键让数据血缘非常清晰。start_date 和 end_date 用 DATE 而不是 DATETIME因为订阅周期只精确到天不需要时分秒。外键字段类型必须和主表完全一致user_id 是 INT UNSIGNED那 order.user_id 也必须是 INT UNSIGNED少一个 UNSIGNED 建表直接报错这是第五章要展开的坑。3.3 字段参数与版本边界CHECK 约束和软删除的取舍MySQL 8.0.16 之后的版本CHECK 约束真正生效。可以给 newspaper 表补一条价格非负的约束建表语句里加 CONSTRAINT chk_price CHECK (price 0)如果数据库低于 8.0.16这条约束会被解析但直接忽略不会报错也不会拦截非法数据。课程设计完全可以写上答辩时主动提一句“生产环境依赖版本老版本建议靠应用层校验”反而加分。软删除和物理删除的边界也要想清楚。newspaper 表的 is_deleted 是软删除查列表必须带 WHERE is_deleted 0。真正要物理删的只有两种场景一种是数据完全没被引用可以直接 DELETE另一种是做数据订正需要先删子表再删父表还要小心外键。除此之外一律软删除因为历史订单需要回溯报纸当时的名称和价格。建完表后用 SHOW CREATE TABLE newspaper; 查看 MySQL 实际执行的建表语句这是排查“我明明写了约束却不生效”的常用命令。也可以对比 information_schema 里的字段信息确认字符集和排序规则有没有正确下沉到每一张表。4. 打通订阅主链路从 SQL 增删改查到事务与并发控制表结构只是骨架真正决定系统能不能用的是主链路注册、搜索、下单、支付、订阅生效。这一章以 MySQL 8.0 和 Python 3 的 PyMySQL 为例把这条链路上的每一条 SQL 和事务边界讲清楚。4.1 用户注册与报纸查询三条最常用的 SQL注册是增删改查里最典型的 INSERT难点不在写入本身而在重复邮箱怎么处理。先看 SQLINSERT INTO user (email, password, nickname) VALUES (reader1example.com, SHA2(123456, 256), 读者一号); SELECT id, name, category, price, stock FROM newspaper WHERE is_deleted 0 AND (name LIKE %科技% OR category LIKE %科技%) ORDER BY created_at DESC LIMIT 20 OFFSET 0;密码在数据库里直接用 SHA2(123456, 256) 存储这是课程设计里最省事的做法。注意 MySQL 的 SHA2 函数返回固定 64 位十六进制字符串刚好匹配 user 表的 CHAR(64)。生产环境要用加盐哈希这个要在答辩时主动说明表明你分得清“演示方案”和“生产方案”。查询语句里 LIKE %科技% 前后都有百分号这个写法用不上索引数据量小没问题到了一万条以上明显变慢。答辨时提到这个点可以顺势说“真实系统应该改造成前缀匹配或者引入全文索引”。LIMIT 20 OFFSET 0 是分页参数LIMIT 控制每页条数OFFSET 控制跳过多少条顺序不能反。重复邮箱的处理要靠数据库约束不能只靠应用层 if 判断。PyMySQL 里插入重复数据会抛 IntegrityErrorimport pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasenewspaper_db, charsetutf8mb4, autocommitFalse ) try: cur conn.cursor() cur.execute( INSERT INTO user (email, password, nickname) VALUES (%s, %s, %s), (reader1example.com, hashed_password, 读者一号) ) conn.commit() except pymysql.IntegrityError as e: if e.args[0] 1062: print(邮箱已存在这是业务冲突不是系统异常) else: raise finally: cur.close() conn.close()错误码 1062 对应唯一键冲突这是数据库增删改查里最常见的边界情况。用唯一索引做校验比先 SELECT 再 INSERT 的写法更可靠因为两个并发请求同时注册同一个邮箱时“先查后插”的方式两个线程都会查到不存在然后双双插入成功。唯一索引会让第二个插入直接撞墙这是数据库层面保证数据正确性的典型例子。4.2 下单事务把五个写操作包进一个事务下单动作涉及三张表order 主表、order_item 明细表、payment 支付流水。常见做法是写一个函数把整个动作包进一个事务中途任何一步失败都整体回滚。import pymysql def create_order(conn, user_id, items): items 是列表每个元素为 (newspaper_id, quantity) 函数不负责 commit 和 rollback由调用方统一控制事务边界。 cur conn.cursor() # 1. 创建订单主表记录初始状态为待支付 cur.execute( INSERT INTO order (user_id, total_amount, status) VALUES (%s, %s, %s), (user_id, 0, 0) ) order_id cur.lastrowid total 0 # 2. 逐条插入订单明细价格从数据库实时读不信任前端传入的价格 for newspaper_id, quantity in items: cur.execute( SELECT name, price FROM newspaper WHERE id %s AND is_deleted 0 FOR UPDATE, (newspaper_id,) ) row cur.fetchone() if row is None: raise RuntimeError(f报纸 {newspaper_id} 不存在或已下架) name, price row cur.execute( INSERT INTO order_item (order_id, newspaper_id, item_name, quantity, price) VALUES (%s, %s, %s, %s, %s), (order_id, newspaper_id, name, quantity, price) ) total price * quantity # 3. 回填订单总额 cur.execute( UPDATE order SET total_amount %s WHERE id %s, (total, order_id) ) # 4. 生成一条待支付流水 cur.execute( INSERT INTO payment (order_id, pay_amount, pay_method, status) VALUES (%s, %s, mock, 0), (order_id, total) ) return order_id这段代码里有几个参数值得展开。SELECT 语句里的 FOR UPDATE 是悲观行锁它在事务内锁住 newspaper 这一行防止在计算总额期间另一笔订单同时改价或下架。如果不用锁两个事务同时读到同一个价格然后各自计算总额最后订单总额可能与实际支付金额不一致。lastrowid 是 PyMySQL 提供的自增主键回读方法必须在同一事务里使用事务外拿不到正确值。调用方的写法更有讲究conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasenewspaper_db, charsetutf8mb4, autocommitFalse ) try: order_id create_order(conn, 1, [(1, 1), (2, 2)]) conn.commit() except Exception: conn.rollback() raise finally: conn.close()autocommitFalse 是事务生效的前提。如果默认自动提交create_order 里的 INSERT 就会各自独立提交中间一步失败时无法回滚数据库里会出现只有订单没有明细的脏数据。commit 和 rollback 必须成对出现在 try-except 里顺序不能反。支付成功后的动作同样要放进事务这里直接给 SQL 形态START TRANSACTION; UPDATE payment SET status 1, paid_at NOW() WHERE order_id 1001 AND status 0; UPDATE order SET status 1 WHERE id 1001 AND status 0; INSERT INTO subscription (user_id, newspaper_id, order_item_id, start_date, end_date, status) SELECT o.user_id, oi.newspaper_id, oi.id, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 1 FROM order_item oi JOIN order o ON oi.order_id o.id WHERE oi.order_id 1001; UPDATE newspaper n JOIN order_item oi ON oi.newspaper_id n.id AND oi.order_id 1001 SET n.stock n.stock - oi.quantity; COMMIT;每一条 UPDATE 都带 AND status 0这是防重入的关键条件。如果同一笔订单被并发支付两次第二次会因 status 不匹配而影响 0 行代码层检查到影响行数为 0 就可以直接抛异常回滚。INSERT INTO subscription 使用 INSERT SELECT 写法从 order_item 和 order 里直接派生订阅数据既能拿到订单里的用户和报纸 ID又能拿到快照不需要在应用层手工拼 SQL。到这里支付成功、订单已支付、订阅生效、库存扣减四件事被程序包进同一个原子操作任何一步失败前面全部回滚。4.3 并发订阅与库存扣减FOR UPDATE 和死锁怎么办并发场景是答辩老师最爱问的部分。假设一份电子报纸库存只剩 10 份20 个用户同时下单。如果没有锁两个事务同时读到 stock 10分别执行 stock - 1最后库存变 9但两笔订单都显示支付成功这就是超卖。第一种解法是条件更新把“判断库存够不够”和“扣减库存”合并成一条 SQLUPDATE newspaper SET stock stock - 1 WHERE id 1001 AND stock 1;这里 WHERE 后面的 stock 1 就是乐观判断影响行数为 0 说明库存不足。这条 UPDATE 自带行锁两个并发事务会串行执行后执行的那个会因为条件不满足而影响 0 行。优点是不用显式事务缺点是扣库存后还要做别的写操作时边界不好控制。第二种解法就是 4.2 里已经用到的 FOR UPDATE显式锁住行然后做业务判断。但 FOR UPDATE 用多了会引入死锁风险。两个事务各自锁住一份报纸然后都想再锁对方那份时就会互相等待MySQL 检测到死锁后回滚其中一个事务应用层拿到 1213 错误码。死锁的规避有三个常见做法多表操作时固定访问顺序比如永远先写 order 再写 order_item 再写 newspaper不要这次先写 newspaper 下次先写 order一个事务里避免等待用户输入把 SQL 语句顺序固定死减少锁等待窗口应用层捕获死锁错误重试事务。重试代码的结构如下for _ in range(3): try: conn.begin() order_id create_order(conn, user_id, items) conn.commit() break except pymysql.err.OperationalError as e: if e.args[0] 1213: # 死锁 conn.rollback() continue raise参数 1213 是 MySQL 死锁的错误码重试前必须 rollback否则事务残留会污染下一次尝试。重试三次仍然失败就抛给上层处理不要无限循环。顺带说一句数据库并发锁和死锁不是课程设计里才有的问题业务量一大都会遇到提前在课设里踩一遍面试时能讲出具体的解决路径比背八股文有用得多。5. 常见问题排查从外键失败到死锁的五个坑这一章的每一条都是我在类似系统里真实踩过的坑按“现象、原因、解决”三段式来写照着排查能省下大半晚上的时间。5.1 外键建不上先查字符集和排序规则现象执行建 order 表或 order_item 表时MySQL 直接报错“Cannot add foreign key constraint”明明引用的字段都存在字段名也没写错。原因绝大多数情况是两张表的字符集或排序规则不一致。比如 user 表建在 utf8mb4 下order 表建在 utf8 下外键关联的字符串字段排序规则不同MySQL 会拒绝创建外键。另一个常见问题是被引用字段少了 UNSIGNEDuser.id 是 INT UNSIGNEDorder.user_id 却写成 INT类型不完全一致同样报错。解决建库时统一字符集字段类型严格对齐。排查时可以查 information_schemaSELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME IN (user, order) AND COLUMN_NAME IN (id, user_id);这条 SQL 会把两张表关联字段的类型、字符集、排序规则并排显示哪里不一致一目了然。改掉不一致的字段后外键就能正常建立。5.2 唯一索引没拦住重复邮箱NULL 不去重现象注册接口测试时email 字段不填居然也能注册成功而且还能注册好几条邮箱为空的账号。改成重复邮箱后第二次确实被拦截了但空邮箱成了漏网之鱼。原因MySQL 的唯一索引对 NULL 值不去重。email 列如果允许 NULL两条记录的 email 都是 NULL唯一索引不会报冲突业务上就出现了“空邮箱注册多个账号”的脏数据。这是唯一索引特性不是 bug。解决email 列建表时必须加 NOT NULL同时应用层也要做非空校验。数据库约束负责最后一层兜底应用层校验负责给出友好的错误提示。课设里很多人只在应用层做了判断结果并发请求一多两个线程同时绕过判断还是插进去了。只有唯一索引 NOT NULL 组合才能从物理上阻断。5.3 并发扣库存超卖没有锁的读写现象用脚本模拟 20 个并发请求抢购库存 10 份的电子报纸全部返回成功最后库存变成负数但订单生成了 15 单。原因应用层写法是先 SELECT 查库存判断大于 0 后再 UPDATE 扣减。两个并发事务可能同时读到库存 10判断都通过先后执行扣减最终库存 8但两笔都成功。这个窗口期就是典型的丢失更新本质是读和写没有构成原子操作。解决把判断和更新合并进一条 UPDATE 的 WHERE 条件里像 4.3 写的那样WHERE id ? AND stock ?。或者用事务加 SELECT FOR UPDATE 锁行让第二个事务等第一个提交后再判断。课程设计里我更推荐条件更新代码简洁也不容易引入死锁。无论哪种方式扣减后必须检查影响行数不能只 execute 不 fetch。5.4 中文乱码与连接超时连接串参数决定成败现象建表时明明用了 utf8mb4页面展示中文还是乱码或者 Python 读出来的中文变成“???”。更诡异的是 Navicat 里看数据正常代码一读就乱。原因数据库字符集只是其中一层客户端连接串没指定 charset 就会出现两层编码不一致。MySQL 连接建立后服务端会按客户端的字符集设置来做转码Python 端默认可能是 latin1数据入库时被错误转码读出来自然乱码。解决PyMySQL 连接参数必须写 charsetutf8mb4JDBC 连接串对应写 characterEncodingutf8。命令行连库时先执行 SET NAMES utf8mb4。排查路径按“数据库字符集、连接字符集、终端显示字符集”三层依次检查哪一层断掉都乱码。这类问题特征就是“玄学”其实是三层里有一层偷懒没写。5.5 删不掉的父表记录外键约束不是 bug现象管理员想删除一份已经卖出过 300 份的报纸前端点了删除后端返回数据库异常“Cannot delete or update a parent row: a foreign key constraint fails”。原因order_item 表有外键指向 newspaper报纸有了子表记录就不能物理删除。这个设计不是错误是数据库在保护历史数据。真的执行 DELETE FROM newspaper WHERE id 5订单明细里的快照和统计都会断裂。解决不要物理删除用 is_deleted 软删除。列表查询统一带 WHERE is_deleted 0订单和订阅照常 JOIN 即可。如果答辩时被问到直接说“软删除是为了保留历史订单的可追溯性防止商品下架导致历史记录失效”这是个很成熟的业务决策。非要物理删除的场景必须先删子表再删父表并且要在事务里做避免删了一半卡住。6. 验证与进阶用视图、存储过程和并发脚本证明系统能答辩表建好、链路跑通只完成了系统距离“能答辩”还差一个验证环节。这一章给你三条验证路径视图做报表、存储过程造数据、并发对账脚本查一致性问题。6.1 视图订单日报与热销报纸统计报表查询应该固化在数据库端而不是每次都在应用层拼 GROUP BY。建两个视图把热销统计和订单日报封装起来CREATE OR REPLACE VIEW v_hot_newspaper AS SELECT n.id, n.name, SUM(oi.quantity) AS sold_count, SUM(oi.quantity * oi.price) AS revenue FROM order_item oi JOIN newspaper n ON oi.newspaper_id n.id GROUP BY n.id, n.name ORDER BY revenue DESC LIMIT 20; CREATE OR REPLACE VIEW v_order_daily AS SELECT DATE(created_at) AS day, COUNT(*) AS order_cnt, SUM(total_amount) AS amount FROM order WHERE status 1 GROUP BY DATE(created_at);视图的作用是让报查询变成一条 SELECT * FROM v_hot_newspaper; 业务代码不用关心底层 JOIN 逻辑。第一个视图按报纸维度汇总销量和收入第二个视图按天汇总订单数和金额。注意 GROUP BY 的列必须和 SELECT 的非聚合列一致n.id 和 n.name 都要出现在 GROUP BY 里这是 MySQL 的语法约束。6.2 存储过程批量造数据让数据量先到一万行没有数据量的系统跑不出性能问题也看不出索引好坏。写一个存储过程循环造用户数据DELIMITER $$ CREATE PROCEDURE seed_users(IN cnt INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i cnt DO INSERT INTO user (email, password, nickname, status) VALUES (CONCAT(user_, i, test.com), SHA2(123456, 256), CONCAT(用户, i), 1); SET i i 1; END WHILE; END$$ DELIMITER ;DELIMITER 是必须的它把结束符临时改成 $$否则 MySQL 客户端会在每个分号处把语句截断。存储过程里的 DECLARE 声明循环变量 iWHILE 循环体里做 INSERTCONCAT 函数生成唯一 email。调用 CALL seed_users(1000); 就能生成一千个测试用户。造订单数据同理嵌套一层循环引用随机用户 ID 即可。批量造数据时如果外键检查拖慢速度可以用 SET FOREIGN_KEY_CHECKS 0 临时关闭导入完必须改回 1。6.3 并发对账脚本订单、支付、订阅三条线同时核数据一致性不能靠肉眼检查要写对账 SQL。最简单的一组SELECT IF( (SELECT IFNULL(SUM(total_amount), 0) FROM order WHERE status 1) (SELECT IFNULL(SUM(pay_amount), 0) FROM payment WHERE status 1), PASS, FAIL ) AS order_payment_check; SELECT COUNT(*) AS paid_item_cnt FROM order_item oi JOIN order o ON oi.order_id o.id WHERE o.status 1; SELECT COUNT(*) AS sub_cnt FROM subscription WHERE status 1;第一条验证订单总额与支付总额是否相等第二条算已支付订单的明细条数第三条算生效中的订阅数。三个数字如果对不上就说明事务边界出了问题按第四章的事务口径去查。这个对账脚本我每次改完表结构都要跑一遍它能在五分钟内帮你把并发测试留下的脏数据揪出来比用眼睛看表格高效得多。我当年做类似课设时页面做得花里胡哨结果答辩老师只问了一句“并发下单时库存怎么保”当场没答上来。后来把这个系统重写了一遍把所有事务、锁、对账脚本补齐再被问到类似问题时直接打开对账 SQL 给他看结果。表怎么建、锁加在哪一行、状态怎么流转道理其实就这些动手跑一遍比背十遍理论都管用。希望帮到你。本文还有配套的精品资源点击获取