文章/答案/技术大牛

发布

问评论系统的MYSQL模式
EN

Stack Overflow用户

提问于 2020-05-04 21:01:42

回答 4查看 331关注 0票数 4

在我创建的一个应用上，我有一个基本的评论系统，它的表格设置如下：

CREATE TABLE `meet_comment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `meet_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `date_created` datetime NOT NULL,
 `comment` mediumtext NOT NULL,
 PRIMARY KEY (`id`),
 KEY `meet_id` (`meet_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `meet_comment_ibfk_1` FOREIGN KEY (`meet_id`) REFERENCES `meet` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `meet_comment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

meet_id是对用户正在评论的对象的引用。这很好用，尽管目前如果用户编辑评论，我只是更新comment字段。

如果评论被编辑，我希望能够看到评论的历史记录，最好的方法是什么？我猜我需要另一个表来保存注释和引用meet_comment.id？可能是这样的：

CREATE TABLE `meet_comment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `meet_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `meet_id` (`meet_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `meet_comment_ibfk_1` FOREIGN KEY (`meet_id`) REFERENCES `meet` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `meet_comment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `meet_comment_content` (
 `revision` int(3) NOT NULL,
 `meet_comment_id` int(11) NOT NULL,
 `date_created` datetime NOT NULL,
 `comment` mediumtext NOT NULL,
  UNIQUE KEY `revision_2` (`revision`,`meet_comment_id`),
 KEY `revision` (`revision`),
 KEY `meet_comment_id` (`meet_comment_id`),
 CONSTRAINT `meet_comment_content` FOREIGN KEY (`meet_comment_id`) REFERENCES `meet_comment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

如果是这样，那么查询表的最佳方式是什么，我猜我可以执行一个连接来获得所需的数据？

mysql

database

database-design

schema

回答 4

Stack Overflow用户

发布于 2020-05-08 11:03:16

修改历史导致了一场拉锯战--

的简单性，
的规模化效率，
查询的复杂性，
磁盘空间，或者膨胀的

<代码>F29

考虑以下“一般”原则：

有两个表：

Current -- Comment
History的当前版本-- Comment

的所有版本

Current使主查询更简单、更高效。

创建或编辑注释意味着Current中的INSERT或UPDATE，以及(总是) History中的INSERT。

History的模式与Current略有不同，因为需要有修订号，可能还有其他日期/标志/等等。

这并没有解决磁盘空间问题；我怀疑在需要担心磁盘空间问题之前，您可以坚持一段时间。一种技术是“压缩”主TEXT列并将其放入BLOB中。(注意:另一个模式差异。并增加了代码的复杂性。)典型的文本缩小了3:1。

旁注：int(3) -- (3)什么也没说。INT始终为4个字节。建议使用SMALLINT UNSIGNED，它需要2个字节，范围为0..64K。

我会放弃FOREIGN KEYs，尤其是CASCADE；这两个表中可能会有一些引用纠缠在一起。无论如何，您将需要仔细编写代码来处理各种任务，从而消除对FK的需求。

每个表都应该有一个PRIMARY KEY。对于数据的“集群”，我建议使用(comment_id, revision)，而不是相反。

并且，考虑meet_id是否是PK的一部分。

票数 3

Stack Overflow用户

发布于 2020-05-13 22:43:54

-- User USR exists.
--
user {USR}
  PK {USR}

-- Meet MET exists.
--
meet {MET}
  PK {MET}

-- User USR attended meet MET.
--
user_meet {USR, MET}
       PK {USR, MET}

FK1 {USR} REFERENCES user {USR}
FK2 {MET} REFERENCES meet {MET}

如果用户对会议进行了评论，则该用户参加了该会议。

-- User USR commented CMT_TXT on meet MET,
-- as comment number CMT_NO from that user on that meet.
--
comment {USR, MET, CMT_NO, CMT_TXT}
     PK {USR, MET, CMT_NO}

FK {USR, MET} REFERENCES user_meet {USR, MET}

-- For any given (USR, MET), CMT_NO is in {1,2,3 ...}.

-- User USR commented CMT_TXT on meet MET,
-- in version number VER_NO of comment number CMT_NO
-- from that user on that meet.
--
comment_history {USR, MET, CMT_NO, VER_NO, CMT_TXT}
             PK {USR, MET, CMT_NO, VER_NO}

     FK {USR, MET, CMT_NO} REFERENCES
comment {USR, MET, CMT_NO}
     ON UPDATE CASCADE
     ON DELETE RESTRICT

-- For any given (USR, MET, CMT_NO), VER_NO is in {1,2,3 ...}.

我建议将删除评论视为新版本。例如，您可以将comment.CMT_TXT设置为空字符串。这样既保留了历史记录又保留了数据的完整性。

如果comment_history只包含旧版本，并且当前版本在comment中，则使用视图：

CREATE VIEW v_comment
AS
SELECT USR
     , MET
     , CMT_NO
     , 'current' as VER
     , CMT_TXT
FROM comment
UNION
SELECT USR
     , MET
     , CMT_NO
     , concat('ver_', lpad(VER_NO,3,0)) as VER
     , CMT_TXT
FROM comment_history ;

要查询特定用户和会议的备注和历史记录，请执行以下操作：

SELECT *
FROM v_comment
WHERE USR = specific_user
  AND MET = specific_meet
ORDER BY CMT_NO, VER

注意：

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

票数 3

Stack Overflow用户

发布于 2020-05-14 13:27:04

如果您不想使用太多复杂的查询和实现，则可以使用简单的实现。

Like连接

SELECT
  mc.id,
  mc.meet_id as meet_id,
  mc.user_id as user_id,
  mcc.revision as revision,
  mcc.date_created as date_created,
  mcc.comment as comment
FROM
  meet_comment as mc
LEFT JOIN
  meet_comment_content as mcc
ON
  mc.meet_id = mcc.meet_id

此查询将给出完整的评论历史记录。

如果您需要某个特定用户的注释，则必须将其添加到where中

WHERE
    mc.user_id = <User Id>

如果您只需要来自特定会议ID和用户的评论，则可以将其添加到WHERE子句中。

WHERE
    mc.meet_id = <Meet ID> AND
    mc.user_id = <User ID>

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/61592813

复制

相似问题

问评论系统的MYSQL模式
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问评论系统的MYSQL模式EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问评论系统的MYSQL模式
EN