MySQL queries and text search

I have this query:

select name, body
  from news
 where body like %MyWord%;

I use MySQL database engine开发者_JAVA技巧. this query will return name, body when found MyWord in body text.

My problem here is that, when I search about two word in body text, like MyWord1 , MyWord2. or more !!!

How I can do that if you know that this query is calling by function (That I can not modify that query all time).

If you need more functionality on your text search patterns, you should use FULL-TEXT-SEARCH in MySQL with the proper indexes.

You will be able to search two or more words at once if that's what you are needing.

You could use some evil SQL-injection (kind of) :)

I assume you pass "MyWord" to the function that contains the query you can't ("can't" or "don't like to"??) change. What happens, if "MyWord" looks something like this:

MyWord1% OR body like %MyWord2

Evil, I know, you have been warned ;)

if you want to find two different words you can do this:

select name, body
  from news
 where body like %MyWord1% and body like %MyWord2%;

however, that will soon become very non-performant, and there are a number of other options you can look at:

• using fulltext search, as suggested by Pablo Santa Cruz (probably the simplest to use)
• parse your body field into a subtable via a trigger upon insert
• search the data by populating a lucene/solr index and searching off of that (a bit more complicated to setup and maintain but the the performance is very impressive.