手把手教你用MySQL搭建苍穹外卖数据库（附完整sky.sql源码）

从零构建外卖系统数据库MySQL实战与设计精要第一次接触数据库设计时我盯着电脑屏幕发呆了整整半小时——那些看似简单的用户地址、菜品分类和订单状态到底该如何用数据表合理表达如果你也曾在数据库建模时感到无从下手这篇实战指南将带你用MySQL一步步构建完整的外卖系统数据库并附赠可直接部署的sky.sql源码。1. 数据库规划外卖业务的核心骨架任何成功的数据库设计都始于对业务逻辑的透彻理解。外卖平台看似简单实则包含用户端、商家端和配送端三个维度的复杂交互。我们需要先梳理出最核心的实体关系用户侧核心表用户信息(user)、地址簿(address_book)商品侧核心表菜品分类(category)、菜品(dish)、套餐(setmeal)交易侧核心表购物车(shopping_cart)、订单(orders)、订单明细(order_detail)提示在设计初期建议用纸笔画出各实体间的关系图这比直接写SQL更能避免后期结构性问题一个典型的ER图应该包含以下关键关系erDiagram USER ||--o{ ADDRESS_BOOK : has CATEGORY ||--o{ DISH : contains DISH ||--o{ DISH_FLAVOR : has SETMEAL ||--o{ SETMEAL_DISH : includes USER ||--o{ SHOPPING_CART : maintains USER ||--o{ ORDERS : places ORDERS ||--o{ ORDER_DETAIL : contains2. 用户模块地址簿的智能设计地址管理是外卖系统的关键功能好的设计应该兼顾查询效率和数据完整性。以下是经过实战检验的地址表结构CREATE TABLE address_book ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL COMMENT 关联用户ID, consignee varchar(50) DEFAULT NULL COMMENT 收货人, phone varchar(11) NOT NULL COMMENT 手机号, province_code varchar(12) DEFAULT NULL COMMENT 省级编码, province_name varchar(32) DEFAULT NULL COMMENT 省级名称, city_code varchar(12) DEFAULT NULL COMMENT 市级编码, district_code varchar(12) DEFAULT NULL COMMENT 区级编码, detail varchar(200) DEFAULT NULL COMMENT 详细地址, is_default tinyint(1) DEFAULT 0 COMMENT 默认地址标记, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;设计要点解析采用三级行政区划编码而非直接存储名称便于后续与地图API集成为user_id建立索引确保用户查询自己地址时的效率使用is_default标记实现默认地址功能避免频繁更新注意地址类数据建议使用utf8mb4字符集确保生僻字和emoji的正常存储3. 商品中心分类与SKU的优雅表达商品体系设计直接影响后续运营灵活性。我们采用分类→菜品两级结构并引入口味定制功能-- 分类表 CREATE TABLE category ( id bigint NOT NULL AUTO_INCREMENT, type int DEFAULT NULL COMMENT 1菜品分类 2套餐分类, name varchar(32) NOT NULL COMMENT 分类名称, sort int DEFAULT 0 COMMENT 展示顺序, status int DEFAULT 1 COMMENT 启用状态, PRIMARY KEY (id), UNIQUE KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 菜品表 CREATE TABLE dish ( id bigint NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, category_id bigint NOT NULL, price decimal(10,2) DEFAULT NULL, image varchar(255) DEFAULT NULL COMMENT 封面图URL, description varchar(255) DEFAULT NULL, status int DEFAULT 1 COMMENT 0停售 1起售, PRIMARY KEY (id), UNIQUE KEY idx_name (name), KEY idx_category (category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 菜品口味表 CREATE TABLE dish_flavor ( id bigint NOT NULL AUTO_INCREMENT, dish_id bigint NOT NULL, name varchar(32) DEFAULT NULL COMMENT 口味名, value varchar(255) DEFAULT NULL COMMENT 可选值JSON, PRIMARY KEY (id), KEY idx_dish (dish_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;商品体系三大设计技巧分类去重通过name字段的唯一索引避免川菜和川菜 这类重复分类状态分离分类和菜品都有独立的状态控制实现灵活上下架口味扩展使用JSON格式存储多选值适应微辣/中辣/重辣等场景4. 订单系统事务与数据一致性的实战订单系统是交易的核心需要特别注意事务完整性和查询效率。我们的设计方案包含主表和明细表-- 订单主表 CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, number varchar(50) DEFAULT NULL COMMENT 订单号, status int DEFAULT 1 COMMENT 订单状态, user_id bigint NOT NULL, address_book_id bigint NOT NULL, order_time datetime NOT NULL, amount decimal(10,2) NOT NULL, remark varchar(100) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY idx_number (number), KEY idx_user (user_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单明细表 CREATE TABLE order_detail ( id bigint NOT NULL AUTO_INCREMENT, order_id bigint NOT NULL, dish_id bigint DEFAULT NULL, setmeal_id bigint DEFAULT NULL, dish_flavor varchar(50) DEFAULT NULL, number int DEFAULT 1 COMMENT 数量, amount decimal(10,2) NOT NULL COMMENT 单价, PRIMARY KEY (id), KEY idx_order (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;订单处理最佳实践使用存储过程处理下单逻辑确保减库存→创建订单→生成明细的事务原子性订单号采用日期随机数模式避免连续编号暴露业务量为status字段建立索引方便后台按状态筛选订单5. 性能优化索引与查询的黄金法则当数据量增长到10万级时这些优化策略能让你的系统保持流畅必须建立的索引用户地址表的user_id字段订单表的user_id和status组合商品分类表的type字段查询优化示例-- 反例全表扫描 SELECT * FROM orders WHERE DATE(order_time) 2023-06-01; -- 正例利用索引范围查询 SELECT * FROM orders WHERE order_time 2023-06-01 00:00:00 AND order_time 2023-06-02 00:00:00;缓存策略使用Redis缓存热门菜品信息对分类数据实施读写分离订单历史采用分表策略按用户ID哈希分片6. 安全防护从SQL注入到数据加密数据库安全不容忽视我们采用五层防护体系预处理语句永远使用参数化查询# 错误示范 cursor.execute(fSELECT * FROM users WHERE name {user_input}) # 正确做法 cursor.execute(SELECT * FROM users WHERE name %s, (user_input,))最小权限原则为应用账号设置精确的CRUD权限敏感数据加密CREATE TABLE user ( ... phone varchar(16) NOT NULL COMMENT AES加密存储, id_number varchar(64) DEFAULT NULL COMMENT RSA加密 );审计日志记录所有管理操作CREATE TABLE audit_log ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint DEFAULT NULL, action varchar(20) NOT NULL, sql_text text, ip_address varchar(45) DEFAULT NULL, created_at datetime NOT NULL, PRIMARY KEY (id) );定期备份采用xtrabackup实现热备份7. 实战进阶分布式ID与分库分表当单表数据突破500万时需要考虑水平扩展方案雪花算法ID生成public class SnowflakeIdGenerator { private final long twepoch 1288834974657L; private final long workerIdBits 5L; private final long datacenterIdBits 5L; private final long sequenceBits 12L; public synchronized long nextId() { long timestamp timeGen(); if (timestamp lastTimestamp) { throw new RuntimeException( String.format(Clock moved backwards. Refusing to generate id for %d milliseconds, lastTimestamp - timestamp)); } // 实现代码继续... } }分库分表策略用户表按user_id % 16分片订单表按月份分表orders_202301使用ShardingSphere实现透明访问完整数据库源码已打包为sky.sql文件包含所有表的DDL语句基础数据INSERT脚本存储过程和函数示例性能测试数据集生成器在本地MySQL执行以下命令即可部署mysql -u root -p sky_take_out sky.sql记得根据实际业务需求调整字段长度和索引策略。我曾在一个日订单量3万的外卖系统中采用这种设计即使在促销高峰期也能保持毫秒级响应。