如何实现搜索2个不同的表格数据？ [英] how to implement search for 2 different table data?

问题描述

使用mysql和PHP

我已经使用了MATCH AGAINST子句。

个人桌子。就像我想在商店的餐桌上搜索一样。没问题。

我想要的是能够在单个结果页面中搜索和显示不同表格中的结果。

例如，如果我输入巧克力衣服，我可能会得到4个结果如下：

Shop1结果

/ p>

ShopItem1结果

ShopItem2结果

Shop2结果

当然最相关的结果应该排在第一位。

我有很多问题。设计明智，以及明智的实施方式

<1>我应该改变我的设计吗？我正在考虑创建一个名为搜索结果的单独表格，其中包含来自SHOPS和SHOPPRODUCTS表格的数据。然而这意味着我有一些数据重复。

2）我应该保持我目前的设计吗？如果是这样，那么我怎么能得到搜索结果排序的相关性跨越两个不同的表？我看到rottentomatoes组织他们的搜索结果在不同的组。但是，我们更喜欢搜索结果不受不同类型的限制，特别是当我们有分页时，这将会更加困难地导航UI。

http://www.rottentomatoes.com/search/full_search.php?search=girl a>

或者那实际上是最好的出路？

我希望有人可以给我指导这种事情尤其是如果你有在生成搜索结果的经验，看起来像多个表。

由于需求，我会把表结构放在这里

  CREATE TABLE`shopitems`（
`id` int（10）unsigned NOT NULL auto_increment，
`ShopID` int（10）unsigned NOT NULL，
`ImageID` int（10）unsigned NOT NULL，
`name` varchar（100）NOT NULL，
`description` varchar（255）NOT NULL ，
`pricing` varchar（45）NOT NULL，
`datetime_创建日期时间NOT NULL，
 PRIMARY KEY（`id`）
）ENGINE = MyISAM AUTO_INCREMENT = 31 DEFAULT CHARSET = utf8; 
 
 / *表商店的表格结构* / 
 
如果存在商店 
 
 CREATE TABLE`stores`（
` id` int（11）NOT NULL auto_increment，
`title` varchar（100）default NULL，
`description`文本，
`关键字`文本，
`url` varchar（255）默认值''，
 
`owner_id` varchar（255）默认NULL，
`datetime_created `datetime默认NULL，
`created_by` varchar（255）默认NULL，
`datetime_modified` datetime默认NULL，
`modified_by` varchar（255）默认NULL，
 
`overall_rating_avg` decimal（4,2）默认'0.00'，
 
 
 PRIMARY KEY（`id`），
 FULLTEXT KEY`url`（`url`） ，
 FULLTEXT KEY`TitleDescFullText`（`keywords`，`title`，`description`，`url`）
）ENGINE = MyISAM AUTO_INCREMENT = 3051 DEFAULT CHARSET = utf8; 
  

我打算搜索shopproducts表的描述和名称列。

但你可以看到它还没有实现。

虽然搜寻店铺已经开始运作。

解决方案

<这里有几条游戏规则，你必须牢记这个问题才能解决这个问题。您可能已经知道这些，但明确说明它们可能有助于确认其他读者。

• MySQL中的所有索引都只能引用一列基表。您无法创建索引到多个表的全文索引。


• 您无法为视图定义索引，仅限于基表。
MATCH（）针对全文索引的查询必须与索引中声明的顺序匹配全文索引中的所有列。





我将创建第三个表来存储要索引的内容。不需要冗余存储这些内容 - 仅将其存储在第三个表中。这就借用了面向对象设计中的通用超类的概念（只要我们可以将它应用到RDBMS设计中）。



  CREATE TABLE Searchable（
`id` SERIAL PRIMARY KEY，
`title` varchar（100）default NULL，
`description` text，
`keywords` text，
`url` varchar（255）default''，
 FULLTEXT KEY`TitleDescFullText`（`keywords`，`title`，`description`，`url`）
）ENGINE = MyISAM DEFAULT CHARSET = UTF8; 
 
 CREATE TABLE`shopitems`（
`id` INT UNSIGNED NOT NULL，
`ShopID` INT UNSIGNED NOT NULL，
`ImageID` INT UNSIGNED NOT NULL， 
`pricing` varchar（45）NOT NULL，
`datetime_created` datetime NOT NULL，
 PRIMARY KEY（`id`），
 FOREIGN KEY（`id`）REFERENCES可搜索（`id`）
）ENGINE = MyISAM DEFAULT CHARSET = utf8; 
 
 CREATE TABLE`stores`（
`id` INT UNSIGNED NOT NULL，
`owner_id` varchar（255）default NULL，
`datetime_created` datetime default NULL ，
`created_by` varchar（255）default NULL，
`datetime_modified` datetime default NULL，$ b $``modified_by` varchar（255）default NULL，
`overall_rating_avg` decimal（4 ，2）默认的'0.00'，
 PRIMARY KEY（`id`），
 FOREIGN KEY（`id`）REFERENCES可搜索的（`id`）
）ENGINE = MyISAM DEFAULT CHARSET = utf8 ; 
  

