有哪些方法可以改进这个长 mysql 搜索查询?
希望改进搜索查询的 mysql select 语句:
Select * from table WHERE ( user = '$search_query'
OR user LIKE '$search_query %'
OR keyword LIKE '$search_query'
OR tag LIKE '$search_query'
OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'
OR tag LIKE '$search_query%'
OR REPLACE(question, ',' ,'') LIKE '$search_query %'
OR REPLACE(question, ',' ,'') LIKE '% $search_query'
OR REPLACE(question, ',' ,'') LIKE '% $search_query %'
OR REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(question, '\'', ''), ',', ''), '.',''), ':',''), ';',''), '!',''), '?','') LIKE '%$search_query%'
)
它之所以如此崩溃,是因为如果有人搜索“art”,我不希望它也显示“heart”的结果。
我确实需要拥有这些相同的功能,但运行更少的资源。
Looking to improve this mysql select statement for a search query:
Select * from table WHERE ( user = '$search_query'
OR user LIKE '$search_query %'
OR keyword LIKE '$search_query'
OR tag LIKE '$search_query'
OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'
OR tag LIKE '$search_query%'
OR REPLACE(question, ',' ,'') LIKE '$search_query %'
OR REPLACE(question, ',' ,'') LIKE '% $search_query'
OR REPLACE(question, ',' ,'') LIKE '% $search_query %'
OR REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(question, '\'', ''), ',', ''), '.',''), ':',''), ';',''), '!',''), '?','') LIKE '%$search_query%'
)
The reason it's broke down the way it is, is because say if someone searches for "art", I don't want it showing results for "heart" as well.
I really need to have of these same functions but running fewer resources.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(3)
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
如果您的表是
MyISAM
,您可以在其上创建FULLTEXT
索引:此查询将返回
some
和something
但不是很棒
。实际上,第一步(创建索引)不是必需的,但是,它将加快查询速度并允许更复杂的搜索(不限于
BOOLEAN MODE
)和相关性范围。默认情况下,搜索查询的最小长度为
4
个字符,因此将找不到示例中提到的art
。要解决此问题,您必须更改服务器设置中的参数
ft_min_word_len
。If your table is
MyISAM
, you can create aFULLTEXT
index on it:This query will return
some
andsomething
but notawesome
.Actually, the first step (creating the index) is not required, however, it will speed up the queries and allow more complex searches (not limited to
BOOLEAN MODE
) and relevance ranging.By default, minimal length of a search query is
4
characters, soart
mentioned in your example would not be found.To work around this, you will have to change parameter
ft_min_word_len
in server settings.