MySQL全文搜索具有不同字段的多个表 [英] MySQL fulltext search on multiple tables with different fields
问题描述
我试图加入多个表格并对它们执行全文搜索。
大部分表格都是不相关的，但有类似的字段。
我有全文搜索的工作，但我需要能够从结果中创建链接，这是下一步，但我不瘦K它会工作，因为我没有得到足够的信息enoygh领域。
基本上我想搜索标题和每个表的内容，但我也想搜索我的论坛表，这是主题和消息。主题和消息表被链接在一起。
这个查询可以做到这一点，而不用查询论坛表格bu ti是否需要搜索这些表格。 b
$ b
SELECT * FROM（SELECT title，content，
MATCH（title，content）AGAINST（'folk *'IN BOOLEAN MODE）
作为分数来自新闻WHERE MATCH（标题，内容）
AGAINST（'民间*'IN BOOLEAN模式）
UNION ALL
SELECT title，content，
MATCH（title，内容）反对（'民间*'在布尔模式）
作为分数来自事件匹配（标题，内容）
反对（'民间*'在布尔模式中）
联合全部
选择标题，内容，
MATCH（标题，内容）AGAINST（'民间*'IN BOOLEAN模式）
作为分数FROM博客WHERE MATCH（标题，内容）
AGAINST（'folk * 'BOOLEAN模式）
UNION ALL
SELECT title，content，
MATCH（标题，内容）AGAINST（'民间*'IN BOOLEAN模式）
作为分数FROM ylands WHERE MATCH（标题，内容）
AGAINST（'民间*'BOOLEAN模式）
联合全部
选择标题，内容，
MATCH（标题，内容）民间*'IN BOOLEAN模式）
作为分数FROM文章WHERE MATCH（标题，内容）
AGAINST（'民间*'IN BOOLEAN模式）
UNION ALL
SELECT title，content ，
MATCH（标题，内容）AGAINST（'民间*'IN BOOLEAN模式）
作为分数FROM信息WHERE MATCH（标题，内容）
反对（民间*
UNION ALL
选择topicid作为标题，boardid作为内容，
MATCH（标题，内容）AGAINST（'民间*'IN BOOLEAN模式）
作为分数FROM文章WHERE MATCH标题，内容）
AGAINST（'民间*'BOOLEAN模式）
UNION ALL
选择topicid作为标题，作为内容的消息，
MATCH（标题，内容）AGAINST（'民间*'在BOOLEAN模式）
作为分数FROM信息匹配（标题，内容）
反对（'民间*'IN BOOLEAN MODE））a ORDER BY score DESC
我应该能够为有通用字段名称的表，比如events.php？id = 1从记录中获取id，但是我如何为表格主题和消息topic.php执行此操作？boardid = 1& topic = 2？
下面是我的表结构
CREATE TABLE articles （
id int（4）NOT NULL auto_increment，
title varchar（70）NOT NULL默认值''，
content text NOT NULL，
PRIMARY KEY（ id ）
）;
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默认值'0'，
'topicid` int（4）NOT NULL默认'0'，
'message`文本NOT NULL，
`author` varchar（255）NOT NULL默认值''，
`postdate` datetime默认为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`）
）;
$ b CREATE TABLE`topics`（
`topicid` int（4）NOT NULL auto_increment，
`boardid` int（2）NOT NULL默认值'0' ，
`topicname` varchar（255）NOT NULL默认值''，
`author` varchar（255）NOT NULL默认值''，
`counter` int（5）NOT NULL default' 0'，
'sticky` char（1）NOT NULL默认值'n'，
'locked` char（1）NOT NULL默认值'n'，
PRIMARY KEY（`topicid`）
）;
这是我目前获取所有记录的方式，但无法为主题添加额外字段和使用UNION的消息表
SELECT * FROM（SELECT title，content，
MATCH（title，content） （'$关键字*'在BOOLEAN模式）
作为分数来自新闻WHERE MATCH（标题，内容）
AGAINST（'$关键字*'BOOLEAN模式）
联合全部
SELECT title，content，
MATCH（标题，内容）AGAINST（'$关键字*'在BOOLEAN模式中）
作为分数FROM事件WHERE MATCH（标题，内容）
AGAINST *'在BOOLEAN模式中）
UNION ALL
SELECT title，content，
MATCH（标题，内容）AGAINST（'BOOLEAN模式下的'$关键字*'）
作为分数FROM博客（匹配标题，内容）
AGAINST（'$关键字*'在布尔模式中）
联合全部
SELECT title，content，
MATCH $关键字*'IN BOOLEAN MODE）
作为分数FROM honeylands WHERE MATCH（标题，内容）
AGAINST（'$关键字*'在布尔模式中）
联合全部
SELECT title，content，
MATCH（标题，内容）AGAINST（'$关键字*'在BOOLEAN模式）
作为分数FROM文章WHERE MATCH（标题，内容）
AGAINST（'$ keywords *'BOOLEAN模式）
UNION ALL
SELECT title，content，
MATCH（标题，内容）AGAINST（'$关键字*'在BOOLEAN模式）
作为分数FROM信息WHERE MATCH（标题，内容）
AGAINST（'$关键字*'在BOOLEAN模式中）
UNION ALL
SELECT topicname as title，message as content，
MATCH（topicname，message）AGAINST（'$ keywords *' IN BOOLEAN模式）
作为分数FROM主题t INNER JOIN消息m ON t.topicid = m.topicid
WHERE MATCH（topicname，message）
AGAINST（'$ keywords *'IN BOOLEAN MODE ））a ORDER BY得分DESC
我遇到了这个问题，在具有多种内容（电影数据库）的网站上构建搜索。我希望用户能够进行一次搜索并找到演员，电影或角色名称。
我没有试图获得一个大的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;
//电影标题示例...按照相同的程序为其他人
$ 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 [ （$ j = count（$ topResults）; $ j> $ i; $ j - ）{
$（$ j $）$&$ $ topResults [$ i] [2]）{ topResults [$ j] = $ topResults [$ j-1];
}
$ topResults [$ i] = array（$ row ['mid']，'m'，$ row ['Score'] - $ movieWeight）;
休息;
}
}
$ movieFound = 1;
}
// ....在电影标题示例中添加其他内容类型示例
for（$ i = 0; $ i
if（$ countMovies< $ limit）{
$ movieTitleDivText。= str_replace（'\''，'&＃39;'，createPersonMovieImageLink（$ topResults [$ i] [0]））;
$ countMovies ++;
}
}
I am trying to join multiple tables and perform a full text search on them.
Most of the tables are unrelated but have similar fields.
I have had the fulltext searches working but i need to be able to create links from the results which is the next step but i don't thin k it will work becuase i haven't got enoygh fields to get enough info.
Basically I want to search for the title and the content of each table but i also want to search my forum tables which are topics and messages. The topics and messages tables are linked.
This query will do the trick without querying the forum tables bu t i need to be able to search those tables.
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
I should be able to create links for the tables that have commom field names such as events.php?id=1 getting the id from the record, but how would i do this for the tables topics and messages topic.php?boardid=1&topic=2 ?
Here is my table structure
CREATE TABLE articles (
id int(4) NOT NULL auto_increment,
title varchar(70) NOT NULL default '',
content text NOT NULL,
PRIMARY KEY (id)
);
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`)
);
This is how i currently get all the records but there is no way of adding extra fields for the topics and messages tables using a UNION
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
I came across this issue building a search on a website with multiple types of content (movie database). I wanted the user to be able to do one search and find an actor, movie, or character name.
Instead of trying to get one big SQL statement, I did a match for each type of content (movie_title, movie_plot, actor_name, character_name, etc.) and stuck the id of the row, the type of content, and the score of the match into a multidimensional array. I would usually limit each content type to the top 50 matches.
I was then able to sort the array based on score. Then I would use the id and content type to look up the information I needed for each result.
EDIT (adding code)
Disclaimer: This is old code, and there is probably more efficient ways of doing it
$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('\'',''',createPersonMovieImageLink($topResults[$i][0]));
$countMovies++;
}
}
这篇关于MySQL全文搜索具有不同字段的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
