How to implement a great search within a mysqldb - within a table if i search with '...LIke %bla%....' not all entrys would be found - if bla within a word for example. a search with soundex would be great to - but if i read the manual i must create an soundex-index to search for soundex-values?
如何在mysqldb中实现一个伟大的搜索 - 如果我使用'... LIke%bla%....搜索,在一个表内,并不是所有的命令都会被找到 - 例如,如果bla在一个单词中。使用soundex进行搜索会很棒 - 但如果我阅读手册,我必须创建一个soundex-index来搜索soundex值?
So the question whats the "best practice" to write a good db-search vor a keyword within a simple column "title" or someting else.
因此,问题是“最佳实践”是在简单的“标题”列或其他内容中编写一个好的数据库搜索关键字。
bye
2 个解决方案
#1
2
For text search you have two options besides using a LIKE clause - FULLTEXT index (which means using MyISAM) or using specific indexing engines such as Lucene or Sphinx.
对于文本搜索,除了使用LIKE子句外,还有两个选项 - FULLTEXT索引(表示使用MyISAM)或使用特定的索引引擎,如Lucene或Sphinx。
Personally I'd recommend using Sphinx, it has excellent integration with both MySQL and PHP and is very fast for text search (and other queries as well).
我个人推荐使用Sphinx,它与MySQL和PHP都有很好的集成,并且对于文本搜索(以及其他查询)也非常快。
#2
2
If you're not up to installing and using Sphinx, then you can try using some of MySQL's built in full text searching features like "MATCH"
如果您不打算安装和使用Sphinx,那么您可以尝试使用MySQL的一些内置全文搜索功能,如“MATCH”
This lets you do full text searching and even ranks results with a relevance score. You can write queries like:
这使您可以进行全文搜索,甚至可以使用相关性分数对结果进行排名。您可以编写如下查询:
SELECT id, col1, col2, MATCH(col1, col2) AGAINST('some text' IN NATURAL LANGUAGE MODE) AS relevance FROM table1;
You can even use query expansion to pick up on other closely related terms. For example, if the user searches for "database" and the top most relevant results contain "mysql", then the mysql will also search for "mysql" because it has determined that the "mysql" term is closely related to the "database" term the user searched for.
您甚至可以使用查询扩展来获取其他密切相关的术语。例如,如果用户搜索“数据库”并且最相关的结果包含“mysql”,那么mysql也将搜索“mysql”,因为它已确定“mysql”术语与“数据库”密切相关用户搜索的术语。
http://dev.mysql.com/doc/refman/5.1/en/fulltext-query-expansion.html
#1
2
For text search you have two options besides using a LIKE clause - FULLTEXT index (which means using MyISAM) or using specific indexing engines such as Lucene or Sphinx.
对于文本搜索,除了使用LIKE子句外,还有两个选项 - FULLTEXT索引(表示使用MyISAM)或使用特定的索引引擎,如Lucene或Sphinx。
Personally I'd recommend using Sphinx, it has excellent integration with both MySQL and PHP and is very fast for text search (and other queries as well).
我个人推荐使用Sphinx,它与MySQL和PHP都有很好的集成,并且对于文本搜索(以及其他查询)也非常快。
#2
2
If you're not up to installing and using Sphinx, then you can try using some of MySQL's built in full text searching features like "MATCH"
如果您不打算安装和使用Sphinx,那么您可以尝试使用MySQL的一些内置全文搜索功能,如“MATCH”
This lets you do full text searching and even ranks results with a relevance score. You can write queries like:
这使您可以进行全文搜索,甚至可以使用相关性分数对结果进行排名。您可以编写如下查询:
SELECT id, col1, col2, MATCH(col1, col2) AGAINST('some text' IN NATURAL LANGUAGE MODE) AS relevance FROM table1;
You can even use query expansion to pick up on other closely related terms. For example, if the user searches for "database" and the top most relevant results contain "mysql", then the mysql will also search for "mysql" because it has determined that the "mysql" term is closely related to the "database" term the user searched for.
您甚至可以使用查询扩展来获取其他密切相关的术语。例如,如果用户搜索“数据库”并且最相关的结果包含“mysql”,那么mysql也将搜索“mysql”,因为它已确定“mysql”术语与“数据库”密切相关用户搜索的术语。
http://dev.mysql.com/doc/refman/5.1/en/fulltext-query-expansion.html