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

Question

一些背景资料，我有一组来自Skyrim的代表炼金术成分及其效果的数据。如果你对此不熟悉，你可以将2-4种成分组合成一种药水。每种成分都有4种作用。如果成分之间的任何影响是相同的，它将制作该类型的药水。我已经将其标识为多对多关系，并将我的表设置如下：

配料: ing_id (密钥)，ing_name，(其他补充信息)

效果: eff_id (键)，eff_name

ing_eff_xref: eff_id，ing_id

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

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

Answer 1

这是为这种类型的关系构建表的正确方式吗？

是的，但是你不需要在配料表上有effect1到effect4。

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

是。获取所需数据的唯一方法是将三个表JOINing在一起。如果没有外键(或者更具体地说，没有适当的索引)，在查询上可能不会有很好的性能。当然，总体上您确实有少量的行，但是在这种类型的场景中，使用外键是一个很好的实践。

是否有一个查询可以接受一组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来消除那些效果。

魔药效果可以在Skyrim中堆叠吗？如果它们可以堆叠，那么您可以使用计数执行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。不确定Skyrim药水是否以这种方式工作，但这只是一个额外的想法。