mysql显示每个单词一行,如何计算单词在mysql数据库的多行中的出现

i am looking for some help with counting the number of occurences in multiple rows of a mysql db.the purpose is for creting a search engine like google but only for my website

the scheme is to display the entry which has the most number of occurences of the word at the very top

for eg . the user searches for the word key.the records are searched and the data is shown in order of the number of occurences

1.this is a key which is a test key and also the profile key (3 occurences)

2.key give me a key (2 occurences)

....and so on...

is this the best approach?

SELECT (LENGTH(daya) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') AS `count` ORDER BY `count` DESC // data is the column name and key is the word

or is there a better one

Note:i dont want to use Like % %. also i want to show the number of times the word has occured like so the word key has occured 3 times.Could i use count()

解决方案

You can add the count in ORDER BY clause.

Try this way:

SELECT *,(LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') as cnt

FROM TableName

ORDER BY ((LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key')) DESC

To get the records of 2 occurences, you can use HAVING clause:

SELECT *,(LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') as cnt

FROM TableName

HAVING cnt=2

ORDER BY ((LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key')) DESC