SQL search query like stackoverflow

Like when the user writes the article title in the input field, I want to search existing articles to see if there are similar ones.

For eg.

SQL search 开发者_C百科query like stackoverflow

I want to find the most relevant articles related to this title.

I know it's something like:

WHERE article_title LIKE 'word'

but how do I handle multiple keywords?

Use a fulltext index, which'd be something like:

SELECT ... FROM ... WHERE MATCH (fieldname) AGAINST ('keyword keyword keyword');

Or hack up the query to look like

SELECT ... FROM ... WHERE (fieldname LIKE '%keyword%' OR fieldname LIKE '%keyword%' etc...)

Of the two, the fulltext version will be faster, as it can use an index. The 'LIKE %...% version will be very expensive, as wildcard search of that sort cannot use indexes at all. The downside is that fulltext indexes are only available on MyISAM tables, and will probably never be available for InnoDB tables.

You need to have full text search for that.

Make sure you are using MyISAM as the engine for the table you want to search on.

Have the following table

Table articles
--------------
id integer autoincrement PK
title varchar(255) with fulltext index
contents textblob with fulltext index

And use a query like:

SELECT id
  , MATCH(title, contents) AGAINST ('$title_of_article_thats_being_edited') 
  as relevance
FROM articles
WHERE MATCH(title, contents) AGAINST ('$title_of_article_thats_being_edited') 
ORDER BY relevance

Note that SO refines the list when you enter tags.

WHERE article_title LIKE '%word1%word2%'

will return all rows in which article_title contains 'word1' and 'word2' in this particular order