如何：匹配（搜索空间）（与其他表中的列连接）

I'm lousy at SQL queries so this may be a silly question. However, here is roughly what i'd like to do:

我对SQL查询很糟糕,所以这可能是一个愚蠢的问题。但是,这里大致是我想做的事情:

table corpuses //stuff i'd like to search thru title body ...

表语料库//我希望通过标题正文搜索...

table searches //list of search terms to be applied to corpuses term
...

表搜索//要应用于语料库术语的搜索词列表...

The query i'd like to write is more or less as follows: I beleive I need some sort of a join, but I'm not sure just how to do that. Additionally, I'm not sure that the against() operator will take anything aside from a literal - the docs didn't seem to mention either way.

我想写的查询或多或少如下:我相信我需要某种联接,但我不确定如何做到这一点。另外,我不确定against()运算符会从文字中除外 - 文档似乎没有提到任何一种方式。

select * from corpuses where match (title, body) against (select term from searches);

从匹配(标题,正文)的语料库中选择*(从搜索中选择术语);

I'm using MySQL 5

我正在使用MySQL 5

Any thoughts are greatly appreciated.

任何想法都非常感谢。

Thanks! Brian

1 个解决方案

#1

Sounds like you need to use a FULLTEXT matching expression in your join condition.

听起来你需要在连接条件中使用FULLTEXT匹配表达式。

I've never used a fulltext match in a join condition, so I'm not sure this will work, but hypothetically this might do it:

我从来没有在连接条件下使用全文匹配,所以我不确定这是否会起作用,但假设这可能会这样做:

SELECT DISTINCT c.*
FROM corpuses c JOIN searches s 
  ON (MATCH(c.title, c.body) AGAINST (s.term));

Okay I've tried it using your table definitions and some sample data from the MySQL manual. Here's a query that works (tested with MySQL 5.1.30):

好吧,我已经尝试使用你的表定义和MySQL手册中的一些示例数据。这是一个有效的查询(使用MySQL 5.1.30测试):

SELECT *
FROM corpuses 
WHERE MATCH(title, body)
  AGAINST ( (SELECT GROUP_CONCAT(term SEPARATOR ' ') FROM searches) 
    IN BOOLEAN MODE);

#1