摘要

随着人工智能与情感计算技术的深度融合，虚拟情感陪伴系统正成为缓解现代人孤独感、提供情绪价值的重要工具。本文提出并实现了一套高度可扩展、支持动态情绪感知、具备行为策略引擎的多角色恋人陪护系统，其核心数据库基于 MySQL 8.0+ 构建，充分运用了JSON、生成列、窗口函数、全文索引、分区表等高级特性。

系统支持10+种性格角色（如温柔女友、霸道总裁、元气少年、病娇学姐等），结合实时情绪识别、情感值动态计算、上下文记忆与个性化反馈机制，实现深度情感共鸣。本文从需求分析、概念建模、逻辑设计、物理实现、高级SQL、性能优化、安全策略、数据看板支持等八大维度，全面阐述该系统的数据库架构与实现细节，代码级严谨，适用于中大型情感AI产品后端建设。

---

1. 项目背景与需求分析（深度扩展）

1.1 市场痛点与技术挑战

• 孤独经济崛起：Z世代情感需求旺盛，但现实社交成本高。
• AI情感陪伴缺口：现有产品多为“机械回复”，缺乏长期记忆与情绪共情。
• 角色单一化：多数系统仅支持单一角色，无法满足用户多场景需求。
• 数据闭环缺失：缺乏用户情绪变化追踪与反馈优化机制。

1.2 系统核心目标

维度	目标
情感价值	提供安慰、鼓励、陪伴、倾听等情绪支持
角色多样性	支持角色切换、角色成长、角色专属剧情
智能交互	基于上下文与情绪的动态响应
数据驱动	记录用户行为，优化AI策略
隐私安全	敏感数据加密，符合GDPR/CCPA

1.3 功能需求细化（新增模块）

模块	子功能（新增/增强）
用户管理	社交账号绑定、实名认证（可选）、黑名单机制
角色管理	角色等级、技能树、外观定制、语音包
情绪管理	多模态情绪识别（文本+语音+表情）、情绪趋势预测
对话系统	上下文窗口管理、关键词触发、敏感词过滤
行为策略	条件规则引擎、概率化响应、奖励机制（如“亲密度”）
成长系统	用户等级、角色亲密度、成就系统
数据统计	情感健康报告、互动热力图、留存分析
系统管理	运营后台、规则配置、日志审计

---

2. 概念模型设计（E-R模型增强）

新增实体与关系：

• UserPreference（用户偏好）
• IntimacyLevel（亲密度等级）
• Achievement（成就）
• UserAchievement（用户成就记录）
• SensitiveWord（敏感词库）
• SystemLog（系统操作日志）

新增关系：

• 用户与成就多对多
• 角色与技能多对多
• 用户与亲密度一对一（当前值）
• 系统日志与操作者关联

---

3. 逻辑模型设计（关系模式扩展）

实体	属性（新增字段）
User	phone, email, avatar, intimacy_level, last_login, is_verified
Role	level, experience, skill_points, voice_pack_url
UserRole	intimacy_score, unlocked_at, favorite (BOOLEAN)
ChatSession	duration_seconds (GENERATED), is_active (BOOLEAN)
ChatMessage	is_edited, edit_count, reply_to_msg_id (FK)
EmotionState	facial_expression_data (JSON), voice_tone_data (JSON)
BehaviorRule	cooldown_seconds, required_intimacy, condition_script (TEXT)
EmotionScoreLog	previous_score, delta
New: UserPreference	user_id, theme, notification_enabled, auto_emotion_detect
New: Achievement	achievement_id, title, description, icon_url, unlock_condition, reward_type, reward_value
New: UserAchievement	user_id, achievement_id, unlocked_at
New: SensitiveWord	word, severity_level, replacement, created_by, created_at
New: SystemLog	log_id, operator_id, action_type, target_id, details, ip_address, created_at

---

4. 物理模型设计（MySQL DDL 扩展版）

4.1 数据库与表空间配置

-- 创建专用数据库
CREATE DATABASE IF NOT EXISTS lover_companion_v2 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE lover_companion_v2;

-- 启用通用查询日志（调试用，生产关闭）
-- SET GLOBAL general_log = 'ON';
-- SET GLOBAL log_output = 'TABLE';

4.2 用户表（User） - 增强版

