心理咨询建表
/*
Navicat Premium Dump SQL
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80040 (8.0.40)
Source Host : localhost:3306
Source Schema : psychological_counseling
Target Server Type : MySQL
Target Server Version : 80040 (8.0.40)
File Encoding : 65001
Date: 05/01/2026 17:43:20
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for appointments
DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
id bigint NOT NULL AUTO_INCREMENT,
user_id bigint NOT NULL,
counselor_id bigint NOT NULL,
consultation_type enum('PHONE','TEXT','VIDEO') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
duration_minutes int NOT NULL,
scheduled_time datetime NOT NULL,
price decimal(10, 2) NOT NULL,
status enum('PENDING','CONFIRMED','COMPLETED','CANCELLED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'PENDING',
payment_status enum('PENDING','PAID','REFUNDED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'PENDING',
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX user_id(user_id ASC) USING BTREE,
INDEX counselor_id(counselor_id ASC) USING BTREE,
CONSTRAINT appointments_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT appointments_ibfk_2 FOREIGN KEY (counselor_id) REFERENCES counselors (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for child_basic_info
DROP TABLE IF EXISTS child_basic_info;
CREATE TABLE child_basic_info (
id bigint NOT NULL AUTO_INCREMENT,
user_id bigint NOT NULL COMMENT '关联用户ID',
name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
gender varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
birth_date date NULL DEFAULT NULL COMMENT '出生年月',
ethnicity varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '民族',
hometown varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '籍贯',
sibling_rank varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '家中排行',
birth_place varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '出生地',
language_environment varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '语言环境',
current_school varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '现就读学校/园',
current_address varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '现就读住址',
hobbies text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '孩子兴趣爱好',
interest_activities text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '孩子兴趣活动',
health_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
health_description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '身体状况具体描述',
past_illnesses json NULL COMMENT '过往病史',
other_past_illnesses text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '其他病史描述',
father_phone varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '父亲电话',
mother_phone varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '母亲电话',
guardian_phone varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '监护人电话',
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
UNIQUE INDEX user_id_unique(user_id ASC) USING BTREE,
CONSTRAINT child_basic_info_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;
-- Table structure for consultation_records
DROP TABLE IF EXISTS consultation_records;
CREATE TABLE consultation_records (
id bigint NOT NULL AUTO_INCREMENT,
appointment_id bigint NOT NULL,
summary text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
counselor_feedback text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
user_rating int NULL DEFAULT NULL,
user_review text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX appointment_id(appointment_id ASC) USING BTREE,
CONSTRAINT consultation_records_ibfk_1 FOREIGN KEY (appointment_id) REFERENCES appointments (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for consultation_requests
DROP TABLE IF EXISTS consultation_requests;
CREATE TABLE consultation_requests (
id bigint NOT NULL AUTO_INCREMENT,
user_id bigint NOT NULL,
problem_description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
problem_duration varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
preferred_methods json NULL,
attached_images json NULL,
status enum('PENDING','MATCHED','COMPLETED','CANCELLED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'PENDING',
matched_counselor_id bigint NULL DEFAULT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX user_id(user_id ASC) USING BTREE,
INDEX matched_counselor_id(matched_counselor_id ASC) USING BTREE,
CONSTRAINT consultation_requests_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT consultation_requests_ibfk_2 FOREIGN KEY (matched_counselor_id) REFERENCES counselors (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for counselor_reviews
DROP TABLE IF EXISTS counselor_reviews;
CREATE TABLE counselor_reviews (
id bigint NOT NULL AUTO_INCREMENT,
counselor_id bigint NOT NULL,
user_id bigint NOT NULL,
rating int NOT NULL,
review text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX counselor_id(counselor_id ASC) USING BTREE,
INDEX user_id(user_id ASC) USING BTREE,
CONSTRAINT counselor_reviews_ibfk_1 FOREIGN KEY (counselor_id) REFERENCES counselors (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT counselor_reviews_ibfk_2 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for counselors
DROP TABLE IF EXISTS counselors;
CREATE TABLE counselors (
id bigint NOT NULL AUTO_INCREMENT,
user_id bigint NOT NULL,
real_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
id_card varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
qualification_certificate varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
experience_years int NULL DEFAULT 0,
specialization text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
consultation_methods json NULL,
therapy_approaches json NULL,
price_per_hour decimal(10, 2) NULL DEFAULT 0.00,
rating decimal(3, 2) NULL DEFAULT 5.00,
introduction text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
status varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX user_id(user_id ASC) USING BTREE,
CONSTRAINT counselors_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for messages
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
id bigint NOT NULL AUTO_INCREMENT,
sender_id bigint NOT NULL,
receiver_id bigint NOT NULL,
content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
message_type enum('TEXT','IMAGE','SYSTEM') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'TEXT',
consultation_request_id bigint NULL DEFAULT NULL,
is_read tinyint(1) NULL DEFAULT 0,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX sender_id(sender_id ASC) USING BTREE,
INDEX receiver_id(receiver_id ASC) USING BTREE,
INDEX consultation_request_id(consultation_request_id ASC) USING BTREE,
CONSTRAINT messages_ibfk_1 FOREIGN KEY (sender_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT messages_ibfk_2 FOREIGN KEY (receiver_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT messages_ibfk_3 FOREIGN KEY (consultation_request_id) REFERENCES consultation_requests (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for question_categories
DROP TABLE IF EXISTS question_categories;
CREATE TABLE question_categories (
id bigint NOT NULL AUTO_INCREMENT,
name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类名称',
description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '分类描述',
sort_order int NULL DEFAULT 0 COMMENT '排序',
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for questions
DROP TABLE IF EXISTS questions;
CREATE TABLE questions (
id bigint NOT NULL AUTO_INCREMENT,
question_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '题目内容',
question_type enum('SINGLE_CHOICE','MULTIPLE_CHOICE','TRUE_FALSE','SHORT_ANSWER') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'SINGLE_CHOICE' COMMENT '题目类型',
difficulty enum('EASY','MEDIUM','HARD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'MEDIUM' COMMENT '难度',
category_id bigint NULL DEFAULT NULL COMMENT '分类ID',
options json NULL COMMENT '选项（JSON格式）',
correct_answer text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '正确答案',
explanation text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '答案解析',
learning_package_id bigint NULL DEFAULT NULL COMMENT '关联学习包ID',
is_active tinyint(1) NULL DEFAULT 1 COMMENT '是否启用',
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX category_id(category_id ASC) USING BTREE,
INDEX learning_package_id(learning_package_id ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for student_records
DROP TABLE IF EXISTS student_records;
CREATE TABLE student_records (
id bigint NOT NULL AUTO_INCREMENT,
counselor_id bigint NOT NULL,
student_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
age int NULL DEFAULT NULL,
grade varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
school varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
parent_contact varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
real_situation text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '家长沟通的现实情况',
interpersonal_relations text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '学校人际情况',
learning_situation text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '学习真实情况',
counselor_notes text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '咨询师备注',
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
INDEX counselor_id(counselor_id ASC) USING BTREE,
CONSTRAINT student_records_ibfk_1 FOREIGN KEY (counselor_id) REFERENCES counselors (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;
-- Table structure for users
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id bigint NOT NULL AUTO_INCREMENT,
username varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
password varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
email varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
phone varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
avatar_url varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
gender enum('MALE','FEMALE','OTHER') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
age int NULL DEFAULT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE,
UNIQUE INDEX username(username ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- Table structure for videos
DROP TABLE IF EXISTS videos;
CREATE TABLE videos (
id bigint NOT NULL AUTO_INCREMENT,
title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频标题',
description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '视频描述',
video_url varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频文件URL',
cover_url varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '封面图片URL',
tags varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '标签，多个用逗号分隔',
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
浙公网安备 33010602011771号