mysql如何实现订单管理功能_mysql业务表设计示例

订单主表和明细表必须分拆为 orders 与 order_items 两张表，禁用 JSON 字段存储商品；orders 表需含 order_no 唯一单号，order_items 表须存 sku_id 与 quantity；状态变更需状态机 + 版本号防并发，查询应避免深分页而用游标，退款与库存回滚须在单事务内原子执行。

订单主表和明细表必须分拆，不能用 JSON 字段存商品

MySQL 不是文档数据库，把 order_items 塞进 JSON 字段会导致无法索引、无法关联统计、难以做库存扣减。真实业务中要查“某个商品卖了多少”“某用户最近三次订单”，都依赖标准 JOIN 和 WHERE 条件。

典型错误设计：orders 表里加一个 items_json 字段存 [{"id":1,"qty":2},{"id":3,"qty":1}] —— 后续所有报表、对账、退款 拆单都会卡住。

正确做法是两张表：主表 orders 存订单头信息，从表 order_items 每行一条商品记录，靠 order_id 关联。

• orders 表必须有 order_no（唯一业务单号，非自增 ID），用于对外交互和排查
• order_items 表必须有 sku_id 和 quantity，不能只存 product_id（同一商品不同规格价格 / 库存不同）
• 两个表都要有 created_at 和 updated_at，且 updated_at 要设为 ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE `orders` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   `order_no` VARCHAR(32) NOT NULL UNIQUE,   `user_id` BIGINT NOT NULL,   `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1 待支付 2 已支付 3 已发货 4 已完成 5 已取消',   `total_amount` DECIMAL(10,2) NOT NULL,   `pay_amount` DECIMAL(10,2) NOT NULL,   `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,   `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`id`),   KEY `idx_user_status` (`user_id`, `status`),   KEY `idx_order_no` (`order_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order_items` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   `order_id` BIGINT NOT NULL,   `sku_id` BIGINT NOT NULL,   `quantity` INT NOT NULL,   `unit_price` DECIMAL(10,2) NOT NULL,   `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`),   KEY `idx_order_id` (`order_id`),   KEY `idx_sku_id` (`sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

状态变更不能只靠 UPDATE，得用状态机 + 版本号防并发

用户重复点击“确认收货”，或支付回调和手动操作同时触发，会导致 status 被错刷成“已完成”再变成“已发货”。单纯 UPDATE orders SET status = 4 WHERE id = ? 没有前置校验，极容易出错。

必须加条件判断：只允许从“已发货”变成“已完成”，不允许跳变（比如从“待支付”直变“已完成”）。

• 推荐用 CASE WHEN 或应用层校验 + 数据库 WHERE status = ? 双保险
• 关键操作（如支付成功、发货）建议加 version 字段做乐观锁，每次更新带 AND version = ?，失败则重试
• 所有状态变更必须写入 order_logs 表，字段至少含 order_id、from_status、to_status、operator、remark

查询用户订单列表时，避免 SELECT * 和深分页

前端 拉取“第 100 页订单”，用 LIMIT 9990, 20 会让 MySQL 扫描近一万行再丢弃，响应直接超时。真实系统里，用户最多翻到前 10 页，后面应该用游标（cursor）方式。

• 接口参数别传 page=100&size=20，改传 last_order_id=123456，SQL 改成 WHERE id
• SELECT * 在 orders 表上非常危险：一旦加了大字段（如 delivery_address TEXT），IO 和网络开销剧增
• 常用查询字段（如 order_no、status、total_amount、created_at）建联合索引，例如 KEY idx_user_created (user_id, created_at)

退款和库存回滚必须走事务，且不能依赖应用层补偿

用户申请退款时，要同时做三件事：更新订单状态、释放库存、生成退款记录。任何一步失败，整个流程就得回滚。如果用 MQ 异步解耦，又没做幂等和对账，很容易出现“钱退了但库存没加回去”这种资损。

最稳的方式是在一个数据库事务里完成：

• 先 UPDATE stock SET quantity = quantity + ? WHERE sku_id = ? AND quantity >= 0，检查影响行数是否为 1
• 再 INSERT INTO refunds (……) VALUES (……)
• 最后 UPDATE orders SET status = 6 WHERE id = ? AND status = 4（只允许从“已完成”变“已退款”）
• 所有语句在同一个 BEGIN …… COMMIT 内，不要跨连接、不要中间 commit

注意：库存字段必须加 CHECK (quantity>= 0) 约束，且更新时用 quantity = quantity + ? 而不是先查再算，否则并发下会超卖。