mysql一对多查询与否定和/或多个标准 [英] mysql one-to-many query with negation and/or multiple criteria
问题描述
我认为像这样的查询会很容易，因为关系数据库的本质，但似乎给我一个合适。我也搜索了，但发现没有什么真正有帮助。以下是情况：
I thought a query like this would be pretty easy because of the nature of relational databases but it seems to be giving me a fit. I also searched around but found nothing that really helped. Here's the situation:
假设我对产品和产品标签有一个简单的关系。这是一对多的关系，所以我们可以有以下：
Let's say I have a simple relationship for products and product tags. This is a one-to-many relationship, so we could have the following:
productid | tag
========================
1 | Car
1 | Black
1 | Ford
2 | Car
2 | Red
2 | Ford
3 | Car
3 | Black
3 | Lexus
4 | Motorcycle
4 | Black
5 | Skateboard
5 | Black
6 | Skateboard
6 | Green
最高效查询方式是什么（Ford OR Black OR Skateboard）AND NOT（Motorcycles OR Green）？我需要做的另一个查询是像所有（Car）或（Skateboard）或（Green AND Motorcycle）或（Red AND Motorcycle）。
What's the most efficient way to query for all (Ford OR Black OR Skateboard) AND NOT (Motorcycles OR Green)? Another query I'm going to need to do is something like all (Car) or (Skateboard) or (Green AND Motorcycle) or (Red AND Motorcycle).
产品表中有大约150k条记录，标签表中有600k条记录，因此查询将需要尽可能高效。这里有一个查询，我一直在搞乱（示例＃1），但它似乎花了大约4秒左右。任何帮助将非常感激。
There are about 150k records in the products table and 600k records in the tags tables, so the query is going to need to be as efficient as possible. Here's one query that I've been messing around with (example #1), but it seems to be taking about 4 seconds or so. Any help would be much appreciated.
SELECT p.productid
FROM products p
JOIN producttags tag1 USING (productid)
WHERE p.active = 1
AND tag1.tag IN ( 'Ford', 'Black', 'Skatebaord' )
AND p.productid NOT IN (SELECT productid
FROM producttags
WHERE tag IN ( 'Motorcycle', 'Green' ));
到目前为止，我发现的最快的查询是这样的。它需要100-200ms，但它似乎很不灵活和丑陋。基本上我正在抓住所有符合 Ford ， Black 或 Skateboard 。我将这些匹配产品的所有标签连接成冒号分隔字符串，并删除所有在上匹配的产品：绿色：和：摩托车：。任何想法？
The quickest query I've found so far is something like this. It's taking 100-200ms but it seems pretty inflexible and ugly. Basically I'm grabbing all products that match Ford, Black, or Skateboard. Them I'm concatenating all of the tags for those matched products into a colon-separated string and removing all products that match on :Green: AND :Motorcycle:. Any thoughts?
SELECT p.productid,
Concat(':', Group_concat(alltags.tag SEPARATOR ':'), ':') AS taglist
FROM products p
JOIN producttags tag1 USING (productid)
JOIN producttags alltags USING (productid)
WHERE p.active = 1
AND tag1.tag IN ( 'Ford', 'Black', 'Skateboard' )
GROUP BY tag1.productid
HAVING ( taglist NOT LIKE '%:Motorcycle:%'
AND taglist NOT LIKE '%:Green:%' );
推荐答案
我写的排除连接没有子查询：
I'd write the exclusion join with no subqueries:
SELECT p.productid
FROM products p
INNER JOIN producttags AS t ON p.productid = t.productid
LEFT OUTER JOIN producttags AS x ON p.productid = x.productid
AND x.tag IN ('Motorcycle', 'Green')
WHERE p.active = 1
AND t.tag IN ( 'Ford', 'Black', 'Skateboard' )
AND x.productid IS NULL;
确保您对这两个列（active，productid）上的产品有索引。
Make sure you have an index on products over the two columns (active, productid) in that order.
您还应该按照该顺序在两个列（productid，tag）上的producttag上建立索引。
You should also have an index on producttags over the two columns (productid, tag) in that order.
另一个查询我要做的是像所有（汽车）或（滑板）或（绿色和摩托车）或（红色和摩托车）。
有时，这些复杂的条件对MySQL优化器来说很难。一个常见的解决方法是使用UNION组合更简单的查询：
Sometimes these complex conditions are hard for the MySQL optimizer. One common workaround is to use UNION to combine simpler queries:
SELECT p.productid
FROM products p
INNER JOIN producttags AS t1 ON p.productid = t1.productid
WHERE p.active = 1
AND t1.tag IN ('Car', 'Skateboard')
UNION ALL
SELECT p.productid
FROM products p
INNER JOIN producttags AS t1 ON p.productid = t1.productid
INNER JOIN producttags AS t2 ON p.productid = t2.productid
WHERE p.active = 1
AND t1.tag IN ('Motorcycle')
AND t2.tag IN ('Green', 'Red');
PS：您的标记表不是实体属性值表。
PS: Your tagging table is not an Entity-Attribute-Value table.
这篇关于mysql一对多查询与否定和/或多个标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
