MySQL 新手寻找多对多关系查询

Question

一些背景我有一组数据，代表天际的炼金术成分及其效果。如果您对此不熟悉，您可以将 2-4 种成分混合起来制成药水。每种成分都有4种功效。如果成分之间的任何效果相同，就会制成这种类型的药水。我已将其确定为多对多关系，并按如下方式设置表格：

成分：ing_id（键），ing_name，（其他补充信息）

效果：eff_id（键），eff_name

ing_eff_xref：eff_id，ing_id

I想要输入 2 种或更多可用成分并返回可能的组合，但不知道效果是什么。我的 sql 经验几乎仅限于 phpmyadmin 和简单的选择查询。我想我的问题是：这是为这种类型的关系构建表的正确方法吗？如果我不打算更新表，我是否需要设置外键？是否有一个查询可以接受一组ing_names 并仅返回相交的 eff_names？

如果有人感兴趣，这里是数据库的 mysqldump： http://dl.dropbox.com /u/59699040/alchemy_db.sql

Answer 1

这是为此类关系构建表格的正确方法吗？

是的，但是你不需要在成分表上添加effect1到effect4。

如果我不打算更新表，是否需要设置外键？

是的。获取所需数据的唯一方法是将三个表连接在一起。如果没有外键（或更具体地说，没有适当的索引），查询可能无法很好地执行。当然，总体上行数确实很少，但在这种情况下使用外键是一个很好的做法。

是否有一个查询可以接受一组 ing_names 并仅返回
eff_names 相交吗？

我认为您追求的是这样的结果：

SELECT e.eff_name
FROM ingredients i
INNER JOIN ing_eff_xref ie ON ie.ing_id = i.ing_id
INNER JOIN effects e ON e.eff_id = ie.eff_id
WHERE i.ing_name = 'Abecean Longfin ';

如果您需要查看多种成分的效果，您可以调整 WHERE 子句，如下所示：

WHERE i.ing_name IN ('Abecean Longfin ','Eye of Sabre Cat ','Bear Claws ');

您可能不想要重复的效果，因此您可以执行 SELECT DISTINCT 来消除这些效果。

《天际》中的药水效果可以叠加吗？如果它们可以堆叠，那么您可以使用 COUNT 执行 GROUP BY 查询来获取每个效果的堆叠值：

SELECT e.eff_name, count(*) as value
FROM ingredients i
INNER JOIN ing_eff_xref ie ON ie.ing_id = i.ing_id
INNER JOIN effects e ON e.eff_id = ie.eff_id
WHERE i.ing_name IN ('Eye of Sabre Cat ','Bear Claws ')
GROUP BY e.eff_name;

此查询将列出 6 个效果，值为 1，“恢复耐力”的值为 2。不确定《天际》药剂是否以这种方式起作用，但这只是一个额外的想法。