代码之家 › 专栏 › 技术社区 › AdRock

mysql对多个字段表的全文搜索

union full-text-search mysql

AdRock · 技术社区 · 14 年前

我正在尝试加入多个表并对它们执行全文搜索。

大多数表是不相关的,但是有相似的字段。

我已经有全文搜索工作,但我需要能够从结果创建链接,这是下一步,但我不瘦k它将工作,因为我没有足够的字段来获得足够的信息。

基本上,我想搜索每个表的标题和内容,但我也想搜索我的论坛表,其中是主题和消息。将链接“主题”和“消息”表。

这个查询不需要查询论坛表就可以了,因为我需要能够搜索那些表。

SELECT * FROM (SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM news WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM events WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM blogs WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM honeylands WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM articles WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM info WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT topicid as title, boardid as content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM articles WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT topicid as title, message as content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM info WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)) a ORDER BY score DESC

我应该能够为具有公共字段名(如events.php)的表创建链接。id=1从记录中获取id,但如何对tables topics和messages topic.php执行此操作?boardID=1&topic=2?

这是我的桌子结构创建表 articles ( id int(4)非空自动递增, title varchar(70)不为空默认“”, content 文本不为空, 主键( 身份证件 ) ;

     CREATE TABLE `blogs` (
       `id` int(3) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `events` (
       `id` int(11) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `honeylands` (
       `id` int(4) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `info` (
       `id` int(1) NOT NULL auto_increment,
       `title` varchar(50) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `messages` (
       `messageid` int(6) NOT NULL auto_increment,
       `boardid` int(2) NOT NULL default '0',
       `topicid` int(4) NOT NULL default '0',
       `message` text NOT NULL,
       `author` varchar(255) NOT NULL default '',
       `postdate` datetime default NULL,
       PRIMARY KEY  (`messageid`)
     );

     CREATE TABLE `news` (
       `id` int(4) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );


     CREATE TABLE `topics` (
       `topicid` int(4) NOT NULL auto_increment,
       `boardid` int(2) NOT NULL default '0',
       `topicname` varchar(255) NOT NULL default '',
       `author` varchar(255) NOT NULL default '',
       `counter` int(5) NOT NULL default '0',
       `sticky` char(1) NOT NULL default 'n',
       `locked` char(1) NOT NULL default 'n',
       PRIMARY KEY  (`topicid`)
     );

这是我当前获取所有记录的方式,但无法使用union为topics和messages表添加额外字段

SELECT * FROM (SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM news WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM events WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM blogs WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM honeylands WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM articles WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM info WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT topicname as title,message as content,
    MATCH(topicname, message) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM topics t INNER JOIN messages m ON t.topicid=m.topicid  
    WHERE MATCH(topicname, message) 
    AGAINST('$keywords*' IN BOOLEAN MODE)) a ORDER BY score DESC

1 回复 | 直到 14 年前

Justin Giboney 14 年前

我在一个包含多种内容的网站(电影数据库)上搜索时遇到了这个问题。我希望用户能够进行一次搜索,找到一个演员、电影或角色的名字。

我没有尝试获取一个大的sql语句,而是对每种类型的内容(movie_title、movie_plot、actor_name、character_name等)进行了匹配,并将行的id、内容的类型和匹配的分数粘贴到多维数组中。我通常会将每个内容类型限制在前50个匹配项。

然后我可以根据分数对数组进行排序。然后我将使用id和内容类型查找每个结果所需的信息。

编辑(添加代码)

免责声明:这是旧代码,可能有更有效的方法

$topResults = array();
$topResults[0] = array('nil', 'nil', 0);

$movieFound = 0;
$plotFound = 0;
$actorFound = 0;
$characterFound = 0;

// example of movie title... follow the same procedure for the others
$sql = "SELECT movies.Movie_ID as mid, MATCH (Movie_Title) AGAINST ('$searchstring') AS Score FROM movies, Rating_Movie_Relationships WHERE MATCH (Movie_Title) AGAINST ('$searchstring') AND Front_Image_File IS NOT NULL AND movies.Movie_ID = Rating_Movie_Relationships.Movie_ID $sqlwhere ORDER BY Score DESC LIMIT 0, 20";
$result = @mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    for ($i = 0; $i < count($topResults);$i++){
        if ($row['Score'] > $topResults[$i][2]){
            for ($j = count($topResults); $j > $i; $j--){
                $topResults[$j] = $topResults[$j-1];
            }
            $topResults[$i] = array($row['mid'], 'm', $row['Score'] - $movieWeight);
            break;
        }
    }
    $movieFound = 1;
}

//.... add the other content types here following the movie title example

for ($i = 0; $i < count($topResults); $i++){
    if ($topResults[$i][1] == 'm'){
        if ($countMovies < $limit) {
            $movieTitleDivText .= str_replace('\'','&#39;',createPersonMovieImageLink($topResults[$i][0]));
            $countMovies++;
        }
}