搜索引擎的SQL查询

My project is based on Questions and Answers (* style).

我的项目基于问题和答案(*风格)。

I need to allow users to search for previously asked questions.

我需要允许用户搜索以前问过的问题。

The Questions table would be like this:

问题表是这样的:

Questions
-------------------------------------------------
id                 questions
-------------------------------------------------
1                  How to cook pasta?
2                  How to Drive a car?
3                  When did Napoleon die?

Now when I'm going to write something to search for, I would write something like this:

当我要写一些东西来搜索的时候，我会这样写:

When did Brazil win the world cup?

Let's say I'm gonna split this String on spaces, into an array of Strings.

假设我要将这个字符串分割成一个字符串数组。

What is the best SELECT SQL query to fetch all questions containing those Strings, ignoring upper case and lower case for each word, and sorting the results by the less mentioned word, why?

什么是最好的SELECT SQL查询，以获取所有包含这些字符串的问题，忽略每个单词的大小写，并根据较少提到的单词对结果进行排序，为什么?

Because there will be so many questions which will contain When,and,will,how,etc.. , but not so many questions which will have Brazil, so Brazil would be like the Key Word.

因为会有很多问题包含在何时、何时、何时、何时、如何等问题中。但巴西不会有那么多问题，所以巴西可能是关键。

I'm using SQL Server 2008.

我使用的是SQL Server 2008。

3 个解决方案

#1

You really don't want to be doing this in raw SQL.

你真的不想在原始SQL中这样做。

I suggest you look into the full-text search options for your database, this might be a good place to start.

我建议您查看数据库的全文搜索选项，这可能是一个很好的起点。

#2

In mysql you have full-text indexes and the match() select function which allow just this, in SQL Server you should use the function Contains() Find more info on http://msdn.microsoft.com/en-us/library/ms142571.aspx

在mysql中，有全文索引和match() select函数，这就允许这样做，在SQL Server中，应该使用Contains()函数在http://msdn.microsoft.com/en-us/library/ms142571.aspx上查找更多信息

#3

Your option is not the best one. Take a look at open source Apache Solr project. http://lucene.apache.org/solr/

你的选择不是最好的。看看开源的Apache Solr项目。http://lucene.apache.org/solr/

Solr is a standalone enterprise search server with a REST-like API. You put documents in it (called "indexing") via XML, JSON, CSV or binary over HTTP. You query it via HTTP GET and receive XML, JSON, CSV or binary results.

Solr是一个独立的企业搜索服务器，具有类似rest的API。您可以通过XML、JSON、CSV或HTTP上的二进制文件将文档放入其中(称为“索引”)。您可以通过HTTP GET和接收XML、JSON、CSV或二进制结果来查询它。

Advanced Full-Text Search Capabilities Optimized for High Volume Web
高级全文搜索功能优化为高容量Web
Traffic Standards Based Open Interfaces - XML, JSON and HTTP
基于流量标准的开放接口——XML、JSON和HTTP
Comprehensive HTML Administration Interfaces Server statistics
全面的HTML管理接口服务器统计信息
exposed over JMX for monitoring Linearly scalable, auto index
通过JMX公开，用于监视线性可伸缩的自动索引
replication, auto failover and recovery Near Real-time indexing
复制、自动故障转移和实时索引附近的恢复
Flexible and Adaptable with XML configuration Extensible Plugin Architecture
灵活适应XML配置可扩展插件体系结构

Take a look at Detailed Features and aspecialy Query section. There all you need for your app.

查看详细的特性和特殊的查询部分。这就是你的应用所需要的一切。

#1