SpringBoot 2.7 + Vue 3 校园二手平台：3大核心模块数据库设计与10张表ER图详解

校园二手交易平台作为高校信息化建设的重要组成部分，其数据库设计直接决定了系统的性能、扩展性和数据一致性。本文将深入剖析用户、商品、订单三大核心模块的数据库设计，通过10张核心表的ER图与建表SQL，展示如何将业务需求转化为高效的数据模型。

1. 系统架构与数据库设计原则

现代校园二手平台通常采用前后端分离架构，后端基于SpringBoot 2.7提供RESTful API，前端使用Vue 3构建用户界面，数据库选用MySQL 8.0。在设计数据库时，我们遵循以下核心原则：

• 高并发处理 ：采用读写分离策略，主库处理写操作，从库处理读操作
• 数据一致性 ：通过事务管理和乐观锁机制确保交易数据准确
• 扩展性 ：使用分表分库设计应对未来数据增长
• 安全性 ：敏感数据加密存储，如用户密码采用BCrypt加密

提示：校园二手平台的特点是访问具有明显的时段性（如学期初和学期末交易活跃），需要在数据库配置中合理设置连接池参数。

2. 用户模块设计

用户模块负责平台所有参与者的身份认证和基础信息管理，包含3张核心表：

2.1 用户表(user)

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(100) NOT NULL COMMENT '加密密码',
  `salt` varchar(20) NOT NULL COMMENT '加密盐值',
  `email` varchar(100) NOT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `student_id` varchar(20) DEFAULT NULL COMMENT '学号',
  `college` varchar(50) DEFAULT NULL COMMENT '学院',
  `major` varchar(50) DEFAULT NULL COMMENT '专业',
  `grade` varchar(10) DEFAULT NULL COMMENT '年级',
  `credit_score` int DEFAULT '100' COMMENT '信用分(初始100)',
  `status` tinyint DEFAULT '1' COMMENT '状态(0-禁用 1-正常)',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_college` (`college`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

2.2 用户认证表(user_auth)

CREATE TABLE `user_auth` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `identity_type` varchar(20) NOT NULL COMMENT '认证类型(学号/手机号/邮箱)',
  `identifier` varchar(100) NOT NULL COMMENT '认证标识',
  `credential` varchar(100) NOT NULL COMMENT '认证凭证',
  `verified` tinyint(1) DEFAULT '0' COMMENT '是否已验证',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_type_identifier` (`identity_type`,`identifier`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户认证表';

2.3 用户地址表(user_address)

CREATE TABLE `user_address` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `receiver_name` varchar(50) NOT NULL COMMENT '收货人姓名',
  `receiver_phone` varchar(20) NOT NULL COMMENT '收货人电话',
  `building` varchar(50) NOT NULL COMMENT '楼栋',
  `room` varchar(20) NOT NULL COMMENT '房间号',
  `is_default` tinyint(1) DEFAULT '0' COMMENT '是否默认地址',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户地址表';

用户模块ER关系如下图所示：

+-------------+       +-------------+       +---------------+
|    user     |       |  user_auth  |       | user_address  |
+-------------+       +-------------+       +---------------+
| PK id       |<---+  | PK id       |    +--| PK id         |
|   username  |    |  |   user_id   |----+  |   user_id     |
|   password  |    |  | identity_type|      | receiver_name |
|   ...       |    |  | identifier  |      | ...           |
+-------------+    |  +-------------+      +---------------+
                   |
                   |
+-------------+    |
| user_credit |    |
+-------------+    |
| PK id       |----+
|   user_id   |
|   score     |
|   ...       |
+-------------+

3. 商品模块设计

商品模块是平台的核心，包含商品信息、分类、图片等数据，设计4张核心表：

3.1 商品表(product)

CREATE TABLE `product` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL COMMENT '发布用户ID',
  `category_id` int NOT NULL COMMENT '分类ID',
  `title` varchar(100) NOT NULL COMMENT '商品标题',
  `description` text COMMENT '商品描述',
  `price` decimal(10,2) NOT NULL COMMENT '价格',
  `original_price` decimal(10,2) DEFAULT NULL COMMENT '原价',
  `condition_level` tinyint DEFAULT '3' COMMENT '成色(1-5)',
  `view_count` int DEFAULT '0' COMMENT '浏览数',
  `like_count` int DEFAULT '0' COMMENT '点赞数',
  `status` tinyint DEFAULT '1' COMMENT '状态(0-下架 1-在售 2-已售)',
  `recommend` tinyint(1) DEFAULT '0' COMMENT '是否推荐',
  `transaction_mode` tinyint DEFAULT '1' COMMENT '交易方式(1-面交 2-快递)',
  `location` varchar(100) DEFAULT NULL COMMENT '交易地点',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_category` (`category_id`),
  FULLTEXT KEY `ft_title_desc` (`title`,`description`) COMMENT '全文索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表';

3.2 商品分类表(category)

CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT '0' COMMENT '父分类ID',
  `name` varchar(50) NOT NULL COMMENT '分类名称',
  `icon` varchar(100) DEFAULT NULL COMMENT '图标',
  `sort` int DEFAULT '0' COMMENT '排序',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品分类表';

3.3 商品图片表(product_image)

CREATE TABLE `product_image` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `product_id` bigint NOT NULL COMMENT '商品ID',
  `url` varchar(255) NOT NULL COMMENT '图片URL',
  `sort` int DEFAULT '0' COMMENT '排序',
  `is_main` tinyint(1) DEFAULT '0' COMMENT '是否主图',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品图片表';

3.4 商品收藏表(product_favorite)

CREATE TABLE `product_favorite` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `product_id` bigint NOT NULL COMMENT '商品ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_product` (`user_id`,`product_id`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品收藏表';

商品模块ER关系如下图所示：

+-------------+       +-------------+       +---------------+
|  category   |       |  product    |       | product_image |
+-------------+       +-------------+       +---------------+
| PK id       |<---+  | PK id       |----+  | PK id         |
|   parent_id |    |  |   user_id   |    |  |   product_id  |
|   name      |    |  | category_id |----+  |   url         |
|   ...       |    |  |   title     |      |   ...         |
+-------------+    |  |   ...       |      +---------------+
                   |  +-------------+
                   |         ^
                   |         |
                   |  +---------------+
                   |  | product_favorite|
                   |  +---------------+
                   |  | PK id         |
                   +--|   user_id     |
                      |   product_id  |
                      |   ...         |
                      +---------------+

4. 订单模块设计

订单模块处理交易流程，包含3张核心表和若干辅助表：

4.1 订单表(order)

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL COMMENT '订单编号',
  `buyer_id` bigint NOT NULL COMMENT '买家ID',
  `seller_id` bigint NOT NULL COMMENT '卖家ID',
  `product_id` bigint NOT NULL COMMENT '商品ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额',
  `payment_amount` decimal(10,2) NOT NULL COMMENT '实付金额',
  `shipping_fee` decimal(10,2) DEFAULT '0.00' COMMENT '运费',
  `transaction_mode` tinyint NOT NULL DEFAULT '1' COMMENT '交易方式(1-面交 2-快递)',
  `shipping_address` varchar(255) DEFAULT NULL COMMENT '收货地址',
  `buyer_message` varchar(255) DEFAULT NULL COMMENT '买家留言',
  `order_status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态(0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-已退款)',
  `payment_time` datetime DEFAULT NULL COMMENT '支付时间',
  `delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
  `completion_time` datetime DEFAULT NULL COMMENT '完成时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_buyer` (`buyer_id`),
  KEY `idx_seller` (`seller_id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

4.2 订单明细表(order_item)

CREATE TABLE `order_item` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `product_id` bigint NOT NULL COMMENT '商品ID',
  `product_title` varchar(100) NOT NULL COMMENT '商品标题',
  `product_image` varchar(255) DEFAULT NULL COMMENT '商品图片',
  `current_price` decimal(10,2) NOT NULL COMMENT '成交价',
  `quantity` int NOT NULL DEFAULT '1' COMMENT '数量',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单明细表';

4.3 支付记录表(payment)

CREATE TABLE `payment` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `order_no` varchar(32) NOT NULL COMMENT '订单编号',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `payment_method` tinyint NOT NULL COMMENT '支付方式(1-支付宝 2-微信 3-校园卡)',
  `payment_amount` decimal(10,2) NOT NULL COMMENT '支付金额',
  `transaction_no` varchar(64) DEFAULT NULL COMMENT '第三方交易号',
  `payment_status` tinyint NOT NULL DEFAULT '0' COMMENT '支付状态(0-未支付 1-支付成功 2-支付失败)',
  `payment_time` datetime DEFAULT NULL COMMENT '支付时间',
  `callback_time` datetime DEFAULT NULL COMMENT '回调时间',
  `callback_content` text COMMENT '回调内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order` (`order_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_transaction` (`transaction_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='支付记录表';

订单模块ER关系如下图所示：

+-------------+       +-------------+       +---------------+
|   order     |       | order_item  |       |   payment     |
+-------------+       +-------------+       +---------------+
| PK id       |<---+  | PK id       |    +--| PK id         |
|   order_no  |    |  |   order_id  |----+  |   order_id    |
|   buyer_id  |    |  | product_id  |      |   order_no    |
|   ...       |    |  |   ...       |      |   ...         |
+-------------+    |  +-------------+      +---------------+
                   |
                   |
+-------------+    |
| order_log   |    |
+-------------+    |
| PK id       |----+
|   order_id  |
|   status    |
|   ...       |
+-------------+

5. 数据库优化策略

针对校园二手平台的高并发场景，我们实施了以下优化措施：

5.1 索引优化

• 为所有外键字段建立普通索引
• 为高频查询条件建立组合索引，如 (category_id, status)
• 商品表添加全文索引支持标题和描述的搜索

5.2 分表策略

表名	分表方式	分表键	分表数量	说明
order	水平分表	user_id	16	按用户ID哈希分表
product	水平分表	category_id	8	按商品分类分表
payment	水平分表	create_time	按月	按创建时间分表

5.3 缓存设计

// Spring Cache配置示例
@Configuration
@EnableCaching
public class CacheConfig {
    
    @Bean
    public CacheManager cacheManager() {
        return new RedisCacheManager(
            RedisCacheWriter.nonLockingRedisCacheWriter(redisConnectionFactory()),
            RedisCacheConfiguration.defaultCacheConfig()
                .entryTtl(Duration.ofMinutes(30))  // 默认缓存30分钟
                .disableCachingNullValues()
                .serializeValuesWith(SerializationPair.fromSerializer(new GenericJackson2JsonRedisSerializer()))
        );
    }
    
    @Bean
    public RedisConnectionFactory redisConnectionFactory() {
        return new LettuceConnectionFactory();
    }
}

5.4 事务管理

@Service
@RequiredArgsConstructor
public class OrderServiceImpl implements OrderService {
    
    private final OrderMapper orderMapper;
    private final ProductMapper productMapper;
    
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void createOrder(OrderCreateDTO dto) {
        // 1. 检查商品状态
        Product product = productMapper.selectByIdForUpdate(dto.getProductId());
        if (product.getStatus() != ProductStatus.ON_SALE) {
            throw new BusinessException("商品已下架或售出");
        }
        
        // 2. 扣减库存(乐观锁)
        int updateCount = productMapper.reduceStock(
            dto.getProductId(), 
            product.getVersion(), 
            dto.getQuantity()
        );
        if (updateCount == 0) {
            throw new ConcurrentException("库存不足");
        }
        
        // 3. 创建订单
        Order order = convertToOrder(dto);
        orderMapper.insert(order);
        
        // 4. 创建支付记录
        Payment payment = buildPayment(order);
        paymentMapper.insert(payment);
    }
}

6. 典型查询场景示例

6.1 商品分页查询

-- 商品列表分页查询(带分类过滤)
SELECT 
    p.id, p.title, p.price, p.view_count, p.like_count,
    pi.url AS cover_image,
    u.username AS seller_name, u.avatar AS seller_avatar
FROM 
    product p
LEFT JOIN 
    (SELECT product_id, url FROM product_image WHERE is_main = 1) pi ON p.id = pi.product_id
LEFT JOIN 
    user u ON p.user_id = u.id
WHERE 
    p.category_id = #{categoryId}
    AND p.status = 1
ORDER BY 
    p.create_time DESC
LIMIT #{offset}, #{pageSize};

6.2 订单状态统计

// 使用MyBatis的注解方式实现复杂查询
@Select("SELECT " +
        "SUM(CASE WHEN order_status = 0 THEN 1 ELSE 0 END) AS toPay, " +
        "SUM(CASE WHEN order_status = 1 THEN 1 ELSE 0 END) AS toShip, " +
        "SUM(CASE WHEN order_status = 2 THEN 1 ELSE 0 END) AS toReceive, " +
        "SUM(CASE WHEN order_status = 3 THEN 1 ELSE 0 END) AS completed " +
        "FROM `order` WHERE user_id = #{userId}")
@Results({
    @Result(property = "toPay", column = "toPay"),
    @Result(property = "toShip", column = "toShip"),
    @Result(property = "toReceive", column = "toReceive"),
    @Result(property = "completed", column = "completed")
})
OrderStatsVO getOrderStats(@Param("userId") Long userId);

6.3 商品搜索优化

// 使用Elasticsearch实现商品搜索
@Service
@RequiredArgsConstructor
public class ProductSearchServiceImpl implements ProductSearchService {

    private final ProductRepository productRepository;
    
    @Override
    public Page<ProductVO> search(ProductSearchDTO dto) {
        // 构建查询条件
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder()
            .withQuery(QueryBuilders.multiMatchQuery(dto.getKeyword(), "title", "description"))
            .withFilter(QueryBuilders.termQuery("status", ProductStatus.ON_SALE.getValue()))
            .withPageable(PageRequest.of(dto.getPage(), dto.getSize()))
            .withSort(SortBuilders.fieldSort("createTime").order(SortOrder.DESC));
        
        // 添加分类过滤
        if (dto.getCategoryId() != null) {
            queryBuilder.withFilter(QueryBuilders.termQuery("categoryId", dto.getCategoryId()));
        }
        
        // 执行搜索
        SearchHits<ProductDocument> searchHits = elasticsearchOperations.search(
            queryBuilder.build(), 
            ProductDocument.class
        );
        
        // 转换结果
        List<ProductVO> products = searchHits.stream()
            .map(hit -> convertToVO(hit.getContent()))
            .collect(Collectors.toList());
            
        return new PageImpl<>(products, PageRequest.of(dto.getPage(), dto.getSize()), searchHits.getTotalHits());
    }
}