使用PHP MySql进行关键字搜索? [英] Keyword search using PHP MySql?

问题描述

我的mysql表中有标题(varchar)，描述(文本)，关键字(varchar)字段.

I have title (varchar), description (text), keywords (varchar) fields in my mysql table.

我保留了关键字字段，因为我认为我只会在此字段中进行搜索.但是我现在需要在所有三个字段中进行搜索.因此，对于关键字"word1 word2 word3"，我的查询变为

I kept keywords field as I thought I would be searching in this field only. But I now require to search among all three fields. so for keywords "word1 word2 word3", my query becomes

SELECT * FROM myTable
WHERE (
name LIKE '%word1%' OR description LIKE '%word1%' OR keywords LIKE '%word1%' 
OR name LIKE '%word2%' OR description LIKE '%word2%' OR keywords LIKE '%word2%' 
OR name LIKE '%word3%' OR description LIKE '%word3%' OR keywords LIKE '%word3%') 
AND status = 'live'

看起来有点混乱，但这可行.但是现在我需要实现同义词搜索.因此，对于给定的单词，假设有几个同义词可用，那么当我遍历所有单词时，该查询会变得更加混乱.随着需求变得越来越清晰，我也需要将此myTable加入其他一些表中.

Looks a bit messy but this works. But now I need to implement synonym search. so for a given word assuming there are a few synonyms available this query becomes more messy as I loop through all of the words. As the requirements are getting clearer, I will need to join this myTable to some other tables as well.

所以

• 您是否认为上述方法比较凌乱，并且会随着数据的增长而引起问题?

• Do you think the above way is messy and will cause problems as the data grow?

如何避免上述混乱?我可以通过任何更清洁的解决方案吗?任何例子都会对我有帮助.

How can I avoid above mess? Is there any cleaner solution I can go by? Any example will help me.

谢谢

@Peter Stuifzand建议我可以创建一个search_index表并存储所有3个字段(标题，关键字，描述)的信息并进行全文搜索.我知道该表还将包括对myTable主键的引用.

@Peter Stuifzand suggested me that I could create one search_index table and store all 3 fields (title,keyword,desc) info on that and do full text search. I understand that additionally this table will include reference to myTable primary key as well.

但是我的高级搜索可能包括将mytable与Category表，geoge_location表(用于在10英里，20英里等范围内进行搜索)结合在一起，通过其他条件过滤，当然还包括对搜索结果的排序.您认为使用mysql fulltext不会减慢速度吗?

But my advanced search may include joining mytable with Category table, geographic_location table (for searching within 10, 20 miles etc), filtering by someother criteria and of course, sorting of search results. Do you think using mysql fulltext will not slow it down?

推荐答案

当查询失控时，有时最好用SQL编写查询的一部分，并用您选择的编程语言编写其他部分.

When your queries are getting out of hand, it's sometimes better to write parts of it in SQL and other parts in your programming language of choice.

您还可以使用全文搜索进行搜索.您可以使用要搜索的所有字段创建单独的表，然后添加FULLTEXT修饰符.

And you could also use fulltext search for searching. You can create separate table with all fields that you want to search and add the FULLTEXT modifier.

CREATE TABLE `search_index` (
    `id` INT NOT NULL,
    `data` TEXT FULLTEXT,
);

SELECT `id` FROM `search_index` WHERE MATCH(`data`) AGAINST('word1 word2 word3');

这篇关于使用PHP MySql进行关键字搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！