跨多个表的MySQL全文搜索

I have a series of tables that contain data I want to full text search. I've tried combining the tables with UNION, but the result loses its fulltext index so can't be fulltext searched. I don't think that putting the data into a temp table is the way to go. Is there someway that I can fulltext search these tables efficiently? Thanks in advance!

我有一系列包含我想要全文搜索的数据的表。我已尝试将表与UNION组合，但结果丢失了其全文索引，因此无法进行全文搜索。我不认为将数据放入临时表是可行的方法。有道理我可以全文搜索这些表吗？提前致谢！

UPDATE: my query for fulltext was

更新：我的全文查询是

SELECT ID, Title, Description, Author, MATCH (Title,Tags,Body) AGAINST ("search terms") AS Relevance 
FROM [combination of tables goes here] 
WHERE MATCH (Title,Tags,Body) AGAINST ("search terms")

4 个解决方案

#1

MySQL can't make a fulltext (or any) index accross multiple tables. So using a single index is out.

MySQL无法在多个表中创建全文（或任何）索引。所以使用单个索引就出来了。

As an alternative, you could either:

作为替代方案，您可以：

Use an index on each table, and a join/union as appropriate to retrieve the rows that match your requirements.

在每个表上使用索引，并根据需要使用联接/联合来检索符合您要求的行。
Create an aggregate table to apply the index to.

创建聚合表以应用索引。
Use a tool such as lucene or solr to provide your search index. (If you are going for any sort of scale, this is likely the best option)

使用lucene或solr等工具提供搜索索引。（如果你想要任何规模，这可能是最好的选择）

#2

Add the relevance scores together:

将相关性分数加在一起：

SELECT ID, Title, Description, Author, 
MATCH (Title) AGAINST ("search terms") +
MATCH (Tags) AGAINST ("search terms") +
MATCH (Body) AGAINST ("search terms") 
AS Relevance

#3

simply do:

干得好：

select * from table a where a.col=myval
union
select * from table b where b.col=myval
..

indices are used as they are with a normal select.

索引按正常选择使用。

#4

With your setup being what appears to be a type of message board I assume that you have three tables (correct me if I am wrong):

你的设置似乎是一种留言板，我假设你有三张桌子（如果我错了，请纠正我）：

Message Table (Message_ID, Title, Body, Description, Author)
消息表（Message_ID，标题，正文，描述，作者）
Tag Table (Tag_ID, Name)
标签表（Tag_ID，名称）
Message Tags (Message_ID, Tag_ID)
消息标签（Message_ID，Tag_ID）

Here is how I would do it

我就是这样做的

SELECT Message.Message_ID, Message.Title, Message.Description, Message.Author, 
  IFNULL( 
    MATCH (Name)
    AGAINST (?)
    , 
    IFNULL(
      MATCH (Message.Title)
      AGAINST (?)
      ,
      MATCH (Message.Body)
      AGAINST (?)
    )
  ) AS Relevance 
FROM Message, Tag, Message_Tag 
WHERE Message.Message_ID = Message_Tag.Message_ID AND Message_Tag.Tag_ID = Tag.Tag_ID   
  AND (
    MATCH (Name) 
    AGAINST (?)
  OR 
    MATCH (Message.Title)
    AGAINST (?)
  OR 
    MATCH (Message.Body)
    AGAINST (?)
  )

#1