选择具有多个标签的行...有更好的方法吗？

Question

我正在尝试建立一个标签系统来从数据库中选择产品。我读过，实现这一点的最佳方法是通过多对多关系，因为当有大量记录时，使用 LIKE '%tag%' 会变得很慢。我还读过 this 问题，在多个标签上匹配您必须为每个标签进行联接正在请求标签。

我有 3 个表：shop_products、shop_categories 和 shop_products_categories。例如，我需要能够找到同时具有“鲜花”和“浪漫”标签的产品。

SELECT p.sku, p.name, p.path FROM shop_products p
  LEFT JOIN shop_products_categories pc1 ON p.sku = pc1.product_sku
  LEFT JOIN shop_categories c1           ON pc1.category_id = c1.id
  LEFT JOIN shop_products_categories pc2 ON p.sku = pc2.product_sku
  LEFT JOIN shop_categories c2           ON pc2.category_id = c2.id
WHERE c1.path = 'flowers' AND c2.path = 'romance'

这是我当前正在构建的演示查询，用于在为其编写相关 PHP 代码之前检查它是否有效。但这真的是最好的方法吗？我发现很难相信没有比为每个搜索标签进行联接更好的方法了。 感谢您的任何建议。 :)

Answer 1

无需进行多次连接。如果需要匹配所有标签，可以使用带有子查询的 IN 子句，如下所示：

select p.sku, p.name, p.path 
from shop_products p
where p.sku in (
    select pc.product_sku 
    from shop_products_categories pc 
    inner join shop_categories c on pc.category_id = c.id
    where c.path in ('flowers', 'romance')
    group by pc.product_sku
    having count(distinct c.path) = 2
)

请注意，您需要将数字 2 调整为要匹配的唯一标签的数量。请注意，这是用户输入的数据，并且他们输入相同的标签两次。

Answer 2

SELECT
    p.sku, p.name, p.path  
FROM
    shop_products p 
    INNER JOIN
    (
        SELECT A.sku FROM
        (
            SELECT product_sku sku FROM shop_products_categories
            WHERE category_id=(SELECT id FROM shop_categories WHERE path='flowers')
        ) A
        INNER JOIN
        (
            SELECT product_sku sku FROM shop_products_categories
            WHERE category_id=(SELECT id FROM shop_categories WHERE path='romance')
        ) B
        USING (sku)
    ) flowers_and romance
    USING (sku)
;

确保您有这些索引：

ALTER TABLE shop_categories ADD INDEX (path,id);
ALTER TABLE shop_categories ADD UNIQUE INDEX (path);
ALTER TABLE shop_products_categories ADD INDEX (product_sku,category_id);