核心内容摘要
2019，法航“塞尔维亚之夜”：一场邂逅，改变了多少人生？
本章将作为本教程的收官之作我们将前几章学习的知识点DDL、DML、DQL、索引、事务串联起来构建一个功能完备的简易电商数据库系统。
同时我们还将学习数据库运维中最重要的环节——备份与恢复确保数据资产的安全。
1
1 综合实战从零构建电商数据库
10.
1 需求分析与系统设计我们要构建一个支持基本购物流程的电商系统包含以下核心模块用户模块 (Users)管理用户信息、账户余额。
商品模块 (Products)管理商品信息、库存数量。
购物车模块 (Carts)用户临时存放想要购买的商品。
订单模块 (Orders)记录交易结果包含订单主表和订单详情表。
E-R 关系图简述用户 (
- (N) 购物车用户 (
- (N) 订单订单 (
- (N) 订单详情商品 (
- (N) 购物车商品 (
- (N) 订单详情
10.
2 全量 Schema 初始化 (DDL)为了演示方便我们将重新初始化shop_biz数据库。
你可以直接复制以下 SQL 脚本在你的 MySQL 环境中执行。
注意此脚本会清空shop_biz数据库中的旧数据请确保已做好备份或确认无误。
--
环境初始化DROPDATABASEIFEXISTSshop_biz;CREATEDATABASEshop_bizCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;USEshop_biz;--
创建用户表CREATETABLEusers(user_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT用户ID,usernameVARCHAR(
NOTNULLUNIQUECOMMENT用户名,passwordVARCHAR(
NOTNULLCOMMENT密码哈希,balanceDECIMAL(10,
DEFAULT
00COMMENT账户余额,created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT注册时间)COMMENT用户表;--
创建商品表CREATETABLEproducts(product_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT商品ID,nameVARCHAR(
NOTNULLCOMMENT商品名称,priceDECIMAL(10,
NOTNULLCOMMENT单价,stockINTNOTNULLDEFAULT0COMMENT库存数量,descriptionTEXTCOMMENT商品描述,updated_atDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT最后更新时间,INDEXidx_name(name)-- 商品名搜索频繁加索引)COMMENT商品表;--
创建购物车表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购物车表;--
创建订单主表CREATETABLEorders(order_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT订单ID,user_idINTNOTNULLCOMMENT用户ID,order_noVARCHAR(
NOTNULLUNIQUECOMMENT订单编号(业务唯
,total_amountDECIMAL(10,
NOTNULLCOMMENT订单总金额,statusTINYINTNOTNULLDEFAULT0COMMENT状态: 0-待付款, 1-已付款, 2-已发货, 3-已完成, 9-已取消,created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间,INDEXidx_user_status(user_id,status)-- 常用查询某用户的特定状态订单)COMMENT订单主表;--
创建订单详情表CREATETABLEorder_items(item_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT详情ID,order_idINTNOTNULLCOMMENT订单ID,product_idINTNOTNULLCOMMENT商品ID,priceDECIMAL(10,
NOTNULLCOMMENT购买时的单价(快照),quantityINTNOTNULLCOMMENT购买数量,CONSTRAINTfk_order_mainFOREIGNKEY(order_id)REFERENCESorders(order_id),CONSTRAINTfk_order_productFOREIGNKEY(product_id)REFERENCESproducts(product_id))COMMENT订单详情表;--
初始化测试数据-- 注册用户INSERTINTOusers(username,password,balance)VALUES(alice,pass123,
10000.
,(bob,pass456,
500.
;-- 上架商品INSERTINTOproducts(name,price,stock)VALUES(iPhone 15 Pro,
7
00,
,(AirPods Pro,
1
00,
,(Type-C Cable,
9
00,
;-- Alice 加购INSERTINTOcarts(user_id,product_id,quantity)VALUES(1,1,
,-- 1台 iPhone(1,2,
;-- 2个 AirPods
10.
3 核心业务流程演练 (Transaction 实战)模拟最复杂的业务场景购物车下单结算。
这个过程必须是一个完整的事务。
业务逻辑开启事务。
检查购物车商品库存是否充足。
计算订单总金额。
检查用户余额是否充足。
扣减库存、扣减余额。
生成订单主表记录。
生成订单详情表记录。
清空购物车对应商品。
提交事务。
-- 模拟 Alice 点击“结算”按钮STARTTRANSACTION;--
(应用层逻辑) 假设我们要结算 Alice 购物车里的所有商品-- SQL 层面我们先计算总金额SELECTSUM(p.price*c.quantity)INTOtotal_costFROMcarts cJOINproducts pONc.product_idp.product_idWHEREc.user_id1;--
检查余额 (这里用 SQL 变量模拟应用层判断实际开发中可能在代码里做)SELECTbalanceINTOuser_balanceFROMusersWHEREuser_id1FORUPDATE;-- 如果余额不足应该在这里回滚 (SQL 脚本中无法直接写 if...rollback这里假设余额充足继续执行)--
扣减余额UPDATEusersSETbalancebalance-total_costWHEREuser_id1;--
扣减库存 (针对购物车里的每个商品)-- 注意这里需要逐个处理或使用多表 UPDATE为简化演示我们假设只更新 iPhone 的库存UPDATEproductsSETstockstock-1WHEREproduct_id1;UPDATEproductsSETstockstock-2WHEREproduct_id2;--
生成订单INSERTINTOorders(user_id,order_no,total_amount,status)VALUES(1,ORD202310010001,total_cost,
;-- 1代表已付款SETnew_order_idLAST_INSERT_ID();--
迁移购物车数据到订单详情INSERTINTOorder_items(order_id,product_id,price,quantity)SELECTnew_order_id,c.product_id,p.price,c.quantityFROMcarts cJOINproducts pONc.product_idp.product_idWHEREc.user_id1;--
清空购物车DELETEFROMcartsWHEREuser_id1;--
提交事务COMMIT;-- 验证结果SELECT*FROMusersWHEREuser_id1;-- 余额应减少SELECT*FROMproducts;-- 库存应减少SELECT*FROMordersWHEREorder_idnew_order_id;SELECT*FROMorder_itemsWHEREorder_idnew_order_id;SELECT*FROMcartsWHEREuser_id1;-- 应该为空
1
2 数据库运维备份与恢复数据库备份是运维工作的生命线。
即使程序代码丢失只要数据还在企业就能存活反之数据丢失往往意味着灾难。
10.
1 逻辑备份工具mysqldumpmysqldump是 MySQL 自带的逻辑备份工具它会将数据库结构和数据导出为 SQL 文本文件。
常用命令格式在命令行/终端执行不是在 MySQL 客户端内备份单个数据库mysqldump -u root -p shop_bizd:\backup\shop_biz_backup.sql-u root: 用户名-p: 提示输入密码shop_biz: 要备份的数据库名: 重定向输出到文件备份多个数据库mysqldump -u root -p --databases db1 db2multi_db_backup.sql备份所有数据库 (全库备份)mysqldump -u root -p --all-databasesall_db_backup.sql仅备份表结构 (不含数据)mysqldump -u root -p --no-data shop_bizshop_biz_structure.sql
10.
2 数据灾难恢复实战假设某天shop_biz数据库被误删了-- 模拟删库跑路DROPDATABASEshop_biz;此时不要慌张我们利用刚才备份的shop_biz_backup.sql进行恢复。
恢复方法 1在命令行使用 mysql 命令# 不需要先登录 mysql直接在终端执行# 注意如果备份文件中包含了 CREATE DATABASE 语句加了 --databases 参数会有则不需要手动创建库# 如果只是备份单库通常需要先手动创建空库mysql -u root -pd:\backup\shop_biz_backup.sql恢复方法 2在 MySQL 客户端内使用 source 命令-- 登录 MySQL 后CREATEDATABASEshop_biz;-- 如果备份文件里没写这句USEshop_biz;source d:/backup/shop_biz_backup.sql;执行完毕后再次查询数据你会发现所有表和数据都完好如初。
10.
3 生产环境备份建议自动化不要依赖人工手动备份。
Linux 下使用crontabWindows 下使用“任务计划程序”每天定时执行mysqldump脚本。
异地存储备份文件不要只放在数据库服务器上。
必须上传到云存储 (OSS/S
或另一台物理服务器防止服务器硬盘损坏导致数据和备份同时丢失。
定期演练备份不是目的恢复才是。
每隔一段时间如每季度尝试在一个测试环境中恢复备份确保备份文件是有效的。
本章
总结通过本章的学习你已经完成了一个从设计到实现再到运维保障的完整闭环设计能力掌握了电商核心业务表的 E-R 设计与规范化建表。
编码能力熟练运用复杂的 Transaction 处理多表联动的数据一致性问题。
运维能力学会了使用mysqldump保护你的数据资产。
至此MySQL 基础教程的核心内容已全部结束。
希望这些知识能成为你开发之路上坚实的基石