```sql -- 会员表 CREATE TABLE member ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL COMMENT 用户ID, level VARCHAR(20) NOT NULL DEFAULT bronze COMMENT 会员等级, growth_value INT NOT NULL DEFAULT 0 COMMENT 成长值, total_consumption DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 累计消费金额, upgrade_time DATETIME COMMENT 升级时间, expire_time DATETIME COMMENT 等级过期时间, UNIQUE KEY (user_id) );
-- 成长值变动记录表 CREATE TABLE growth_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL COMMENT 用户ID, change_type VARCHAR(20) NOT NULL COMMENT 变动类型, change_value INT NOT NULL COMMENT 变动值, source VARCHAR(50) COMMENT 来源, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, remark VARCHAR(255) COMMENT 备注 );
-- 会员权益表 CREATE TABLE member_benefit ( id BIGINT PRIMARY KEY AUTO_INCREMENT, level VARCHAR(20) NOT NULL COMMENT 会员等级, benefit_type VARCHAR(20) NOT NULL COMMENT 权益类型, benefit_value VARCHAR(100) NOT NULL COMMENT 权益值, description VARCHAR(255) COMMENT 描述, UNIQUE KEY (level, benefit_type) ); ```
2. 核心业务逻辑实现
成长值计算服务
```java public class GrowthService {
// 计算消费获得的成长值 public int calculateConsumptionGrowth(BigDecimal amount, String productType) { double coefficient = 1.0; switch (productType) { case "promotion": coefficient = 0.8; break; case "new": coefficient = 1.2; break; default: coefficient = 1.0; } return (int)(amount.doubleValue() * coefficient); }
// 更新会员等级 public boolean updateMemberLevel(Long userId) { Member member = memberDao.findByUserId(userId); int requiredGrowth = getRequiredGrowth(member.getLevel());
```java // 后端定时任务检查会员升级 @Scheduled(fixedRate = 3600000) // 每小时执行一次 public void checkMemberUpgrades() { List members = memberDao.findAll(); for (Member member : members) { int currentGrowth = member.getGrowthValue(); String currentLevel = member.getLevel(); int requiredGrowth = getRequiredGrowth(currentLevel);
// 获取会员专属活动 public List getExclusiveActivities(Long userId) { Member member = memberDao.findByUserId(userId); return activityDao.findByMinLevel(member.getLevel()); }