/* Navicat Premium Data Transfer Source Server : online_eai Source Server Type : MySQL Source Server Version : 80027 Source Host : 8.130.28.19:3306 Source Schema : eai Target Server Type : MySQL Target Server Version : 80027 File Encoding : 65001 Date: 18/08/2025 15:30:09 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for ai_speaking_assignment -- ---------------------------- DROP TABLE IF EXISTS `ai_speaking_assignment`; CREATE TABLE `ai_speaking_assignment` ( `ai_speaking_assignment_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'AI口语作业ID（自增主键）', `assignment_id` bigint NOT NULL COMMENT '关联的基础作业ID', `min_duration` int NOT NULL DEFAULT 60 COMMENT '最小持续时间（秒）', `min_interaction` int NOT NULL DEFAULT 5 COMMENT '最小交互次数', `survey_links` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '调查问卷链接（JSON格式）', `survey_duration` int NULL DEFAULT NULL COMMENT '问卷预计完成时间（秒）', PRIMARY KEY (`ai_speaking_assignment_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'AI口语专项作业表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for assignment -- ---------------------------- DROP TABLE IF EXISTS `assignment`; CREATE TABLE `assignment` ( `assignment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '作业ID（自增主键）', `course_id` bigint NOT NULL COMMENT '所属课程ID（外键）', `assignment_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '作业名称', `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '作业描述', `type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '作业类型（如HOMEWORK/QUIZ/EXAM等）', `description_file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '作业要求文件URL', `attachments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '附件URL列表（JSON格式）', `teacher_id` bigint NOT NULL COMMENT '发布教师ID', `start_time` datetime NOT NULL COMMENT '开始时间', `end_time` datetime NOT NULL COMMENT '截止时间', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `teacher_portal_id` bigint NULL DEFAULT NULL, `correct_number` int NULL DEFAULT 0, `engage_number` int NULL DEFAULT 0, `exam_mode` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否为考试模式：0-普通作业，1-考试模式', `exam_duration` int NULL DEFAULT NULL COMMENT '考试时长（分钟），仅考试模式有效', PRIMARY KEY (`assignment_id`) USING BTREE, INDEX `idx_time_range`(`start_time`, `end_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 79 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程作业表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for behavior_record -- ---------------------------- DROP TABLE IF EXISTS `behavior_record`; CREATE TABLE `behavior_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '行为记录ID（自增主键）', `student_id` bigint NOT NULL COMMENT '学生ID', `assignment_id` bigint NOT NULL COMMENT '作业ID', `record_file_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '行为记录文件URL', `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 76 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生学习行为记录表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `class_id` bigint NOT NULL AUTO_INCREMENT, `class_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `class_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `course_id` bigint NOT NULL, `stu_number` int NULL DEFAULT NULL, `teacher_id` bigint NULL DEFAULT NULL, `class_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '班级码', PRIMARY KEY (`class_id`) USING BTREE, INDEX `idx_class_course`(`course_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 37 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for class_student -- ---------------------------- DROP TABLE IF EXISTS `class_student`; CREATE TABLE `class_student` ( `id` bigint NOT NULL AUTO_INCREMENT, `class_id` bigint NOT NULL, `official_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `state` int NULL DEFAULT NULL, `stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_class_student_class`(`class_id`) USING BTREE, INDEX `idx_class_student_official`(`official_number`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 38 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `course_id` bigint NOT NULL AUTO_INCREMENT COMMENT '课程ID（自增主键）', `course_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '课程名称', `teacher_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '教师ID列表（JSON序列化字符串）', `teacher_portal_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '教师门户ID', `enroll_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '选课码（唯一）', `course_imgs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '课程图片列表（JSON序列化字符串）', `target_grade` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '目标年级', `start_time` datetime NOT NULL COMMENT '课程开始时间', `end_time` datetime NOT NULL COMMENT '课程结束时间', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '课程描述', `course_images` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, PRIMARY KEY (`course_id`) USING BTREE, INDEX `idx_time_range`(`start_time`, `end_time`) USING BTREE, INDEX `idx_course_teacher_pid`(`teacher_portal_id`) USING BTREE, INDEX `idx_course_time_range`(`start_time`, `end_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for course_student -- ---------------------------- DROP TABLE IF EXISTS `course_student`; CREATE TABLE `course_student` ( `enrollment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '选课记录ID（自增主键）', `course_id` bigint NOT NULL COMMENT '课程ID', `student_id` bigint NOT NULL COMMENT '学生ID', PRIMARY KEY (`enrollment_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 57 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生选课关联表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for evaluation -- ---------------------------- DROP TABLE IF EXISTS `evaluation`; CREATE TABLE `evaluation` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '评价ID（自增主键）', `student_id` bigint NOT NULL COMMENT '学生ID', `assignment_id` bigint NOT NULL COMMENT '作业ID', `version` int NOT NULL DEFAULT 1 COMMENT '评价版本号', `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评价时间', `author` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '评价人', `overall` int NULL DEFAULT NULL COMMENT '整体评分', `sentence` int NULL DEFAULT NULL COMMENT '句子评分', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE, UNIQUE INDEX `uk_evaluation_assignment_student_version`(`assignment_id`, `student_id`, `version`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '作业评价表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for general_log -- ---------------------------- DROP TABLE IF EXISTS `general_log`; CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `thread_id` bigint UNSIGNED NOT NULL, `server_id` int UNSIGNED NOT NULL, `command_type` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `argument` mediumblob NOT NULL ) ENGINE = CSV CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'General log' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for overall_evaluation -- ---------------------------- DROP TABLE IF EXISTS `overall_evaluation`; CREATE TABLE `overall_evaluation` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）', `student_id` bigint NOT NULL COMMENT '学生ID', `assignment_id` bigint NOT NULL COMMENT '作业ID', `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '作业内容', `version` int NOT NULL DEFAULT 1 COMMENT '批改版本（跟随作业版本）', `evaluation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '整体评估结果', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE, UNIQUE INDEX `uk_overall_eval_assignment_student_version`(`assignment_id`, `student_id`, `version`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '作业整体评估表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for phone_verification -- ---------------------------- DROP TABLE IF EXISTS `phone_verification`; CREATE TABLE `phone_verification` ( `id` bigint NOT NULL, `code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `code_time` datetime(6) NULL DEFAULT NULL, `phone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `UK_1ebndp9bn7nuiyqujcmeifojg`(`phone`) USING BTREE, INDEX `verification_phone_index`(`phone`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for phone_verification_seq -- ---------------------------- DROP TABLE IF EXISTS `phone_verification_seq`; CREATE TABLE `phone_verification_seq` ( `next_val` bigint NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for sentence_evaluation -- ---------------------------- DROP TABLE IF EXISTS `sentence_evaluation`; CREATE TABLE `sentence_evaluation` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）', `student_id` bigint NOT NULL COMMENT '学生ID', `assignment_id` bigint NOT NULL COMMENT '作业ID', `no` int NOT NULL COMMENT '句子编号', `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '原始内容', `sentence` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '待评估句子', `evaluation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '评估结果', `version` int NULL DEFAULT 1 COMMENT '版本号', `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '句子类型', `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '句子类别', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE, UNIQUE INDEX `uk_sentence_eval_assignment_student_version_no`(`assignment_id`, `student_id`, `version`, `no`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 74 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '句子评估表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Migration notes (for existing databases) -- ---------------------------- -- If your production DB already contains duplicate rows, the UNIQUE indexes above will fail to create. -- Recommended dedup strategy before adding UNIQUE indexes: -- -- 1) evaluation: keep latest by time -- DELETE e1 FROM evaluation e1 -- JOIN evaluation e2 -- ON e1.assignment_id=e2.assignment_id AND e1.student_id=e2.student_id AND e1.version=e2.version -- AND e1.id < e2.id; -- -- 2) overall_evaluation: keep latest row per (assignment_id, student_id, version) -- DELETE o1 FROM overall_evaluation o1 -- JOIN overall_evaluation o2 -- ON o1.assignment_id=o2.assignment_id AND o1.student_id=o2.student_id AND o1.version=o2.version -- AND o1.id < o2.id; -- -- 3) sentence_evaluation: keep latest row per (assignment_id, student_id, version, no) -- DELETE s1 FROM sentence_evaluation s1 -- JOIN sentence_evaluation s2 -- ON s1.assignment_id=s2.assignment_id AND s1.student_id=s2.student_id AND s1.version=s2.version AND s1.no=s2.no -- AND s1.id < s2.id; -- ---------------------------- -- Table structure for sign_records -- ---------------------------- DROP TABLE IF EXISTS `sign_records`; CREATE TABLE `sign_records` ( `id` bigint NOT NULL AUTO_INCREMENT, `official_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `sign_in_time` datetime(6) NULL DEFAULT NULL, `sign_status` enum('SIGNED','NOT_SIGNED','FINISHED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `sign_task_id` bigint NOT NULL, `student_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_sign_record_task`(`sign_task_id`) USING BTREE, INDEX `idx_sign_record_official_task`(`official_number`, `sign_task_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for sign_tasks -- ---------------------------- DROP TABLE IF EXISTS `sign_tasks`; CREATE TABLE `sign_tasks` ( `id` bigint NOT NULL AUTO_INCREMENT, `class_id` bigint NOT NULL, `course_id` bigint NOT NULL, `create_time` timestamp NULL DEFAULT NULL, `end_time` timestamp NULL DEFAULT NULL, `sign_code` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `sign_type` enum('NUMBER','QR') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `signed_number` int NULL DEFAULT NULL, `status` enum('PROCESSING','FINISHED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `teacher_id` bigint NOT NULL, `total_number` int NULL DEFAULT NULL, `qr_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `time` int NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_sign_task_teacher`(`teacher_id`) USING BTREE, INDEX `idx_sign_task_class`(`class_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for slow_log -- ---------------------------- DROP TABLE IF EXISTS `slow_log`; CREATE TABLE `slow_log` ( `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `query_time` time(6) NOT NULL, `lock_time` time(6) NOT NULL, `rows_sent` int NOT NULL, `rows_examined` int NOT NULL, `db` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `last_insert_id` int NOT NULL, `insert_id` int NOT NULL, `server_id` int UNSIGNED NOT NULL, `sql_text` mediumblob NOT NULL, `thread_id` bigint UNSIGNED NOT NULL ) ENGINE = CSV CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Slow log' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for student_assignment -- ---------------------------- DROP TABLE IF EXISTS `student_assignment`; CREATE TABLE `student_assignment` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）', `assignment_id` bigint NOT NULL COMMENT '作业ID', `student_id` bigint NOT NULL COMMENT '学生ID', `score` decimal(5, 2) NULL DEFAULT NULL COMMENT '作业得分（百分制）', `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '教师评语', `file_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '作业文件OSS链接', `version` int NOT NULL DEFAULT 1 COMMENT '文件版本（用于onlyoffice key构建）', `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNSUBMITTED' COMMENT '完成状态：UNSUBMITTED/SUBMITTED/GRADED', `quill_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'Quill富文本内容', `text_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '纯文本内容', `html_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'HTML格式内容', `submitted` tinyint(1) NULL DEFAULT 0 COMMENT '是否已交卷：0-未交卷，1-已交卷', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `uk_assignment_student_latest`(`assignment_id`, `student_id`) USING BTREE, INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 95 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生作业关联表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for student_assignment_history -- ---------------------------- DROP TABLE IF EXISTS `student_assignment_history`; CREATE TABLE `student_assignment_history` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）', `assignment_id` bigint NOT NULL COMMENT '作业ID', `student_id` bigint NOT NULL COMMENT '学生ID', `version` int NOT NULL COMMENT '提交版本号', `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '提交状态', `quill_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'Quill富文本内容', `text_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '纯文本内容', `html_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'HTML格式内容', `submit_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `uk_assignment_student_version`(`assignment_id`, `student_id`, `version`) USING BTREE, INDEX `idx_assignment_student_submit_time`(`assignment_id`, `student_id`, `submit_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生作业历史版本表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for translations -- ---------------------------- DROP TABLE IF EXISTS `translations`; CREATE TABLE `translations` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '翻译记录唯一标识（自增）', `user_id` bigint NOT NULL COMMENT '关联用户ID', `assignment_id` bigint NULL DEFAULT NULL COMMENT '关联任务ID（可选）', `query_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '待翻译原文', `query_result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '翻译结果', `target_language` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '目标语言类型（如zh/en等）', `query_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '翻译请求时间', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_user_id`(`user_id`) USING BTREE, INDEX `idx_assignment_id`(`assignment_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '翻译记录表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'User唯一标识（自增）', `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '真实姓名（必填）', `official_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '南大官邮，xxx@smail.nju.edu.cn（必填）', `official_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '南大学号、工号（必填）', `role` smallint NULL DEFAULT NULL, `user_avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'default_avatar_url' COMMENT '头像URL（有默认值、选填）', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，系统自动设置', `last_login_time` datetime NULL DEFAULT NULL COMMENT '上一次登陆时间（可选）', `english_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '英文名（选填）', `gender` smallint NULL DEFAULT NULL, `birthday` date NULL DEFAULT NULL COMMENT '生日（选填）', `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '联系方式（选填）', `content_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '联系邮箱（选填）', `pid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '门户id', `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密存储）（必填）', `contact_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 77 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户表' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;