跨多个表的Mysql全文搜索相关性 [英] Mysql fulltext search relevance across multiple tables

问题描述

我的任务是创建一个网站搜索功能。搜索需要查看文章，事件和页面内容。

我之前在MySQL中使用过MATCH（）/ AGAINST（），并知道如何获得结果，但据我所知，搜索的相关性是唯一的（内容，行数等），文章表结果的相关性不会匹配事件表结果的相关性。

是否有统一相关性的方法，以便所有三个表的结果都具有可比性？ 是的，您可以使用搜索引擎（如Apache Lucene和Solr）将它们很好地结合在一起。

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

如果您只需要在MySQL中执行此操作，则可以使用联盟。您可能想要取消任何与零相关的结果。

您需要决定如何影响相关性，具体取决于哪个表匹配。例如，假设你希望文章是最重要的，事件是中等重要的，而页面是最不重要的。您可以使用这样的乘数：

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

下面是一个可用的示例，可以演示其中的一些技巧：

创建示例数据：

  create database d; 
使用d; 
 
创建表文章（id int主键，内容文本）ENGINE = MYISAM; 
创建表事件（id int主键，内容文本）ENGINE = MYISAM; 
创建表页面（id int主键，内容文本）ENGINE = MYISAM; 
 
插入物品价值
（1，Lorem ipsum dolor sit amet），
（2，consectetur adipisicing elit），
（3， sed do eiusmod tempor incididunt）; 
 
插入事件值
（1，Ut enim ad minim veniam），
（2，quis nostrud exercitation ullamco），
（3， laboris nisi ut aliquip）; 
 
插入页面值
（1，duis aute irure dolor in reprehenderit），
（2，in voluptate velit esse cillum），
 3，dolore eu fugiat nulla pariatur。）; 
  

使其可搜索：

  ALTER TABLE文章ADD FULLTEXT（content）; 
 ALTER TABLE事件ADD FULLTEXT（content）; 
 ALTER TABLE页面添加FULLTEXT（内容）; 
  

使用UNION搜索所有这些表：

  set @ target ='dolor'; 
 
 SELECT * from（
 SELECT 
'articles'as'table_name'，id，
 @articles_multiplier *（MATCH（content）AGAINST（@target））as相关性
从文章
 UNION 
选择
'events'作为'table_name'，
 id，
 @events_multiplier *（MATCH（content）AGAINST（@ target））作为相关性
从事件
 UNION 
 SELECT 
'pages'作为'table_name'，
 id，
 @pages_multiplier *（MATCH ）AGAINST（@target））作为相关性
从网页
）
作为站点范围在哪里相关性> 0; 
  

结果：

  + ------------ + ---- + ------------------ + 
 | table_name | id |相关性| 
 + ------------ + ---- + ------------------ + 
 |文章| 1 | 1.98799377679825 | 
 |页面| 3 | 0.65545331108093 | 
 + ------------ + ---- + ------------------ + 
  

I have been tasked with creating a site wide search feature. The search needs to look at articles, events and page content

I've used MATCH()/AGAINST() in MySQL before and know how to get the relevance of a result but as far as I know the relevance is unique to the search (contents, number of rows etc) the relevance of results from the articles table wont match the relevance of results from the events table.

Is there anyway to unify the relevance so that results from all three tables have a comparable relevance?

解决方案

Yes, you can unify them very well using a search engine such as Apache Lucene and Solr.

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

If you need to do it only in MySQL, you can do this with a UNION. You'll probably want to suppress any zero-relevant results.

You'll need to decide how you want to affect the relevance depending on which table matches.

For example, suppose you want articles to be most important, events to be medium important, and pages to be least important. You can use multipliers like this:

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

Here's a working example you can try that demonstrates some of these techniques:

Create sample data:

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

Make it searchable:

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

Use a UNION to search all these tables:

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;

The result:

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

这篇关于跨多个表的Mysql全文搜索相关性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！