orderFood/oederFoodVue/create-order-tables.sql

-- 创建订单相关表

USE orderFood;

-- 订单表
CREATE TABLE IF NOT EXISTS `orders` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '订单ID',
  `order_number` VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号',
  `total_amount` DECIMAL(10,2) NOT NULL COMMENT '商品总价',
  `delivery_fee` DECIMAL(10,2) NOT NULL COMMENT '配送费',
  `final_amount` DECIMAL(10,2) NOT NULL COMMENT '订单总额',
  `status` INT NOT NULL DEFAULT 0 COMMENT '订单状态 0-待支付 1-已支付 2-已取消',
  `remark` VARCHAR(500) COMMENT '备注',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  INDEX `idx_order_number` (`order_number`),
  INDEX `idx_status` (`status`),
  INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单详情表
CREATE TABLE IF NOT EXISTS `order_detail` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '详情ID',
  `order_id` BIGINT NOT NULL COMMENT '订单ID',
  `dish_id` BIGINT NOT NULL COMMENT '菜品ID',
  `dish_name` VARCHAR(100) NOT NULL COMMENT '菜品名称',
  `price` DECIMAL(10,2) NOT NULL COMMENT '单价',
  `quantity` INT NOT NULL COMMENT '数量',
  `subtotal` DECIMAL(10,2) NOT NULL COMMENT '小计',
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_dish_id` (`dish_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单详情表';