在SQL中是否可以将连字符和空格相同? [英] Is it possible to treat hyphens and spaces the same in SQL?

问题描述

因此，我正在为社交网站开发搜索功能，它可以搜索用户帖子.一些用户喜欢使用连字符而不是空格，但是我希望搜索功能在结果中同时查找连字符和空格.

So I'm working on a search function for a social networking site, and it searches user posts. Some users like to put hyphens instead of spaces, but I would like for the search function look for both hyphens and spaces in a result.

例如，如果他们有一个名为"SQL-IS AWESOME"的帖子，而我搜索"SQL IS AWESOME"，我仍然可以找到该帖子吗?我尝试使用2条sql查询，其中1条用于原始搜索查询，另一条经修改以将所有空格更改为连字符.

For example, if they have a post named "SQL-IS AWESOME" and I search for "SQL IS AWESOME", can I still find that post? I tried using 2 sql queries, one for the original search query, and one modified to change all spaces to hyphens.

但是，如果我搜索"SQL IS-AWESOME"，它仍然找不到.有没有更简单的方法?

But if I search "SQL IS-AWESOME" it still won't find it. Is there an easier way?

我当前的代码:

$sql = "SELECT * FROM posts 
WHERE (post_title='".$query."' 
OR post_title LIKE '%".$query."' 
OR post_title LIKE '%".$query."%' 
OR post_title LIKE '".$query."%') 
".$locquery."
".$cat."
ORDER BY date DESC
LIMIT 18";

推荐答案

正如有人建议的那样，您可以改编并使用全文搜索.

As someone has suggested, you could just adapt and use fulltext searching.

如果您选择采用此路线，则需要在必填字段上启用全文搜索.

If you choose to take this route, you will need to enable fulltext searching on the fields required.

我假设您将检查post_title和post_body(?)，这需要您运行它；

I'll assume you will check post_title and post_body (?), which needs you to run this;

ALTER TABLE `posts` ADD FULLTEXT KEY `post_search` (`post_title`,`post_body`);

完成此操作后，您可以轻松地将您的搜索查询进行编辑；

When that is done, your search query can easily be edited to become;

$sql = "SELECT * FROM `posts` WHERE MATCH(post_title,post_body) AGAINST '$search'";

如果您想要更好的匹配，也可以根据它给它一个分数和顺序，这将需要类似于以下代码:

If you'd like better matching, it is also possible to give it a score and order by that, which would require code similar to this:

$sql = "SELECT *, MATCH(post_title, post_body) AGAINST ('{$search}') AS score ".
       "FROM `posts` WHERE MATCH(post_title, post_body) AGAINST ('{$search}') ORDER BY `score` DESC";

---笔记

对于搜索，您需要确定搜索方式.

For the search, you need to work out how you will be searching.

在上一个实例中，我使用了类似的形式，只是有一个搜索词形式(名为搜索")，导致$ _POST ['search']被发送到服务器.

In the last instance I used similar, I simply had a form for the search term (Named "Search") resulting in $_POST['search'] being sent to the server.

然后我用了

$search = (array_key_exists('search', $_POST) && is_string($_POST['search'])) ? mysql_real_escape_string($_POST['search'], $c) : FALSE ;
if ($search) {
    // Do the fulltext query requires (See above)
}

---

由于全文搜索将忽略连字符，因此如果您选择使用计分结果，则只剩下空格，这对于全文来说非常有用.

---

Since fulltext search will disregard the hyphen, you are left with just spaces, which works great for fulltext, if you opt to use scored results.

这篇关于在SQL中是否可以将连字符和空格相同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！