37 lines
1.6 KiB
SQL
37 lines
1.6 KiB
SQL
-- 创建用户活跃记录表
|
||
CREATE TABLE IF NOT EXISTS `t_user_activities` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`userId` varchar(255) NOT NULL COMMENT '用户ID',
|
||
`activityType` tinyint NOT NULL COMMENT '活跃类型：1-登录，2-训练，3-饮食记录，4-体重记录，5-资料更新，6-打卡',
|
||
`activityDate` date NOT NULL COMMENT '活跃日期 YYYY-MM-DD',
|
||
`level` tinyint NOT NULL DEFAULT 1 COMMENT '活跃等级：0-无活跃，1-低活跃，2-中活跃，3-高活跃',
|
||
`remark` text COMMENT '备注信息',
|
||
`createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `unique_user_activity_date_type` (`userId`, `activityDate`, `activityType`),
|
||
KEY `idx_user_activity_date` (`userId`, `activityDate`),
|
||
KEY `idx_activity_date` (`activityDate`),
|
||
-- 添加枚举约束
|
||
CONSTRAINT `chk_activity_type` CHECK (`activityType` IN (1, 2, 3, 4, 5, 6)),
|
||
CONSTRAINT `chk_activity_level` CHECK (`level` IN (0, 1, 2, 3))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户活跃记录表';
|
||
|
||
-- 创建索引以优化查询性能
|
||
CREATE INDEX IF NOT EXISTS `idx_user_activity_level` ON `user_activities` (`userId`, `activityDate`, `level`);
|
||
|
||
-- 枚举值说明
|
||
-- activityType 枚举值：
|
||
-- 1: 登录 (LOGIN)
|
||
-- 2: 训练 (WORKOUT)
|
||
-- 3: 饮食记录 (DIET_RECORD)
|
||
-- 4: 体重记录 (WEIGHT_RECORD)
|
||
-- 5: 资料更新 (PROFILE_UPDATE)
|
||
-- 6: 打卡 (CHECKIN)
|
||
|
||
-- level 枚举值：
|
||
-- 0: 无活跃 (NONE)
|
||
-- 1: 低活跃 (LOW)
|
||
-- 2: 中活跃 (MEDIUM)
|
||
-- 3: 高活跃 (HIGH)
|