第41章：实战项目三 —— 电商订单系统

本章目标

学完本章后，你将能够：

1. 设计电商核心表结构（用户、商品、订单、订单明细）
2. 使用悲观锁实现库存扣减防超卖
3. 在事务中完成下单流程
4. 实现订单多条件查询和统计

41.1 表结构设计

users ───< orders ───< order_items >─── products
              │
         order_items (quantity, price)

模型定义

type Product struct {
	ID      uint    `gorm:"primaryKey"`
	Name    string  `gorm:"type:varchar(200);not null"`
	Price   float64 `gorm:"type:decimal(10,2);not null"`
	Stock   int     `gorm:"type:int unsigned;not null;default:0"`
	Version int     `gorm:"default:0"` // 乐观锁版本号
}

type Order struct {
	ID          uint        `gorm:"primaryKey"`
	OrderNo     string      `gorm:"type:varchar(32);uniqueIndex"`
	UserID      uint
	TotalAmount float64     `gorm:"type:decimal(12,2)"`
	Status      string      `gorm:"type:varchar(20);default:'pending'"`
	Items       []OrderItem `gorm:"foreignKey:OrderID"`
	CreatedAt   time.Time
}

type OrderItem struct {
	ID        uint    `gorm:"primaryKey"`
	OrderID   uint
	ProductID uint
	Product   Product `gorm:"foreignKey:ProductID"`
	Quantity  int
	Price     float64 `gorm:"type:decimal(10,2)"` // 下单时的单价
}

---

41.2 核心下单流程

type CreateOrderInput struct {
	Items []struct {
		ProductID uint `json:"product_id"`
		Quantity  int  `json:"quantity"`
	} `json:"items"`
}

func PlaceOrder(db *gorm.DB, userID uint, input CreateOrderInput) (*Order, error) {
	var order Order

	err := db.Transaction(func(tx *gorm.DB) error {
		// 1. 锁定商品并检查库存
		var products []Product
		productIDs := make([]uint, len(input.Items))
		for i, item := range input.Items {
			productIDs[i] = item.ProductID
		}

		tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("id IN ?", productIDs).
			Find(&products)

		// 构建 ID → Product 映射
		productMap := make(map[uint]Product)
		for _, p := range products {
			productMap[p.ID] = p
		}

		// 2. 计算总价并生成订单明细
		var totalAmount float64
		var orderItems []OrderItem

		for _, item := range input.Items {
			product, ok := productMap[item.ProductID]
			if !ok {
				return fmt.Errorf("商品 %d 不存在", item.ProductID)
			}
			if product.Stock < item.Quantity {
				return fmt.Errorf("商品 %s 库存不足", product.Name)
			}

			orderItems = append(orderItems, OrderItem{
				ProductID: product.ID,
				Quantity:  item.Quantity,
				Price:     product.Price,
			})
			totalAmount += product.Price * float64(item.Quantity)
		}

		// 3. 创建订单
		order = Order{
			OrderNo:     generateOrderNo(),
			UserID:      userID,
			TotalAmount: totalAmount,
			Status:      "paid",
			Items:       orderItems,
		}
		if err := tx.Create(&order).Error; err != nil {
			return err
		}

		// 4. 扣库存
		for _, item := range input.Items {
			result := tx.Model(&Product{}).
				Where("id = ? AND stock >= ?", item.ProductID, item.Quantity).
				Update("stock", gorm.Expr("stock - ?", item.Quantity))

			if result.RowsAffected == 0 {
				return fmt.Errorf("扣库存失败：商品 %d", item.ProductID)
			}
		}

		return nil
	})

	if err != nil {
		return nil, err
	}
	return &order, nil
}

func generateOrderNo() string {
	return fmt.Sprintf("ORD%d%04d", time.Now().Unix(), rand.Intn(10000))
}

---

41.3 订单查询

func ListOrders(db *gorm.DB, userID uint, status string, page, pageSize int) ([]Order, int64, error) {
	var orders []Order
	var total int64

	query := db.Model(&Order{}).Where("user_id = ?", userID)
	if status != "" {
		query = query.Where("status = ?", status)
	}

	query.Count(&total)
	err := query.
		Preload("Items.Product").
		Order("created_at desc").
		Scopes(Paginate(page, pageSize)).
		Find(&orders).Error

	return orders, total, err
}

---

41.4 订单统计

type SalesReport struct {
	TotalOrders  int64   `json:"total_orders"`
	TotalRevenue float64 `json:"total_revenue"`
	AvgOrderAmt  float64 `json:"avg_order_amount"`
	TopProducts  []ProductSales `json:"top_products"`
}

type ProductSales struct {
	ProductID   uint    `json:"product_id"`
	ProductName string  `json:"product_name"`
	SoldCount   int64   `json:"sold_count"`
	TotalAmount float64 `json:"total_amount"`
}

func GetSalesReport(db *gorm.DB, start, end time.Time) (*SalesReport, error) {
	var report SalesReport

	// 订单统计
	db.Model(&Order{}).
		Where("created_at BETWEEN ? AND ? AND status = ?", start, end, "paid").
		Select("COUNT(*) as total_orders, COALESCE(SUM(total_amount), 0) as total_revenue, COALESCE(AVG(total_amount), 0) as avg_order_amount").
		Scan(&report)

	// 热销商品 TOP 10
	db.Model(&OrderItem{}).
		Select("order_items.product_id, products.name as product_name, SUM(order_items.quantity) as sold_count, SUM(order_items.price * order_items.quantity) as total_amount").
		Joins("JOIN orders ON orders.id = order_items.order_id").
		Joins("JOIN products ON products.id = order_items.product_id").
		Where("orders.created_at BETWEEN ? AND ? AND orders.status = ?", start, end, "paid").
		Group("order_items.product_id, products.name").
		Order("sold_count desc").
		Limit(10).
		Scan(&report.TopProducts)

	return &report, nil
}

---

本章小结

本项目综合运用了：事务（下单）、悲观锁（防超卖）、Preload（关联查询）、聚合统计（GROUP BY + SUM）、JOIN（报表查询）。

练习题

1. 使用悲观锁实现库存扣减，确保不超卖。
2. 实现"取消订单"功能：恢复库存 + 更新订单状态（事务）。
3. 用乐观锁（版本号）重写库存扣减逻辑。
4. 实现"热销商品 TOP 10"的统计接口。
5. （思考题）高并发下，悲观锁会成为性能瓶颈。有哪些替代方案？