在mySQL数据库中搜索短语-SearchingforphrasesinamySQLdatabase

2  

Maybe use the fulltext features of mysql?

也许使用mysql的全文功能?

http://devzone.zend.com/26/using-mysql-full-text-searching/

This example uses Zend framework, but the SQL is the same.

此示例使用Zend框架,但SQL是相同的。

There are some open source options that will setup a separate fulltext search server if your indexing/ranking needs are more complex.

如果您的索引/排名需求更复杂,有一些开源选项将设置单独的全文搜索服务器。

Sphinx http://sphinxsearch.com/

Xapian http://xapian.org/

2  

You're going to want to pull a FULLTEXT index on the fields to make it faster, but you can do

你想要在字段上提取一个FULLTEXT索引以使其更快,但你可以做到

SELECT *
FROM Table
WHERE MATCH ('column_name') AGAINST ('here is the phrase')

0  

If you're going to be searching the text often, then full text would be your best bet. Also, I assume that your column is varchar (not TEXT) or else LIKE would not work.

如果您要经常搜索文本,那么全文将是您最好的选择。另外,我假设你的列是varchar(不是TEXT),否则LIKE不起作用。

0  

Here you go. This will take all of the text user entered, get separate words and look for any of those words (whole or part - pick which where clause you wnt)

干得好。这将取得用户输入的所有文本,获取单独的单词并查找任何单词(全部或部分 - 选择您所在的where where子句)

Let's assume that $user_input is set from $_POST

我们假设$ user_input是从$ _POST设置的

$user_input = $_POST;
// try this one for sample data $user_input = "hi hello wh'at els;e is new select 1 from dual;";
$user_input_array = array_map('mysql_real_escape_string', explode(' ',$user_input));
$where_clause = "YOURCOL = '".implode("' OR YOURCOL = '", $user_input_array)."'";

echo $where_clause.'
';

// if you want to use IN 
$where_clause = " YOURCOL IN ('".implode("', '", $user_input_array)."')";
echo $where_clause.'
';

// if you want to use LIKE

$where_clause = "YOURCOL LIKE '%".implode("%' OR YOURCOL LIKE '%", $user_input_array)."%'";
echo $where_clause.'
';

Results from above are:

以上结果是:

YOURCOL = 'hi' OR YOURCOL = 'hello' OR YOURCOL = 'wh\'at' OR YOURCOL = 'els;e' OR YOURCOL = 'is' OR YOURCOL = 'new' OR YOURCOL = 'select' OR YOURCOL = '1' OR YOURCOL = 'from' OR YOURCOL = 'dual;'

YOURCOL ='hi'或YOURCOL ='hello'或YOURCOL ='wh \'在'OR YOURCOL ='els; e'或YOURCOL ='是'或YOURCOL ='new'或YOURCOL ='select'或YOURCOL =' 1'或YOURCOL ='来自'OR YOURCOL ='dual;'

YOURCOL IN ('hi', 'hello', 'wh\'at', 'els;e', 'is', 'new', 'select', '1', 'from', 'dual;')

YOURCOL IN('hi','hello','wh''at','els; e','is','new','select','1','from','dual;')

YOURCOL LIKE '%hi%' OR YOURCOL LIKE '%hello%' OR YOURCOL LIKE '%wh\'at%' OR YOURCOL LIKE '%els;e%' OR YOURCOL LIKE '%is%' OR YOURCOL LIKE '%new%' OR YOURCOL LIKE '%select%' OR YOURCOL LIKE '%1%' OR YOURCOL LIKE '%from%' OR YOURCOL LIKE '%dual;%'

YOURCOL喜欢'%hi%'或YOURCOL喜欢'%hello%'或YOURCOL喜欢'%wh''%'或YOURCOL喜欢'%els; e%'或YOURCOL喜欢'%is%'或YOURCOL LIKE'%new %'或YOURCOL LIKE'%select%'或YOURCOL LIKE'%1%'或YOURCOL类似'%from%'或YOURCOL LIKE'%dual;%'

0  

In stock MySQL you could use

有货MySQL你可以使用

SELECT * from  WHERE phrase_column LIKE '%my search phrase%' 

but it would be a VERY slow query as it will unable to use any index. And it doesn't supports any morphology.

但它将是一个非常慢的查询,因为它将无法使用任何索引。并且它不支持任何形态。

If you have big enough phrase set you could hire phrase operator from extended query language in Sphinx to perform phrase search as described in http://sphinxsearch.com/docs/current.html#extended-syntax

如果您有足够大的短语集,您可以在Sphinx中使用扩展查询语言中的短语运算符来执行短语搜索,如http://sphinxsearch.com/docs/current.html#extended-syntax中所述

Basically you could install Sphinx and create simple on-disk index which contains phrases from MySQL table you've mentioned. Later you could boost required phrases by adding rating to each document you index using additional integer column.

基本上你可以安装Sphinx并创建简单的磁盘索引,其中包含你提到的MySQL表中的短语。稍后,您可以通过使用其他整数列为您索引的每个文档添加评级来增强所需的短语。

You could find quick Sphinx configuration guide at http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/ After firing up Sphinx you could connect to Sphinx and using phrase search:

您可以在http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/找到快速的Sphinx配置指南。启动Sphinx后,您可以连接到Sphinx并使用短语搜索:

SELECT * FROM  WHERE MATCH('"my search phase"'); 

Please note both single (string) and double (phrase operator) parentheses.

请注意单(字符串)和双(短语运算符)括号。

Sphinx will return you phrase IDs so you can retrieve them from MySQL in very fast way (it is usually select using primary key)

Sphinx将返回短语ID,以便您可以非常快速地从MySQL中检索它们(通常使用主键选择)

Another place to look is http://lucene.apache.org/ which is another great full-text search engine.

另一个值得关注的地方是http://lucene.apache.org/,这是另一个很棒的全文搜索引擎。