mysql搭建论坛问答系统数据库架构

正确做法是将用户表与权限字段分离，用user、role、user_role三张表实现角色权限动态管理，支持多角色和JOIN查询，避免硬编码权限。

用户表和权限字段必须拆开设计

论坛系统里，user 表不能只存账号密码。常见错误是把角色（如“版主”“管理员”）、权限（如“删帖”“封禁”）硬编码进字段，导致后期改权限要改代码+改数据。正确做法是用三张表：

• user：只存 id、username、password_hash、email、created_at
• role：存 id、name（如 'member'、'moderator'）
• user_role：关联表，字段为 user_id、role_id、assigned_at

这样支持

一个用户多个角色，也方便通过 JOIN 动态查权限，而不是靠 CASE WHEN 硬判断。

帖子与回复必须用自引用外键 + 索引优化

问答系统里，问题（question）和回答（answer）本质都是“内容”，但混在一张表里会导致查询逻辑混乱；全拆成两张表又冗余。更通用的方案是单张 post 表，靠字段区分：

CREATE TABLE `post` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` BIGINT UNSIGNED NULL COMMENT '指向本表id，NULL表示问题',
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NULL,
  `content` TEXT NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_parent_user` (`parent_id`, `user_id`),
  KEY `idx_user_created` (`user_id`, `created_at`),
  CONSTRAINT `fk_post_parent` FOREIGN KEY (`parent_id`) REFERENCES `post` (`id`) ON DELETE CASCADE
);

注意两点：

• parent_id 允许为 NULL，问题就是根节点；回答则填对应问题或上层回答的 id
• 必须建联合索引 idx_parent_user，否则按问题查所有回答时会全表扫描

点赞/收藏等计数不能实时 COUNT(*)

每次展示帖子时执行 SELECT COUNT(*) FROM post_vote WHERE post_id = ? AND vote_type = 'up'，QPS 上千就扛不住。真实场景应：

• 在 post 表里加字段 upvote_count、downvote_count、favorite_count
• 用户操作投票/收藏时，用 UPDATE post SET upvote_count = upvote_count + 1 WHERE id = ? 原子更新
• 单独建异步任务定期校验（比如每小时跑一次 SELECT post_id, COUNT(*) FROM post_vote GROUP BY post_id 和表中计数比对）

避免用触发器——MySQL 触发器不支持跨库、调试困难，且在批量导入时容易失效。

全文搜索别硬刚 LIKE %关键词%

用户搜“mysql 连接超时”，用 WHERE content LIKE '%mysql%超时%' 不仅慢，还无法分词、不支持同义词。直接上 MyISAM 的 FULLTEXT？不行，MyISAM 已淘汰，且不支持事务。正确路径是：

• MySQL 5.6+ 开启 innodb_ft_enable_stopword，建 INNODB 表的全文索引：ALTER TABLE post ADD FULLTEXT(title, content)
• 查询用 MATCH(title, content) AGAINST('mysql 超时' IN NATURAL LANGUAGE MODE)
• 但注意：默认停用词列表会过滤掉“mysql”这类短词，需修改 ft_min_word_len = 2 并重启 MySQL，再重建索引

如果搜索需求复杂（模糊、拼音、高亮），别在 MySQL 里死磕，导出到 Elasticsearch 或 Meilisearch 更实际。