时间戳

首页 > 解决方案 > 具有逻辑操作的高性能 SQL 标签搜索查询

sql - 具有逻辑操作的高性能 SQL 标签搜索查询

问题描述

如何在 SQL 中实现布尔标记搜索？
这个问题与我能找到的差不多，但有几个。

我知道的唯一真正的解决方案是通过后端代码生成这样的查询并将其放入 SQL 中，但我想它很慢，我也想知道是否有其他方法可以做到这一点（比如有一个主查询而不是多个）。

还有可能使用的解决方案IN或类似的解决方案：
如何在 SQL 中基于多个“标签”查询数据？

我不能使用典型的GROUP BY HAVING COUNT解决方案，因为它不能在具有标签列表的上下文中操作，正如该用户指出的那样： Implementing a tag search with operands

我应该指定大多数现有解决方案都不起作用，因为我正在寻找能够进行更复杂查询的东西，例如括号分组和嵌套操作数。

模式是“毒药” http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

SELECT id AS post_id
FROM posts
WHERE EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS 'random')
AND NOT (
    EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS 'query') AND
    EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS '1')
)
AND EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS '2') 
AND EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS '3')
AND EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS 'racecar')

标签： sqldatabasemany-to-manyrelational-division

解决方案

AGROUP BY HAVING COUNT会起作用——而且速度很快，而且用途广泛。一些例子：

CREATE TABLE tags(
    post_id INT,
    name VARCHAR(50),
    UNIQUE KEY (post_id, name)
);

INSERT INTO tags(post_id, name) VALUES
(1, 'foo'),
(1, 'bar'),

(2, 'foo'),

(3, 'bar'),

(4, 'baz'),

(5, 'foo'),
(5, 'bar'),
(5, 'meh');

-- posts tagged foo AND bar
-- returns 1, 5
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) = 2;

-- posts tagged foo OR bar
-- returns 1, 2, 3, 5
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) > 0;

-- posts tagged (foo AND bar) OR (baz)
-- returns 1, 4, 5
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) = 2
OR     COUNT(CASE WHEN name IN ('baz') THEN 1 END) = 1;

-- posts tagged (foo AND bar) AND (no other tags)
-- returns 1
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) = 2
AND    COUNT(*) = 2;

-- posts tagged (foo OR bar) AND NOT (meh)
-- returns 1, 2, 3
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) > 0
AND    COUNT(CASE WHEN name IN ('meh') THEN 1 END) = 0;

DB<>Fiddle 上的演示

我的答案没有涵盖将表达式转换为tag1 AND tag2 OR tag3相应的表达式，HAVING COUNT但五个示例应该就足够了。

推荐阅读