请注意，带有自动递增键的唯一表现在 Searchable 。表商店和 shopitems 使用具有兼容数据类型的键，但不能自动增量。因此，您必须在 Searchable 中创建一行以生成 id 值，然后才能在商店或 shopitems 。





我已添加<$即使MyISAM会默默地忽略这些约束（并且您已经知道您必须使用MyISAM来支持全文索引）。

$ b> c $ c> FOREIGN KEY 声明用于说明目的。
$ b

现在您可以搜索商店和 shopitems 的文字内容查询，使用单个全文索引：

  SELECT S. *，sh。*，si。*，
 MATCH （关键字，标题，描述，网址）AGAINST（'dummy'）As得分
从可搜索的S 
左外部连接商店sh ON（S.id = sh.id）
 LEFT OUTER JOIN shopitems si ON（S.id = si.id）
 WHERE MATCH（关键字，标题，描述，网址）AGAINST（'dummy'）
 ORDER BY得分DESC; 
  

当然，对于中的给定行可搜索只有一张表应该匹配，无论是商店还是商店，这些表格都有不同的列。因此，结果中 sh。* 或 si。* 将为NULL。这取决于你在应用程序中格式化输出。





---





其他一些答案建议使用 Sphinx搜索。这是另一项补充MySQL的技术，并增加了更复杂的全文搜索功能。它对查询有很好的表现，所以有些人对它很感兴趣。





但是创建索引，特别是增加索引是很昂贵的。事实上，更新Sphinx搜索索引成本太高，以至于推荐的解决方案是为较旧的归档数据创建一个索引，并为最近更可能更新的数据创建另一个较小的索引。然后每个搜索都必须针对两个单独的索引运行两个查询。如果你的数据本身并不适用于旧数据不变的模式，那么你可能无法利用这个技巧。



---





重新评论：以下是的摘录狮身人面像搜索文档关于实时更新索引：






常见的情况是
总数据集也是如此经常从头开始重新编制索引b $ b，但是
的新记录数量相当小。
例如：一个论坛的论坛有一个1,000,000
的存档帖子，但每天只有1,000个新的
帖子。





在这种情况下，活的（几乎真正的
时间）指数更新可能是
使用所谓的
主+三角洲计划实施。




这个想法是因为更新Sphinx搜索索引代价高昂，他们的解决方案是让索引尽可能小。因此，只有最近的论坛帖子（在他们的例子中），而较大的存档论坛帖子的历史从未改变，因此您为该收藏集建立第二个更大的索引一次。当然，如果你想进行搜索，你必须查询两个索引。





定期地说，每周一次，最近的论坛消息将被视为归档并且您必须将最近帖子的当前索引合并到归档索引，然后启动较小索引。他们确实表明合并两个Sphinx搜索索引比更新数据后重新索引更有效。





但我的观点是，并非每个数据集都自然地属于具有永不改变的归档数据集的模式，而不是经常更新的最近数据。



以您的数据库为例：您有商店和商店。你怎么能把它们分离成永不改变的行，而不是新行？应允许目录中的任何商店或产品更新其描述。但是，由于每次进行更改都需要重新构建整个Sphinx搜索索引，因此这将成为一项非常昂贵的操作。也许你会排队修改并在批处理中应用它们，每周重建一次索引。但试试向商店卖家解释为什么他们的店铺描述的细微变化直到周日晚上才会生效。




Using mysql and PHP

I am using MATCH AGAINST clauses already.

It is working fine against individual tables. Like if i want to search in shops table. No problem.

What i want is to be able to search and DISPLAY results from different tables in a single result page.

Eg if i type "chocolate clothes"

i may get 4 results as follows:

Shop1 result

ShopItem1 result

ShopItem2 result

Shop2 result

and of course the most relevant results should be ranked first.

i have quite a few questions. design wise as well as implementation wise

1) should i change my design? i am thinking of having a separate table called search results that will contain data from both SHOPS and SHOPPRODUCTS table. however that means i have some data duplication.

2) should i keep my current design? if so, then how on earth can i get the search results sorted by relevancy across 2 different tables?

i saw that rottentomatoes organised their search results in different groups. however, we prefer the search results not to be restricted by different types especially when we have paging that is going to be even more difficult to navigate UI wise.

http://www.rottentomatoes.com/search/full_search.php?search=girl

OR that is actually the best way out?

I hope that someone can give me guidance on this kind of thing esp if you have experience in generating search results across what would seem like multiple tables.

since by demand, i will put the table structures here

