mysql - 在 SQL 数据库中搜索 N 对 N 关系

Question

这是我的数据库模型:

我需要什么:

我需要输入几个术语并搜索包含所有这些术语的文档 (document.text)。

示例数据:

文件:

id:1  text:dog cat train

id:2  text:dog cat train car

id:3  text:dog cat

id:4  text:dog

条款:

id:1 term:dog

id:2 term:cat

id:3 term:train

id:4 term:car

例子:

我想搜索包含所有术语的文档:dog cat train。结果将是文档 1 和文档 2 但不是文档 3 因为它没有 train 而不是文档 4 因为它没有 cat 或 train.

我的第一次尝试是这样查询:

select document.text from document join document_has_term on       
 document.iddocument=document_has_term.document_iddocument join term on
 term.idterm=document_has_term.term_idterm where term="kindness" and
 term="horrible"

这个查询没有选择任何帖子，但它反射(reflect)了我基本上想要的。

Answer 1

假设每个文档只能有一个术语，如果你运行

SELECT document_iddocument
    FROM document_has_term
    JOIN term ON (term_idterm = idterm)
    WHERE term IN ('cat', 'dog', 'train');

您将有三行，其中三个术语中的每一个都匹配，两行如果两个术语匹配，依此类推。

所以

SELECT document_iddocument
    FROM document_has_term
    JOIN term ON (term_idterm = idterm)
    WHERE term IN ('cat', 'dog', 'train')
GROUP BY document_iddocument
    HAVING COUNT(document_iddocument) = 3;

将只输出具有三个匹配项的文档 ID。

此查询在此阶段甚至不需要访问文档。

您可以将其用作子 SELECT 来获取其 iddocument 在此 id 列表中的文档:

SELECT document.text FROM document WHERE iddocument IN
( the above select );

更复杂的查询

如果您想运行更复杂的搜索，那么也许您应该研究使用 MySQL 进行文本搜索并使用 FULLTEXT 功能。

否则，您需要从“外部”语言开始构建查询，您可以在其中指定类似内容

cat AND NOT dog

这不是 SQL，并将其转换为 SQL 查询。

一种有效的方法是尝试从诸如“cat but not dog”之类的复杂查询中确定哪个组件是最限制的。在此示例中，如果您有 2000 条记录，其中 cat 出现在 100 条记录中，而 dog 出现在除 50 条记录之外的所有记录中，您需要考虑:- 搜索某个术语的存在 的查询非常有效。- 搜索某个术语不存在的查询非常昂贵。

您将首先运行对猫的查询，然后删除确实包含狗的项目。

这种方法也相当复杂。

另一种不太推荐用于大型数据库的可能性是扫描整个 document_has_term 表并查找所有文档的状态:

SELECT document_iddocument,
    SUM(IF(term = 'cat', 1, 0)) AS has_0,
    SUM(IF(term = 'dog', 1, 0)) AS has_1
    FROM document_has_term
    LEFT JOIN term ON (term_idterm = idterm AND term.term IN 
        ('cat', 'dog'))
GROUP BY document_iddocument;

您使用某种外部语言构建的此查询由三部分组成:模板

SELECT document_iddocument,
    <OTHER_FIELDS>
    FROM document_has_term
    LEFT JOIN term ON (term_idterm = idterm AND term.term IN 
        <TERM_LIST>
GROUP BY document_iddocument;

这是固定的；字段列表(每个术语一个)；术语列表。查询越长，列表越长，成本线性增加。

现在您必须将“文本查询”翻译成一系列“它存在/不存在”:

cat and not dog

成为

(has_0) and not (has_1)

实际上，您可以将其集成到一个 HAVING 子句中，这样构建您的查询:

SELECT document.* FROM document
WHERE iddocument IN (

SELECT document_iddocument
    FROM document_has_term
    LEFT JOIN term ON (term_idterm = idterm AND term.term IN 
    ('cat', 'dog') -- list of all terms used
    )
GROUP BY document_iddocument

    HAVING
    (SUM(IF(term = 'cat', 1, 0))!=0) -- for the term "CAT"
    AND NOT                          -- from the "textual query"
    (SUM(IF(term = 'dog', 1, 0))!=0) -- for the term "DOG"
);

Here you can experiment with a little fiddle .

只要您对文本查询使用 SQLish 语法，如果您小心防范 SQL 注入(inject)，Bob is your uncle .如果您不注意清理输入(只允许有效术语和关键字“and”、“or”、“not”和括号，并使用带有 ? 占位符的准备好的查询)，那么 Bobby很可能是你的daddy ...