23 lines
1.3 KiB
SQL
23 lines
1.3 KiB
SQL
-- 创建用户单词学习进度表
|
|
CREATE TABLE IF NOT EXISTS ai_user_word_progress (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '进度ID',
|
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
|
vocabulary_id BIGINT NOT NULL COMMENT '单词ID',
|
|
status VARCHAR(20) DEFAULT 'not_started' COMMENT '学习状态',
|
|
study_count INT DEFAULT 0 COMMENT '学习次数',
|
|
correct_count INT DEFAULT 0 COMMENT '正确次数',
|
|
wrong_count INT DEFAULT 0 COMMENT '错误次数',
|
|
proficiency INT DEFAULT 0 COMMENT '熟练度(0-100)',
|
|
next_review_at TIMESTAMP NULL COMMENT '下次复习时间',
|
|
review_interval INT DEFAULT 1 COMMENT '复习间隔(天)',
|
|
first_studied_at TIMESTAMP NOT NULL COMMENT '首次学习时间',
|
|
last_studied_at TIMESTAMP NOT NULL COMMENT '最后学习时间',
|
|
mastered_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_vocab (user_id, vocabulary_id),
|
|
INDEX idx_next_review (next_review_at),
|
|
INDEX idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户单词学习进度表';
|