MySQL联合搜索（多个表）保持相关性

Question

我有多个表格，拥有不同的列，相应地设置了全文索引。如果我只想搜索一个表格，那么排序数据按相关性得分即可，没有问题。然而，我有多个表格，我使用以下的UNION来进行这些SQL SELECT语句：

$this->dbi->prepare("
    SELECT `id`,'".PRE."pages' as `table`, MATCH(`title`,`content`) AGAINST (?) AS `score` FROM `".PRE."pages` WHERE MATCH(`title`,`content`) AGAINST (?)
    UNION SELECT `id`,'".PRE."news' as `table`, MATCH(`title`,`content`) AGAINST (?) AS `score` FROM `".PRE."news` WHERE MATCH(`title`,`content`) AGAINST (?)
    UNION SELECT `id`,'".PRE."comments' as `table`, MATCH(`title`, `content`) AGAINST (?) AS `score` FROM `".PRE."comments` WHERE MATCH(`title`, `content`) AGAINST(?)
    UNION SELECT `id`,'".PRE."auction_auto' as `table`, MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST (?) AS `score` FROM `".PRE."auction_auto` WHERE MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST (?)
;")->...

我该如何让这么多表格变得相关？现在无论我选择哪个表格，分数数据都不会按照顺序显示。

谢谢。 对于作者提供的可能解决方案，请参见此链接。

- Davit

Answer 1

两个选择：

1. 创建一个视图，然后通过从视图中选择进行排序。
2. 将查询放在from（嵌套的with as表子查询）中，并进行排序。请参见: http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html

这是一个使用我的数据的示例（仅供参考）：

select * from
(
  (select * from products where products_id >10)
  UNION
  (select * from products where products_id <= 10)
)
as SOURCE
order by products_id

所以，以您自己的示例为例，它会类似于：

    $this->dbi->prepare("
select * from 
(
        SELECT `id`,'".PRE."pages' as `table`, MATCH(`title`,`content`) AGAINST (?) AS `score` FROM `".PRE."pages` WHERE MATCH(`title`,`content`) AGAINST (?)
        UNION SELECT `id`,'".PRE."news' as `table`, MATCH(`title`,`content`) AGAINST (?) AS `score` FROM `".PRE."news` WHERE MATCH(`title`,`content`) AGAINST (?)
        UNION SELECT `id`,'".PRE."comments' as `table`, MATCH(`title`, `content`) AGAINST (?) AS `score` FROM `".PRE."comments` WHERE MATCH(`title`, `content`) AGAINST(?)
        UNION SELECT `id`,'".PRE."auction_auto' as `table`, MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST (?) AS `score` FROM `".PRE."auction_auto` WHERE MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST (?)
) as allTables order by `id`;")

- Menelaos