CREATE TABLE `shopitems` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ShopID` int(10) unsigned NOT NULL,
  `ImageID` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) NOT NULL,
  `pricing` varchar(45) NOT NULL,
  `datetime_created` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Table structure for table `shops` */

DROP TABLE IF EXISTS `shops`;

CREATE TABLE `shops` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  `description` text,
  `keywords` text,
  `url` varchar(255) default '',

  `owner_id` varchar(255) default NULL,
  `datetime_created` datetime default NULL,
  `created_by` varchar(255) default NULL,
  `datetime_modified` datetime default NULL,
  `modified_by` varchar(255) default NULL,

  `overall_rating_avg` decimal(4,2) default '0.00',


  PRIMARY KEY  (`id`),
  FULLTEXT KEY `url` (`url`),
  FULLTEXT KEY `TitleDescFullText` (`keywords`,`title`,`description`,`url`)
) ENGINE=MyISAM AUTO_INCREMENT=3051 DEFAULT CHARSET=utf8;

i intend to search through the description and the name columns of the shopproducts table.

but as you can see it has not been implemented yet.

although the search for the shops is already up and running.

解决方案

Here are a few "rules of the game" that you must keep in mind for solving this problem. You probably know these already, but stating them clearly may help confirm for other readers.

• All indexes in MySQL can reference only columns in a single base table. You can't make a fulltext index that indexes across multiple tables.
• You can't define indexes for views, only base tables.
• A MATCH() query against a fulltext index must match against all the columns in the fulltext index, in the order declared in the index.

I would create a third table to store the content you want to index. No need to store this content redundantly -- store it solely in the third table. This borrows a concept of a "common superclass" from object-oriented design (insofar as we can apply it to RDBMS design).

CREATE TABLE Searchable (
  `id` SERIAL PRIMARY KEY,
  `title` varchar(100) default NULL,
  `description` text,
  `keywords` text,
  `url` varchar(255) default '',
  FULLTEXT KEY `TitleDescFullText` (`keywords`,`title`,`description`,`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `shopitems` (
  `id` INT UNSIGNED NOT NULL,
  `ShopID` INT UNSIGNED NOT NULL,
  `ImageID` INT UNSIGNED NOT NULL,
  `pricing` varchar(45) NOT NULL,
  `datetime_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id`) REFERENCES Searchable (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `shops` (
  `id` INT UNSIGNED NOT NULL,
  `owner_id` varchar(255) default NULL,
  `datetime_created` datetime default NULL,
  `created_by` varchar(255) default NULL,
  `datetime_modified` datetime default NULL,
  `modified_by` varchar(255) default NULL,
  `overall_rating_avg` decimal(4,2) default '0.00',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id`) REFERENCES Searchable (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Notice the only table with an auto-increment key is now Searchable. The tables shops and shopitems use a key with a compatible data type, but not auto-increment. So you must create a row in Searchable to generate the id value, before you can create the corresponding row in either shops or shopitems.

I've added FOREIGN KEY declarations for illustration purposes, even though MyISAM will silently ignore these constraints (and you already know that you must use MyISAM to have support for fulltext indexing).

Now you can search the textual content of both shops and shopitems in a single query, using a single fulltext index:

SELECT S.*, sh.*, si.*,
  MATCH(keywords, title, description, url) AGAINST('dummy') As score
FROM Searchable S
LEFT OUTER JOIN shops sh ON (S.id = sh.id)
LEFT OUTER JOIN shopitems si ON (S.id = si.id)
WHERE MATCH(keywords, title, description, url) AGAINST('dummy')
ORDER BY score DESC;

Of course, for a given row in Searchable only one table should match, either shops or shopitems, and these tables have different columns. So either sh.* or si.* will be NULL in the result. It's up to you to format the output in your application.

---

A couple of other answers have suggested using Sphinx Search. This is another technology that complements MySQL and adds more sophisticated full-text search capability. It has great performance for queries, so some people have gotten pretty enchanted with it.

But creating indexes and especially adding to an index incrementally is expensive. In fact, updating a Sphinx Search index is so costly that the recommended solution is to create one index for older, archived data, and another smaller index for recent data that is more likely to be updated. Then every search has to run two queries, against the two separate indexes. And if your data doesn't naturally lend itself to the pattern of older data being unchanging, then you may not be able to take advantage of this trick anyway.

---

Re your comment: Here's an excerpt from the Sphinx Search documentation about live updates to an index:

There's a frequent situation when the total dataset is too big to be reindexed from scratch often, but the amount of new records is rather small. Example: a forum with a 1,000,000 archived posts, but only 1,000 new posts per day.

In this case, "live" (almost real time) index updates could be implemented using so called "main+delta" scheme.

The idea is that since it's costly to update a Sphinx Search index, their solution is to make the index you update as small as possible. So that only the most recent forum posts (in their example), whereas the larger history of archived forum posts never changes, so you build a second, larger index for that collection once. Of course if you want to do a search, you have to query both indexes.

Periodically, say once a week, the "recent" forum messages would become considered "archived" and you'd have to merge the current index for recent posts to the archived index, and start the smaller index over. They do make the point that merging two Sphinx Search indexes is more efficient than reindexing after an update to the data.

But my point is that not every data set naturally falls into the pattern of having an archived set of data that never changes, versus recent data that updates frequently.

Take your database for example: You have shops and shopitems. How can you separate these into rows that never change, versus new rows? Any shops or products in the catalog should be permitted to update their description. But since that'd require rebuilding the entire Sphinx Search index every time you make a change, it becomes a very expensive operation. Perhaps you'd queue up changes and apply them in a batch, rebuilding the index once a week. But try explaining to the shop vendors why a minor change to their shop description won't take effect until Sunday night.

这篇关于如何实现搜索2个不同的表格数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！