跨多个表的Mysql全文搜索相关性

Question

我被分配任务创建一个全站搜索功能。搜索需要查找文章、事件和页面内容。

我以前在MySQL中使用过MATCH()/AGAINST()函数，并知道如何获取结果的相关性，但据我所知，相关性是针对搜索内容（行数等）唯一的，因此来自文章表的结果的相关性不会匹配来自事件表的结果的相关性。

是否有任何方法统一相关性，使得所有三个表格的结果具有可比较的相关性?

- michael

---

从逻辑上看，这似乎是使用联合和子查询与匹配相结合的好地方；但我从未以这种方式进行搜索；因此我怀疑这不是最佳方法。 - xQbert

有没有办法让你加权相关性？只需要简单的乘法。 - bowlerae

我想过将最高相关性归一化为1，但这仍然会使结果分散在多个表中。 - michael

你能提供结构和期望的结果吗？这将有助于更好地理解。 - Angelin Nadar

Answer 1

是的，您可以使用搜索引擎，如Apache Lucene和Solr，将它们很好地统一起来。

http://lucene.apache.org/solr/

如果您只需要在MySQL中完成，可以使用UNION来实现。您可能希望抑制任何与零相关的结果。

您需要决定如何根据匹配哪个表来影响相关性。

例如，假设您希望文章最重要，事件次之，页面最不重要。您可以使用这样的乘数：

set @articles_multiplier=3;
set @events_multiplier=2;
set @pages_multiplier=1;

以下是一个可行的例子，您可以尝试一下，这个例子展示了一些技巧：

创建样本数据：

create database d;
use d;

create table articles (id int primary key, content text) ENGINE = MYISAM;
create table events (id int primary key, content text) ENGINE = MYISAM;
create table pages (id int primary key, content text) ENGINE = MYISAM;

insert into articles values 
(1, "Lorem ipsum dolor sit amet"),
(2, "consectetur adipisicing elit"),
(3, "sed do eiusmod tempor incididunt");

insert into events values 
(1, "Ut enim ad minim veniam"),
(2, "quis nostrud exercitation ullamco"),
(3, "laboris nisi ut aliquip");

insert into pages values 
(1, "Duis aute irure dolor in reprehenderit"),
(2, "in voluptate velit esse cillum"),
(3, "dolore eu fugiat nulla pariatur.");

使其可搜索：

ALTER TABLE articles ADD FULLTEXT(content);
ALTER TABLE events ADD FULLTEXT(content);
ALTER TABLE pages ADD FULLTEXT(content);

使用 UNION 搜索所有这些表：

set @target='dolor';

SELECT * from (
  SELECT 
    'articles' as 'table_name', id, 
    @articles_multiplier * (MATCH(content) AGAINST (@target)) as relevance
    from articles
  UNION
  SELECT 
    'events' as 'table_name', 
    id,
    @events_multiplier * (MATCH(content) AGAINST (@target)) as relevance
    from events
  UNION
  SELECT 
    'pages' as 'table_name', 
    id, 
    @pages_multiplier * (MATCH(content) AGAINST (@target)) as relevance
    from pages
)
as sitewide WHERE relevance > 0;

结果如下：

+------------+----+------------------+
| table_name | id | relevance        |
+------------+----+------------------+
| articles   |  1 | 1.98799377679825 |
| pages      |  3 | 0.65545331108093 |
+------------+----+------------------+

- joelparkerhenderson

---

这太棒了！我有一个非常相似的问题，但我需要相关的匹配项。您能一起看看吗？http://stackoverflow.com/q/9953922/633513 - LordZardeck

Answer 2

请注意，在子查询中使用UNION的优化非常差。一个经常出现的情况是，当您想要使用“LIMIT @page * 10,10”在父查询中对结果进行分页时，MySQL必须获取所有子查询的结果才能评估父查询。

- kien