第十章：综合实战与运维：简易电商系统构建与备份

本章将作为本教程的收官之作，我们将前几章学习的知识点（DDL、DML、DQL、索引、事务）串联起来，构建一个功能完备的简易电商数据库系统。同时，我们还将学习数据库运维中最重要的环节——备份与恢复，确保数据资产的安全。

10.1 综合实战：从零构建电商数据库

10.1.1 需求分析与系统设计

我们要构建一个支持基本购物流程的电商系统，包含以下核心模块：

1. 用户模块 (Users)：管理用户信息、账户余额。
2. 商品模块 (Products)：管理商品信息、库存数量。
3. 购物车模块 (Carts)：用户临时存放想要购买的商品。
4. 订单模块 (Orders)：记录交易结果，包含订单主表和订单详情表。

E-R 关系图简述：

• 用户 (1) -> (N) 购物车
• 用户 (1) -> (N) 订单
• 订单 (1) -> (N) 订单详情
• 商品 (1) -> (N) 购物车
• 商品 (1) -> (N) 订单详情

10.1.2 全量 Schema 初始化 (DDL)

为了演示方便，我们将重新初始化shop_biz数据库。你可以直接复制以下 SQL 脚本在你的 MySQL 环境中执行。

注意：此脚本会清空shop_biz数据库中的旧数据，请确保已做好备份或确认无误。

