如何：匹配（搜索空间）对（与其他表中的列结合） [英] How to: match (search space) against (join with column from other table)

问题描述

我在SQL查询方面很糟糕，所以这可能是一个愚蠢的问题。然而，这里大致是我想要做的：

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

表格语料库//东西我想要搜索
标题
正文
...

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

表格搜索//要应用于语料库的搜索字词列表
字词

...

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

我想写的查询或多或少如下：我相信我需要一些一种加入，但我不确定如何做到这一点。另外，我不确定against（）运算符是否会从文字中取出任何东西 - 文档似乎没有提到任何一种方式。

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.

select * from corpuses where match b $ b（title，body）against（从搜索中选择期限
）;

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

我使用MySQL 5

I'm using MySQL 5

非常感谢您的任何想法。

Any thoughts are greatly appreciated.

谢谢！
Brian

Thanks! Brian

推荐答案

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

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

我从来没有在连接条件中使用过全文匹配，所以我不确定这会起作用，但假设这可以做到这一点：

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));

---

好的我已经用你的表格定义以及MySQL手册中的一些示例数据。下面是一个可用的查询（用MySQL 5.1.30测试）：

---

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):

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

这篇关于如何：匹配（搜索空间）对（与其他表中的列结合）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！