MySQL 中的数据库架构设计和外键
我正在尝试为博客创建一个标准化数据库，以允许回复评论。鉴于我找到的一些答案，看来我需要查看邻接表模型和修改后的先序树遍历算法。然而，在阅读一些关于它的内容之后，我还没有找到它的例子使用外键来强制数据完整性。能做到吗？
在这种情况下，您建议采用哪种数据库设计？理想情况下，我希望能够消除父注释，并且通过使用 PK-FK 关系（PK = 主键，FK = 外键）还能够消除所有子注释，以避免将孤儿保留在桌子。
更新：作为澄清，我还想知道在允许回复评论的博客中使用哪种数据库设计（即回复回复原始线程的评论的评论）。
I'm trying to create a normalized database for a blog allowing reply to comments. Given a few answers I found, it seems that I need to look at the adjacency list model and the modified preorder tree traversal algorithm. However, after reading a bit about it, I haven't found an example of it using foreign keys to enforce data integrity. Can it be done?
What database design do you recommend in this case? Ideally, I'd like to be able to eliminate a parent comment, and by using PK-FK relationships (PK = primary key, FK = foreign key) also be able to eliminate all the child comments in order to avoid keeping orphans in the table.
UPDATE: As a clarification, I'd also like to know which database design is used in blogs that allow reply to comments (that is, comments that reply to a comment that reply to an original thread).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Reddits 源代码在 Github 上开源，看看他们是怎么做的......
Reddits source code is open source on Github, see how they do it....
除非您使用级联删除（不推荐），否则您需要从上到下遍历树，然后开始从底部删除节点。您可以在应用程序层或数据库层（即在存储过程中）执行此遍历，但无论哪种方式，所有删除都应汇总到数据库事务中。
我不清楚你在这里问什么。整个模型很大程度上依赖于 PK/FK 关系（评论与其父评论之间）。您链接的文章没有明确说明（我可以看到），但 title 列将是 PK，parent 列将是 FK。
Unless you are using cascading deletes (not recommended), you will need to walk the tree from top to bottom, then start deleting nodes from the bottom. You could do this traversal in the app layer or the database layer (i.e. in a stored proc), but either way all the deletes should be rolled into a database transaction.
I'm not clear on what you are asking here. The whole model pretty much relies on a PK/FK relationship (between a comment and its parent). The article you link doesn't say so explicitly (that I can see), but the title column would be the PK, and the parent column would be the FK.
是的，MySQL 支持外键和级联删除作为其数据库存储引擎的一部分。您将需要使用 InnoDB，这不是默认的。更改存储引擎非常容易，甚至可以在创建表后完成。
MySQL官方文档：
http://dev.mysql .com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
修改：
ALTER TABLE table1 ENGINE=InnoDB;
使用 InnoDB 引擎与 MyISAM 引擎（MySQL 默认存储引擎）相比还有其他优点和缺点。因此，在将其投入生产环境之前，您应该仔细研究两者。 MyISAM 不支持外键。
Yes, MySQL supports Foreign Keys and cascading deletes as part of its database storage engine. You will need to use InnoDB which is not the default. Changing the storage engine is really easy and can be done even after tables are created.
MySQL official documentation:
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
Alteration:
ALTER TABLE table1 ENGINE=InnoDB;
There are additional pros and cons to using an InnoDB engine vs a MyISAM engine(MySQL storage engine default). So you should carefully research the two before putting it in a production environment. MyISAM does not support Foreign Keys.