编辑代码

-- 创建数据库
CREATE DATABASE dating_website;
USE dating_website;

-- 用户基本信息表
CREATE TABLE user_base_info (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE DEFAULT 'default_username',
    password VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    gender ENUM('male', 'female', 'other') NOT NULL,
    birth_date DATE,
    register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_verified BOOLEAN DEFAULT FALSE,
    verification_time TIMESTAMP,
    last_login_time TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户详细信息表
CREATE TABLE user_detail_info (
    user_id INT PRIMARY KEY,
    height INT,
    weight INT,
    education ENUM('primary_school', 'junior_high_school', 'high_school', 'associate_degree', 'bachelor_degree','master_degree', 'doctor_degree', 'other') NOT NULL,
    occupation VARCHAR(100),
    income DECIMAL(10, 2),
    hometown VARCHAR(100),
    residence VARCHAR(100),
    marital_status ENUM('single', 'divorced', 'widowed', 'in_relationship') NOT NULL,
    introduction TEXT,
    hobby_description TEXT,
    FOREIGN KEY (user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 爱好表
CREATE TABLE hobbies (
    hobby_id INT AUTO_INCREMENT PRIMARY KEY,
    hobby_name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户与爱好关联表
CREATE TABLE user_hobby_relation (
    user_id INT,
    hobby_id INT,
    PRIMARY KEY (user_id, hobby_id),
    FOREIGN KEY (user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (hobby_id) REFERENCES hobbies(hobby_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 择偶标准表
CREATE TABLE mate_selection_criteria (
    user_id INT PRIMARY KEY,
    min_age INT,
    max_age INT,
    min_height INT,
    max_height INT,
    expected_gender ENUM('male', 'female', 'any') NOT NULL,
    expected_education ENUM('primary_school', 'junior_high_school', 'high_school', 'associate_degree', 'bachelor_degree','master_degree', 'doctor_degree', 'any') NOT NULL,
    expected_occupation VARCHAR(100),
    expected_income_min DECIMAL(10, 2),
    expected_income_max DECIMAL(10, 2),
    expected_marital_status ENUM('single', 'divorced', 'widowed', 'in_relationship', 'any') NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 消息记录表
CREATE TABLE message_record (
    message_id INT AUTO_INCREMENT PRIMARY KEY,
    sender_user_id INT,
    receiver_user_id INT,
    message_content TEXT,
    send_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_read BOOLEAN DEFAULT FALSE,
    reply_to_message_id INT,
    FOREIGN KEY (sender_user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (receiver_user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (reply_to_message_id) REFERENCES message_record(message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 打招呼记录表
CREATE TABLE greeting_record (
    greeting_id INT AUTO_INCREMENT PRIMARY KEY,
    sender_user_id INT,
    receiver_user_id INT,
    greeting_content VARCHAR(255),
    greeting_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (receiver_user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 点赞记录表
CREATE TABLE like_record (
    like_id INT AUTO_INCREMENT PRIMARY KEY,
    liker_user_id INT,
    liked_user_id INT,
    like_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (liker_user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (liked_user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 会员套餐表
CREATE TABLE member_package (
    package_id INT AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(50) NOT NULL UNIQUE,
    package_price DECIMAL(10, 2) NOT NULL,
    package_duration INT NOT NULL,
    package_features TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户会员记录表
CREATE TABLE user_member_relation (
    relation_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    package_id INT,
    activation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiration_time TIMESTAMP,
    payment_status ENUM('unpaid', 'paid', 'partial_paid') NOT NULL DEFAULT 'unpaid',
    payment_time TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (package_id) REFERENCES member_package(package_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 系统通知表
CREATE TABLE system_notification (
    notification_id INT AUTO_INCREMENT PRIMARY KEY,
    notification_title VARCHAR(100),
    notification_content TEXT,
    send_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 黑名单表
CREATE TABLE blacklist (
    blacklist_id INT AUTO_INCREMENT PRIMARY KEY,
    blocker_user_id INT,
    blocked_user_id INT,
    block_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blocker_user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (blocked_user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 举报记录表
CREATE TABLE report_record (
    report_id INT AUTO_INCREMENT PRIMARY KEY,
    reporter_user_id INT,
    reported_user_id INT,
    report_content TEXT,
    report_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    handling_status ENUM('unhandled', 'in_progress', 'handled') NOT NULL DEFAULT 'unhandled',
    handling_result TEXT,
    FOREIGN KEY (reporter_user_id) REFERENCES user_base_info(user_id),
    FOREIGN KEY (reported_user_id) REFERENCES user_base_info(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;    

SHOW CREATE TABLE user_detail_info;