-- 1. 环境初始化DROPDATABASEIFEXISTSshop_biz;CREATEDATABASEshop_bizCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;USEshop_biz;-- 2. 创建用户表CREATETABLEusers(user_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',usernameVARCHAR(50)NOTNULLUNIQUECOMMENT'用户名',passwordVARCHAR(100)NOTNULLCOMMENT'密码哈希',balanceDECIMAL(10,2)DEFAULT0.00COMMENT'账户余额',created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'注册时间')COMMENT='用户表';-- 3. 创建商品表CREATETABLEproducts(product_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'商品ID',nameVARCHAR(100)NOTNULLCOMMENT'商品名称',priceDECIMAL(10,2)NOTNULLCOMMENT'单价',stockINTNOTNULLDEFAULT0COMMENT'库存数量',descriptionTEXTCOMMENT'商品描述',updated_atDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'最后更新时间',INDEXidx_name(name)-- 商品名搜索频繁，加索引)COMMENT='商品表';-- 4. 创建购物车表CREATETABLEcarts(cart_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'购物车ID',user_idINTNOTNULLCOMMENT'用户ID',product_idINTNOTNULLCOMMENT'商品ID',quantityINTNOTNULLDEFAULT1COMMENT'购买数量',added_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'添加时间',UNIQUEKEYuk_user_product(user_id,product_id),-- 每个用户对同一商品只有一条记录CONSTRAINTfk_cart_userFOREIGNKEY(user_id)REFERENCESusers(user_id),CONSTRAINTfk_cart_productFOREIGNKEY(product_id)REFERENCESproducts(product_id))COMMENT='购物车表';-- 5. 创建订单主表CREATETABLEorders(order_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'订单ID',user_idINTNOTNULLCOMMENT'用户ID',order_noVARCHAR(32)NOTNULLUNIQUECOMMENT'订单编号(业务唯一)',total_amountDECIMAL(10,2)NOTNULLCOMMENT'订单总金额',statusTINYINTNOTNULLDEFAULT0COMMENT'状态: 0-待付款, 1-已付款, 2-已发货, 3-已完成, 9-已取消',created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'下单时间',INDEXidx_user_status(user_id,status)-- 常用查询：某用户的特定状态订单)COMMENT='订单主表';-- 6. 创建订单详情表CREATETABLEorder_items(item_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'详情ID',order_idINTNOTNULLCOMMENT'订单ID',product_idINTNOTNULLCOMMENT'商品ID',priceDECIMAL(10,2)NOTNULLCOMMENT'购买时的单价(快照)',quantityINTNOTNULLCOMMENT'购买数量',CONSTRAINTfk_order_mainFOREIGNKEY(order_id)REFERENCESorders(order_id),CONSTRAINTfk_order_productFOREIGNKEY(product_id)REFERENCESproducts(product_id))COMMENT='订单详情表';-- 7. 初始化测试数据-- 注册用户INSERTINTOusers(username,password,balance)VALUES('alice','pass123',10000.00),('bob','pass456',500.00);-- 上架商品INSERTINTOproducts(name,price,stock)VALUES('iPhone 15 Pro',7999.00,50),('AirPods Pro',1899.00,100),('Type-C Cable',99.00,200);-- Alice 加购INSERTINTOcarts(user_id,product_id,quantity)VALUES(1,1,1),-- 1台 iPhone(1,2,2);-- 2个 AirPods

10.1.3 核心业务流程演练 (Transaction 实战)

模拟最复杂的业务场景：购物车下单结算。这个过程必须是一个完整的事务。

业务逻辑：

1. 开启事务。
2. 检查购物车商品库存是否充足。
3. 计算订单总金额。
4. 检查用户余额是否充足。
5. 扣减库存、扣减余额。
6. 生成订单主表记录。
7. 生成订单详情表记录。
8. 清空购物车对应商品。
9. 提交事务。

-- 模拟 Alice 点击“结算”按钮STARTTRANSACTION;-- 1. (应用层逻辑) 假设我们要结算 Alice 购物车里的所有商品-- SQL 层面我们先计算总金额SELECTSUM(p.price*c.quantity)INTO@total_costFROMcarts cJOINproducts pONc.product_id=p.product_idWHEREc.user_id=1;-- 2. 检查余额 (这里用 SQL 变量模拟应用层判断，实际开发中可能在代码里做)SELECTbalanceINTO@user_balanceFROMusersWHEREuser_id=1FORUPDATE;-- 如果余额不足，应该在这里回滚 (SQL 脚本中无法直接写 if...rollback，这里假设余额充足继续执行)-- 3. 扣减余额UPDATEusersSETbalance=balance-@total_costWHEREuser_id=1;-- 4. 扣减库存 (针对购物车里的每个商品)-- 注意：这里需要逐个处理或使用多表 UPDATE，为简化演示，我们假设只更新 iPhone 的库存UPDATEproductsSETstock=stock-1WHEREproduct_id=1;UPDATEproductsSETstock=stock-2WHEREproduct_id=2;-- 5. 生成订单INSERTINTOorders(user_id,order_no,total_amount,status)VALUES(1,'ORD202310010001',@total_cost,1);-- 1代表已付款SET@new_order_id=LAST_INSERT_ID();-- 6. 迁移购物车数据到订单详情INSERTINTOorder_items(order_id,product_id,price,quantity)SELECT@new_order_id,c.product_id,p.price,c.quantityFROMcarts cJOINproducts pONc.product_id=p.product_idWHEREc.user_id=1;-- 7. 清空购物车DELETEFROMcartsWHEREuser_id=1;-- 8. 提交事务COMMIT;-- 验证结果SELECT*FROMusersWHEREuser_id=1;-- 余额应减少SELECT*FROMproducts;-- 库存应减少SELECT*FROMordersWHEREorder_id=@new_order_id;SELECT*FROMorder_itemsWHEREorder_id=@new_order_id;SELECT*FROMcartsWHEREuser_id=1;-- 应该为空

10.2 数据库运维：备份与恢复

数据库备份是运维工作的生命线。即使程序代码丢失，只要数据还在，企业就能存活；反之，数据丢失往往意味着灾难。

10.2.1 逻辑备份工具：mysqldump

mysqldump是 MySQL 自带的逻辑备份工具，它会将数据库结构和数据导出为 SQL 文本文件。

常用命令格式（在命令行/终端执行，不是在 MySQL 客户端内）：

1. 备份单个数据库：

   mysqldump -u root -p shop_biz>d:\backup\shop_biz_backup.sql

   • -u root: 用户名
   • -p: 提示输入密码
   • shop_biz: 要备份的数据库名
   • >: 重定向输出到文件

2. 备份多个数据库：

   mysqldump -u root -p --databases db1 db2>multi_db_backup.sql

3. 备份所有数据库 (全库备份)：

   mysqldump -u root -p --all-databases>all_db_backup.sql

4. 仅备份表结构 (不含数据)：

   mysqldump -u root -p --no-data shop_biz>shop_biz_structure.sql

10.2.2 数据灾难恢复实战

假设某天shop_biz数据库被误删了：

-- 模拟删库跑路DROPDATABASEshop_biz;

此时，不要慌张，我们利用刚才备份的shop_biz_backup.sql进行恢复。

恢复方法 1：在命令行使用 mysql 命令

# 不需要先登录 mysql，直接在终端执行# 注意：如果备份文件中包含了 CREATE DATABASE 语句（加了 --databases 参数会有），则不需要手动创建库# 如果只是备份单库，通常需要先手动创建空库mysql -u root -p<d:\backup\shop_biz_backup.sql

恢复方法 2：在 MySQL 客户端内使用 source 命令

-- 登录 MySQL 后CREATEDATABASEshop_biz;-- 如果备份文件里没写这句USEshop_biz;source d:/backup/shop_biz_backup.sql;

执行完毕后，再次查询数据，你会发现所有表和数据都完好如初。

10.2.3 生产环境备份建议

1. 自动化：不要依赖人工手动备份。Linux 下使用crontab，Windows 下使用“任务计划程序”每天定时执行mysqldump脚本。
2. 异地存储：备份文件不要只放在数据库服务器上。必须上传到云存储 (OSS/S3) 或另一台物理服务器，防止服务器硬盘损坏导致数据和备份同时丢失。
3. 定期演练：备份不是目的，恢复才是。每隔一段时间（如每季度）尝试在一个测试环境中恢复备份，确保备份文件是有效的。

本章总结

通过本章的学习，你已经完成了一个从设计到实现，再到运维保障的完整闭环：

1. 设计能力：掌握了电商核心业务表的 E-R 设计与规范化建表。
2. 编码能力：熟练运用复杂的 Transaction 处理多表联动的数据一致性问题。
3. 运维能力：学会了使用mysqldump保护你的数据资产。

至此，MySQL 基础教程的核心内容已全部结束。希望这些知识能成为你开发之路上坚实的基石！