CREATE TABLE `User` (
    `user_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户唯一ID',
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一',
    `password_hash` CHAR(64) NOT NULL COMMENT 'SHA-256加密密码',
    `salt` CHAR(16) NOT NULL COMMENT '密码盐值',
    `email` VARCHAR(100) UNIQUE COMMENT '邮箱（可验证）',
    `phone` VARCHAR(15) UNIQUE COMMENT '手机号（可验证）',
    `gender` ENUM('M', 'F', 'O') DEFAULT 'O' COMMENT '性别：男、女、其他',
    `age` TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    `avatar` VARCHAR(255) DEFAULT 'default.png' COMMENT '头像URL',
    `intimacy_level` TINYINT UNSIGNED DEFAULT 1 COMMENT '亲密度等级（1-10）',
    `preferences` JSON COMMENT '用户偏好（JSON格式）',
    `last_login` TIMESTAMP NULL DEFAULT NULL COMMENT '最后登录时间',
    `is_verified` BOOLEAN DEFAULT FALSE COMMENT '是否实名认证',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '账户状态',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 索引优化
    INDEX idx_username (`username`),
    INDEX idx_email (`email`),
    INDEX idx_phone (`phone`),
    INDEX idx_last_login (`last_login`),
    INDEX idx_intimacy (`intimacy_level`),
    FULLTEXT INDEX ft_prefs (`preferences`) -- 假设偏好包含可搜索文本
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
  COMMENT='用户基本信息与账户状态'
  ROW_FORMAT=DYNAMIC; -- 支持大字段与压缩

4.3 角色表（Role） - 增强版

CREATE TABLE `Role` (
    `role_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role_name` VARCHAR(100) NOT NULL COMMENT '角色名称',
    `personality_type` ENUM(
        'gentle', 'dominant', 'cheerful', 
        'mysterious', 'tsundere', 'yandere',
        'innocent', 'mature', 'playful', 'serious'
    ) NOT NULL,
    `description` TEXT COMMENT '角色背景故事',
    `avatar_url` VARCHAR(255) NOT NULL,
    `voice_pack_url` VARCHAR(255) COMMENT '语音包资源',
    `level` TINYINT UNSIGNED DEFAULT 1 COMMENT '角色等级',
    `experience` INT UNSIGNED DEFAULT 0 COMMENT '经验值',
    `skill_points` TINYINT UNSIGNED DEFAULT 0 COMMENT '技能点',
    `unlock_condition` TEXT COMMENT '解锁条件（脚本或描述）',
    `is_premium` BOOLEAN DEFAULT FALSE COMMENT '是否为付费角色',
    `price_credits` INT DEFAULT 0 COMMENT '解锁所需积分',
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_personality (`personality_type`),
    INDEX idx_level (`level`),
    INDEX idx_premium (`is_premium`),
    INDEX idx_experience (`experience`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='虚拟角色定义与成长体系';

4.4 用户-角色关联表（UserRole） - 增强版

CREATE TABLE `UserRole` (
    `user_id` BIGINT UNSIGNED NOT NULL,
    `role_id` INT UNSIGNED NOT NULL,
    `current_role` BOOLEAN DEFAULT FALSE COMMENT '是否为当前使用角色',
    `intimacy_score` INT UNSIGNED DEFAULT 0 COMMENT '与该角色的亲密度分数',
    `unlocked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '解锁时间',
    `favorite` BOOLEAN DEFAULT FALSE COMMENT '是否设为收藏',
    `last_used` TIMESTAMP NULL DEFAULT NULL COMMENT '最后使用时间',
    
    PRIMARY KEY (`user_id`, `role_id`),
    FOREIGN KEY (`user_id`) REFERENCES `User`(`user_id`) ON DELETE CASCADE,
    FOREIGN KEY (`role_id`) REFERENCES `Role`(`role_id`) ON DELETE CASCADE,
    
    INDEX idx_current_role (`current_role`),
    INDEX idx_intimacy_score (`intimacy_score`),
    INDEX idx_last_used (`last_used`),
    INDEX idx_favorite (`favorite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户与角色的多对多关系（含亲密度）';

4.5 情绪状态表（EmotionState） - 多模态增强

CREATE TABLE `EmotionState` (
    `state_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `emotion_label` ENUM(
        'happy', 'sad', 'angry', 'anxious', 
        'calm', 'excited', 'lonely', 'neutral',
        'surprised', 'disgusted', 'fearful'
    ) NOT NULL,
    `intensity` TINYINT UNSIGNED DEFAULT 5 COMMENT '强度1-10',
    `detected_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `source_type` ENUM('text', 'voice', 'facial', 'manual', 'context') NOT NULL,
    `confidence` DECIMAL(3,2) DEFAULT 1.00,
    `text_analysis_result` JSON COMMENT 'NLP分析结果',
    `voice_tone_data` JSON COMMENT '语调、语速、音量等',
    `facial_expression_data` JSON COMMENT '面部识别数据（如OpenCV输出）',
    `context_inference` TEXT COMMENT '基于上下文的推断说明',
    
    FOREIGN KEY (`user_id`) REFERENCES `User`(`user_id`) ON DELETE CASCADE,
    INDEX idx_user_time (`user_id`, `detected_at`),
    INDEX idx_emotion_source (`emotion_label`, `source_type`),
    INDEX idx_confidence (`confidence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多模态情绪识别记录';

4.6 聊天会话表（ChatSession） - 增强版

CREATE TABLE `ChatSession` (
    `session_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `role_id` INT UNSIGNED NOT NULL,
    `start_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `end_time` TIMESTAMP NULL DEFAULT NULL,
    `topic` VARCHAR(100) DEFAULT 'general',
    `mood_score` DECIMAL(3,2) DEFAULT 5.00,
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '会话是否进行中',
    `session_type` ENUM('free', 'guided', 'event') DEFAULT 'free' COMMENT '会话类型',
    `event_trigger` VARCHAR(50) DEFAULT NULL COMMENT '触发的剧情事件',
    
    FOREIGN KEY (`user_id`) REFERENCES `User`(`user_id`) ON DELETE CASCADE,
    FOREIGN KEY (`role_id`) REFERENCES `Role`(`role_id`) ON DELETE CASCADE,
    
    INDEX idx_user_start (`user_id`, `start_time`),
    INDEX idx_active (`is_active`),
    INDEX idx_session_type (`session_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='聊天会话主表（支持剧情模式）';

4.7 聊天消息表（ChatMessage） - 支持回复与编辑

CREATE TABLE `ChatMessage` (
    `msg_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `session_id` BIGINT UNSIGNED NOT NULL,
    `sender_type` ENUM('user', 'role') NOT NULL,
    `content` TEXT NOT NULL,
    `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `sentiment_score` DECIMAL(3,2) DEFAULT 0.00,
    `emotion_tag` VARCHAR(20) DEFAULT NULL,
    `is_edited` BOOLEAN DEFAULT FALSE,
    `edit_count` TINYINT UNSIGNED DEFAULT 0,
    `reply_to_msg_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '回复的消息ID',
    `word_count` INT GENERATED ALWAYS AS (
        GREATEST(1, CHAR_LENGTH(TRIM(content)) - 
        CHAR_LENGTH(REPLACE(TRIM(content), ' ', '')) + 1)
    ) STORED,
    `char_count` INT GENERATED ALWAYS AS (CHAR_LENGTH(content)) STORED,
    
    FOREIGN KEY (`session_id`) REFERENCES `ChatSession`(`session_id`) ON DELETE CASCADE,
    FOREIGN KEY (`reply_to_msg_id`) REFERENCES `ChatMessage`(`msg_id`) ON DELETE SET NULL,
    
    FULLTEXT INDEX ft_content (`content`),
    INDEX idx_session_sender (`session_id`, `sender_type`),
    INDEX idx_sent_at (`sent_at`),
    INDEX idx_reply_to (`reply_to_msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
  COMMENT='聊天消息明细（支持回复、编辑、字数统计）';

4.8 行为规则表（BehaviorRule） - 引擎级增强

CREATE TABLE `BehaviorRule` (
    `rule_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role_id` INT UNSIGNED NOT NULL,
    `trigger_emotion` VARCHAR(20) NOT NULL COMMENT '可为多个情绪，逗号分隔',
    `trigger_keyword` VARCHAR(100) DEFAULT NULL COMMENT '触发关键词',
    `required_intimacy` TINYINT UNSIGNED DEFAULT 1 COMMENT '最低亲密度等级',
    `required_role_level` TINYINT UNSIGNED DEFAULT 1,
    `cooldown_seconds` INT DEFAULT 300 COMMENT '冷却时间（秒）',
    `last_triggered` TIMESTAMP NULL DEFAULT NULL,
    `response_template` TEXT NOT NULL,
    `response_type` ENUM('text', 'text+audio', 'action', 'event') DEFAULT 'text',
    `weight` DECIMAL(3,2) DEFAULT 1.00,
    `priority` TINYINT DEFAULT 5,
    `condition_script` TEXT COMMENT 'Lua或JS脚本，用于复杂条件判断',
    `enabled` BOOLEAN DEFAULT TRUE,
    `created_by` BIGINT UNSIGNED DEFAULT 1 COMMENT '创建者ID（运营/开发）',
    
    FOREIGN KEY (`role_id`) REFERENCES `Role`(`role_id`) ON DELETE CASCADE,
    INDEX idx_role_trigger (`role_id`, `trigger_emotion`),
    INDEX idx_priority (`priority`),
    INDEX idx_last_triggered (`last_triggered`),
    INDEX idx_keyword (`trigger_keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='高级行为规则引擎';

4.9 情感值日志表（EmotionScoreLog） - 增量记录

CREATE TABLE `EmotionScoreLog` (
    `log_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `score` DECIMAL(4,2) NOT NULL,
    `previous_score` DECIMAL(4,2) DEFAULT NULL,
    `delta` DECIMAL(4,2) GENERATED ALWAYS AS (score - COALESCE(previous_score, score)) STORED,
    `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `reason` VARCHAR(100) NOT NULL,
    `source_module` ENUM('chat', 'event', 'system', 'manual') DEFAULT 'chat',
    
    FOREIGN KEY (`user_id`) REFERENCES `User`(`user_id`) ON DELETE CASCADE,
    INDEX idx_user_time (`user_id`, `timestamp`),
    INDEX idx_delta (`delta`),
    INDEX idx_reason (`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='情感值变化日志（含增量）';

4.10 新增表：用户偏好（UserPreference）

CREATE TABLE `UserPreference` (
    `user_id` BIGINT UNSIGNED PRIMARY KEY,
    `theme` ENUM('light', 'dark', 'pink', 'blue') DEFAULT 'light',
    `font_size` ENUM('small', 'medium', 'large') DEFAULT 'medium',
    `notification_enabled` BOOLEAN DEFAULT TRUE,
    `auto_emotion_detect` BOOLEAN DEFAULT TRUE,
    `sensitive_filter_level` ENUM('low', 'medium', 'high') DEFAULT 'medium',
    `ai_response_speed` ENUM('slow', 'normal', 'fast') DEFAULT 'normal',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `User`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户界面与功能偏好';

4.11 新增表：成就系统（Achievement & UserAchievement）

-- 成就定义
CREATE TABLE `Achievement` (
    `achievement_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `icon_url` VARCHAR(255),
    `unlock_condition` TEXT NOT NULL COMMENT '解锁条件（如：累计聊天100次）',
    `reward_type` ENUM('credits', 'item', 'role', 'none') DEFAULT 'credits',
    `reward_value` INT DEFAULT 0,
    `rarity` ENUM('common', 'uncommon', 'rare', 'epic') DEFAULT 'common',
    `is_hidden` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成就库';

-- 用户成就记录
CREATE TABLE `UserAchievement` (
    `user_id` BIGINT UNSIGNED NOT NULL,
    `achievement_id` INT UNSIGNED NOT NULL,
    `unlocked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `progress` JSON DEFAULT NULL COMMENT '进度记录（如{"current":50,"target":100}）',
    
    PRIMARY KEY (`user_id`, `achievement_id`),
    FOREIGN KEY (`user_id`) REFERENCES `User`(`user_id`) ON DELETE CASCADE,
    FOREIGN KEY (`achievement_id`) REFERENCES `Achievement`(`achievement_id`) ON DELETE CASCADE,
    
    INDEX idx_unlocked_at (`unlocked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户成就解锁记录';

4.13 新增表：系统日志（SystemLog）

CREATE TABLE `SystemLog` (
    `log_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `operator_id` BIGINT UNSIGNED NOT NULL COMMENT '操作者ID（0为系统）',
    `action_type` VARCHAR(50) NOT NULL COMMENT '如：login, rule_update, user_ban',
    `target_id` BIGINT UNSIGNED NULL COMMENT '目标ID（如被操作的user_id）',
    `details` JSON COMMENT '操作详情',
    `ip_address` VARCHAR(45) NOT NULL COMMENT 'IPv6兼容',
    `user_agent` VARCHAR(255) COMMENT '客户端信息',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_action_time (`action_type`, `created_at`),
    INDEX idx_operator (`operator_id`),
    INDEX idx_ip (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统操作审计日志';

5. 高级SQL实现与业务逻辑（增强版）

5.1 获取用户当前角色及完整上下文

SELECT 
    u.user_id,
    u.username,
    u.intimacy_level,
    ur.intimacy_score,
    r.role_name,
    r.personality_type,
    r.level AS role_level,
    es.emotion_label,
    es.intensity,
    es.confidence,
    COALESCE(cs.session_id, 0) AS current_session_id,
    up.theme,
    up.notification_enabled
FROM User u
JOIN UserRole ur ON u.user_id = ur.user_id AND ur.current_role = TRUE
JOIN Role r ON ur.role_id = r.role_id
LEFT JOIN EmotionState es ON u.user_id = es.user_id 
    AND es.detected_at = (
        SELECT MAX(detected_at) 
        FROM EmotionState 
        WHERE user_id = u.user_id
    )
LEFT JOIN ChatSession cs ON u.user_id = cs.user_id AND cs.is_active = TRUE
LEFT JOIN UserPreference up ON u.user_id = up.user_id
WHERE u.user_id = 1001;

5.2 复杂行为规则匹配（支持关键词+情绪+亲密度）

SELECT 
    br.*,
    (br.weight * (11 - br.priority) * 
     CASE WHEN ur.intimacy_score >= br.required_intimacy * 100 THEN 1.2 ELSE 0.8 END
    ) AS final_score
FROM BehaviorRule br
JOIN UserRole ur ON br.role_id = ur.role_id
JOIN EmotionState es ON ur.user_id = es.user_id
JOIN User u ON ur.user_id = u.user_id
WHERE ur.user_id = 1001
    AND ur.current_role = TRUE
    AND br.enabled = TRUE
    AND (
        FIND_IN_SET(es.emotion_label, br.trigger_emotion) > 0
        OR (br.trigger_keyword IS NOT NULL 
            AND EXISTS (
                SELECT 1 FROM ChatMessage cm 
                WHERE cm.session_id IN (
                    SELECT session_id FROM ChatSession 
                    WHERE user_id = 1001 AND is_active = TRUE
                )
                AND cm.content LIKE CONCAT('%', br.trigger_keyword, '%')
                AND cm.sent_at > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
            )
        )
    )
    AND ur.intimacy_score >= (br.required_intimacy - 1) * 100
    AND r.level >= br.required_role_level
    AND (br.last_triggered IS NULL OR br.last_triggered < DATE_SUB(NOW(), INTERVAL br.cooldown_seconds SECOND))
ORDER BY final_score DESC
LIMIT 1;

5.3 情感值趋势分析（7日滑动平均）

SELECT 
    DATE(timestamp) AS date,
    AVG(score) AS daily_avg,
    AVG(AVG(score)) OVER (
        ORDER BY DATE(timestamp) 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d,
    STDDEV(score) AS volatility,
    COUNT(*) AS interactions
FROM EmotionScoreLog
WHERE user_id = 1001
    AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(timestamp)
ORDER BY date;

5.4 成就解锁检测（模拟触发）

-- 检查用户是否满足某成就条件（示例：累计聊天100次）
SELECT 
    a.title,
    a.reward_type,
    a.reward_value
FROM Achievement a
LEFT JOIN UserAchievement ua ON a.achievement_id = ua.achievement_id AND ua.user_id = 1001
WHERE a.achievement_id = 101
    AND ua.achievement_id IS NULL -- 未解锁
    AND (
        -- 模拟条件：用户总消息数 >= 100
        (a.unlock_condition = 'chat_count_100' 
         AND (SELECT COUNT(*) FROM ChatMessage cm 
              JOIN ChatSession cs ON cm.session_id = cs.session_id 
              WHERE cs.user_id = 1001) >= 100)
    );

5.5 敏感词过滤（全文匹配）

-- 检查消息是否包含敏感词
SELECT 
    sw.word, 
    sw.severity_level, 
    sw.replacement
FROM SensitiveWord sw
WHERE MATCH('用户输入的文本内容') AGAINST (sw.word IN NATURAL LANGUAGE MODE)
   OR ('用户输入的文本内容' LIKE CONCAT('%', sw.word, '%'));

6. 性能优化与安全建议（企业级）

6.1 索引策略

• 覆盖索引：UserRole(user_id, current_role, intimacy_score) 用于快速获取当前角色亲密度
• 前缀索引：对长文本字段使用前缀索引