如何从不同的MySQL表中删除所有相关记录
我的数据库中有两个表：“故事”和“投票”。
故事表包含有关文章的所有信息（例如标题、正文、作者姓名等）。 投票表包含所有文章的所有投票。 votes 中有一个名为 item_name 的字段，其中包含投票的文章的 id。
简而言之，votes 中的 item_name 等于 stories 中的 id（取决于用户对哪篇文章投票） ）。
问题是：如果一篇文章被删除，如何自动删除votes表中与该文章相关的所有记录？
是否可以在数据库本身中进行设置，从而无需设置额外的 PHP 查询？
这是我的数据库结构：
stories
投票 
I have two tables in my database: 'stories' and 'votes'.
The stories table contains all information about an article (e.g. title, body, author name, etc.). The votes table contains all votes on all articles. There is a field in votes called item_name which contains the id of an article that a vote was cast on.
In simple words, item_name in votes is equal to id in stories (depending on which article a user voted on).
The question is: if an article gets deleted, how can I automatically delete all records in the votes table that are related to that article?
Can it be set up in the database itself, so there's no need to set up additional PHP queries?
Here is my database's structure:
stories
votes
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MySQL 有不同的存储引擎。在大多数 MySQL 管理 IDE 中，默认存储引擎是
MyISAM。如果您的表使用此存储引擎，则无法在它们的列之间创建任何关系，并且必须自己删除相关列。对于您想要的，
innoDB是最好的解决方案。这种类型的存储引擎使得在不同表的列之间创建关系成为可能。您的表中将需要主键和外键，创建这些关系后，您必须为其指定以下功能：ON UPDATE CASCADE和ON DELETE CASCADE所以您赢了删除主记录后不需要删除相关列值。
看一下 此链接 在基准测试中比较它们。
MySQL has different storage engines. Default storage engine is
MyISAMin most of MySQL managing IDEs. If you use this storage engine for your tables, you can not create any relation between their columns and you have to delete related columns by yourself.For what you want,
innoDBis the best solution. This type of storage engine make the situation to create relation between columns of different tables. You will need Primary Keys and Foreign Keys in your tables and after creating these relation, you must specify the features below to them:ON UPDATE CASCADEandON DELETE CASCADESo you won't need to delete related columns values after deleting the main record.
Take a look at this link to compare them in a benchmark test.
在相关字段上设置外键，并在删除时级联。
也许有人能够给你一个更详细的答案，但你必须使用支持外键的引擎（如 InnoDB），如果你不知道如何手动完成，PHPMyAdmin 应该帮助你完成剩下的事情。
简而言之，在字段上设置删除级联会告诉您的数据库在删除文章（位于另一个表上）时删除具有该约束的每条记录。
请参阅此处了解更多信息：
http://dev .mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
http://forums.digitalpoint.com/showthread.php?t=488163
Set up foreign keys on the related fields with cascade on delete.
Maybe someone will be capable of giving you a more detailed answer, but you have to use an engine that supports foreign keys (like InnoDB), and PHPMyAdmin should help you with the rest if you don't know how to do it by hand.
Simply put, setting cascade on delete on a field tells your database to delete every record that has that constraint when, in your case, the article (that's on another table) is deleted.
See here for more information:
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
http://forums.digitalpoint.com/showthread.php?t=488163