A detailed example analysis of mysql keyword relevance ranking method

Small projects sometimes need to use keyword search relevance ranking, using sphinx seems to kill the chicken with a knife, mysql order by to deal with.
Method 1:

select * from articles where (title LIKE '%keywords%') or (content LIKE '%helloworld%') order by ((CASE WHEN title LIKE '%keywords%' THEN 2 ELSE 0 END) + (CASE WHEN content LIKE '%helloworld%' THEN 1 ELSE 0 END)) ASC, dateline DESC

Method 2:
For example, if you search for the keyword "IBM," "server,"
First of all, the search keyword processing, the code is as follows:

$kw = preg_replace("/(\s+)|(　+)+/","", $kw);// Replace the blank space , A newline ,tab, Chinese space 
$kw = preg_replace( "/(^\s*)|(\s*$)/ ", "",$kw);// Remove first and last Spaces 
$kw = preg_replace("/(\s+)/", "", $kw);// Replace multiple Spaces with 1 A blank space 
$q = explode('',$kw);// Enumeration keyword 

You need to add one more code to get rid of punctuation, but this code will have problems and you don't know how to fix it.

Then the code that generates the SQL statement

$f = array( " name " , " description " ); // Query field name= Product name ,description= The product description 
$s = array(4,2); // The weight ,name Field matching integral 4 Points, description Field matching product 2 Points, and then finally sorted by integration 

// Create a query condition 
for($i=0;$i<count($q);$i++){
for($j=0;$j<count($f);$j++){
$clause[$c] =  "  ( " .$f[$j]. "  LIKE  ' % " .$q[$i]. " %')  " ;
$score[$c] =  "  IF(LOCATE(' " .$q[$i]. "' ,  " .$f[$j]. " ),  " .$s[$j]. " , 0)  " ;
$c++;
}
}
$sql =  " SELECT id, name, description,
( " .implode( " + " ,$score). " ) AS score
FROM product
WHERE ( " .implode( "  OR  " ,$clause). " )
ORDER BY score DESC " ;