Geek Answers Handbook

MySQL LIKE query

I am trying to find a string in my database, but it should match any word, not the whole phrase.

Suppose the table data has text of type abcdefg . Then, if I'm looking for dcb , it should show the results.

field LIKE '%dcb%' does not work this way.

Can anyone suggest a more reliable way to search, perhaps also show a relevancy counter.

I am not against using PHP for the above as well, but I prefer to do a DB level search.

+4
source share
5 answers

For best results, you need to create a FULLTEXT index for your data.

 CREATE TABLE mytable (id INT NOT NULL, data TEXT NOT NULL, FULLTEXT KEY fx_mytable_data) ENGINE=MyISAM SELECT * FROM mytable WHERE MATCH(data) AGAINST ('+word1 +word2 +word3' IN BOOLEAN MODE)

Note that to index single-letter words (as in your example) you need to set ft_min_word_len to 1 in MySQL confguration.

This syntax may work even if you don't have an index (so far your MyISAM table), but will be pretty slow.

+4
source

I think what you want to do, for any of the letters:

 field LIKE '%d%' or field like '%c%' or field like '%b%'

for all letters

 field LIKE '%d%' and field like '%c%' and field like '%b%'
+2
source

If the table is in MyISAM, you can use the FULLTEXT search integrated in MySQL: 11.8. Full Text Search Features

Although there will be some restrictions (for example, if I remember correctly, you cannot search for a word shorter than X characters - X is usually 3 or 4).


Another solution would be to use some Fulltext engine, such as Lucene , Solr , or Sphinx - they usually do a better job when it comes to full-text search: this is their job (MySQL's job is data storage, not full-text search)

There were a lot of questions about those on SO; eg:


If you use PHP and cannot install anything else, there is a complete PHP implementation of Lucene: Zend_Search_Lucene

+1
source

After all, MySQL LIKE sentences are not intended to be used as "powerful" search tools for word-based matching. This is a simple tool to search for partial phrases. It is also not known for scaling, so if you do this on a high-performance website, you probably need a different solution.

So, to be said, there are some options for you to get what you want:

  • REGEX support, MySQL has support for REGEX-based search queries . Using this and with complex enough REGEX, you can find what you are looking for.

  • True full-text indexing in MySQL. MySQL has a way to create FULLTEXT indexes . You need to use the MyISAM data engine, and there are restrictions on what exactly you can or cannot do. But it is much more powerful than the basic "similar" functions that SQL has. I would recommend reading on it if you are interested.

  • Third party indexes. This is actually the route that most people go. They will use Lucene / Solr , or other similar indexing technologies that are specifically designed for full-text word searches with different logic, just like modern web search engines work. They are extremely effective because they essentially maintain their own database, where they break everything down and store it in a way that is best suited for these types of searches.

Hopefully one of these three options will work for you.

+1
source

When using a like clause, observe %variable% or variable% not %variable .

Secondly. to do an affective search, use the explode function to break words, for example, if I search for “learn php”, it should look for the following: “learn + php”, as in Google. This is the explode() function.

0
source

More articles:


All Articles