24 lines
1.4 KiB
SQL
24 lines
1.4 KiB
SQL
-- 创建用户词汇书学习进度表
|
|
CREATE TABLE IF NOT EXISTS user_vocabulary_book_progress (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '进度ID',
|
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
|
book_id VARCHAR(36) NOT NULL COMMENT '词汇书ID',
|
|
learned_words INT DEFAULT 0 COMMENT '已学习单词数',
|
|
mastered_words INT DEFAULT 0 COMMENT '已掌握单词数',
|
|
progress_percentage DECIMAL(5,2) DEFAULT 0.00 COMMENT '学习进度百分比',
|
|
streak_days INT DEFAULT 0 COMMENT '连续学习天数',
|
|
total_study_days INT DEFAULT 0 COMMENT '总学习天数',
|
|
average_daily_words DECIMAL(5,2) DEFAULT 0.00 COMMENT '平均每日学习单词数',
|
|
estimated_completion_date TIMESTAMP NULL COMMENT '预计完成时间',
|
|
is_completed BOOLEAN DEFAULT FALSE COMMENT '是否已完成',
|
|
completed_at TIMESTAMP NULL COMMENT '完成时间',
|
|
started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始学习时间',
|
|
last_studied_at TIMESTAMP NULL COMMENT '最后学习时间',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
|
|
UNIQUE INDEX idx_user_book (user_id, book_id),
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_book_id (book_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户词汇书学习进度表';
|