Implement a simple mysql weighted Chinese full-text search

Implementing a simple mysql weighted Chinese full-text searchI am writing a web and hope to perform full-text search on the database. However, Google learned that due to Chinese word segmentation, MySQL only supports full-text search in English. If you want to support Chinese, you need various plug-ins or implement some more complex mechanisms, and the virtual host you bought does not support these complex things. I thought about it carefully, because the function I need is relatively simple, mainly searching in two fields, and the amount of data is not large. Even if I add a few fields and run a few more selects, it will not have a big impact on the speed. So the requirements were realized through some work around.

Step 1: Use locate for a simple search
Locate can determine whether the substring is in the substring
There are two columns, one name and one description.
So you can use LOCATE>0 To determine whether the keyword appears in it.
In fact, it is
SELECT * FROM table WHERE LOCATE(key, 'name')>0 OR LOCATE(key, 'description);
In this way, we simply implement a certain key between two Domain search

Step 2: Search for multiple keywords
Usually, searches have multiple keywords, so we need to execute the query in Step 1 for each keyword. (Of course, you can also combine it into one, but here we are lazy and only query one keyword at a time)
Then, we merge the arrays queried each time, so that we get a final set.

The php code is as follows:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>function selectlocate($tarcols,$skey){<br /> </li><li>$where ="";<br /></li><li>$connector = " ";<br /></li><li>global $count;<br /></li><li>foreach($tarcols as $tarcol ){<br /></li><li>$where .= $connector;<br /></li><li>$where .= "LOCATE('$skey', $tarcol) != 0  ";<br /></li><li>if($connector == " "){<br /></li><li>$connector = " OR ";<br /></li><li>}<br /></li><li>}<br /></li><li><br /></li><li>$sql = "SELECT * FROM pets_table WHERE $where";<br /></li><li>$result = mysql_query($sql);<br /></li><li>$ret = Array();<br /></li><li>while($item = mysql_fetch_array($result, MYSQL_ASSOC)){<br /></li><li>$count ++;<br /></li><li>$ret[] = $item;<br /></li><li>}<br /></li><li>return $ret;<br /></li><li>} </li></ol>

Copy after login

Step 3: Matching weight
The result of Step 2 above is actually unordered. Usually, if we search for a field:
1. If this field is exactly the same as the keyword, then generally speaking, this result should be the most relevant
2. If it only appears once, The correlation is the lowest.
3. If the number of times it appears is higher than the number of times it appears in other rows, then its correlation is higher than the result in 2
So, when searching, consider the weight according to this order,
a .If they are completely equal, the weight is 1000
b. If it appears once, the weight is 10, and it appears n times
c. The weight is n*10
A weight is attached to each search result --- -》Then merge the same items----》and add up the weights
Finally sort by weight to get a sorted search result.

The following are the codes for two types of queries with 1 keyword corresponding to 1 field (the above code is 1 keyword with multiple fields) (excluding the code for merging two arrays, the relevant code is in Step 4), Just traverse each keyword and field to complete the search

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>$count = 0;<br /> </li><li>function selectequal($col,$skey){<br /></li><li>$connector = " ";<br /></li><li>global $count;<br /></li><li>$sql = "SELECT * FROM pets_table WHERE LOWER($col)=LOWER('$skey')";<br /></li><li>$result = mysql_query($sql);<br /></li><li>$ret = Array();<br /></li><li>while($item = mysql_fetch_array($result, MYSQL_ASSOC)){<br /></li><li>$count ++;<br /></li><li>$item["weight"] = 1000;<br /></li><li>$ret[] = $item;<br /></li><li>}<br /></li><li>return $ret;<br /></li><li>}<br /></li><li>function selectlocate($col,$skey){<br /></li><li>global $count;<br /></li><li>$sql = "SELECT *,(LENGTH(description) - LENGTH(REPLACE(description, '$skey', '')))/LENGTH('$skey') *10 as weight FROM pets_table WHERE LOCATE(LOWER('$skey'),LOWER($col))>0";<br /></li><li>$result = mysql_query($sql);<br /></li><li>$ret = Array();<br /></li><li>while($item = mysql_fetch_array($result, MYSQL_ASSOC)){<br /></li><li>$count ++;<br /></li><li>$ret[] = $item;<br /></li><li>}<br /></li><li>return $ret;<br /></li><li>} </li></ol>

Copy after login

Step 4: Weight of the field
In my needs, obviously name this The field is more important than the description, so when matching, the result of the name field should be tilted, so a weight coefficient for the field can be added.

1. If it is a match in the name field, set the coefficient to 10;
2. If it is a match in the description, set the coefficient to 1;

Calculate Step 3 each time By multiplying the weight obtained by this coefficient, a new, more effective weight value can be obtained.
Finally, sort by weight to get the most relevant search results

Other details:
If a keyword has satisfied the equal condition, then when using the locate condition will still return a result, so when using the locate condition, filter out the equal case

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li><?php<br /> </li><li>$count = 0;<br /></li><li>function selectequal($col,$val,$skey){<br /></li><li>$connector = " ";<br /></li><li>global $count;<br /></li><li>$sql = "SELECT * FROM pets_table WHERE LOWER($col)=LOWER('$skey')";<br /></li><li>$result = mysql_query($sql);<br /></li><li>$ret = Array();<br /></li><li>while($item = mysql_fetch_array($result, MYSQL_ASSOC)){<br /></li><li>$count ++;<br /></li><li>$item["weight"] = 1000*$val;<br /></li><li>$ret[] = $item;<br /></li><li>}<br /></li><li>return $ret;<br /></li><li>}<br /></li><li>function selectlocate($col,$val,$skey){<br /></li><li>global $count;<br /></li><li>$sql = "SELECT *,(LENGTH(description) - LENGTH(REPLACE(description, '$skey', '')))/LENGTH('$skey') *10*$val as weight FROM pets_table WHERE LOCATE(LOWER('$skey'),LOWER($col))>0 AND LOWER($col)!=LOWER('$skey')";<br /></li><li>$result = mysql_query($sql);<br /></li><li>$ret = Array();<br /></li><li>while($item = mysql_fetch_array($result, MYSQL_ASSOC)){<br /></li><li>$count ++;<br /></li><li>$ret[] = $item;<br /></li><li>}<br /></li><li>return $ret;<br /></li><li>}<br /></li><li>function cleanarr($arr){<br /></li><li>global $count;<br /></li><li>$tmp = Array();<br /></li><li>$tmpall = Array();<br /></li><li>foreach($arr as $item){<br /></li><li>if(array_key_exists($item['uid'], $tmp)){<br /></li><li>$tmp[$item['uid']]+=$item["weight"];<br /></li><li>}<br /></li><li>else{<br /></li><li>$tmp[$item['uid']] = $item["weight"];<br /></li><li>$tmpall[$item['uid']] = $item;<br /></li><li>}<br /></li><li>}<br /></li><li><br /></li><li>//sort by weight in descending order <br /></li><li>arsort($tmp);<br /></li><li><br /></li><li>$ret = Array();<br /></li><li><br /></li><li>//rebuildthe return arary<br /></li><li>$count = 0;<br /></li><li>foreach($tmp as $k=>$v){<br /></li><li>$count++;<br /></li><li>$tmpall[$k]['weight']=$v;<br /></li><li>$ret[]=$tmpall[$k];<br /></li><li>}<br /></li><li>return $ret;<br /></li><li>}<br /></li><li><br /></li><li>require_once("consvr.php");<br /></li><li><br /></li><li><br /></li><li>$colshash = array("name"=>10,"description"=>1);<br /></li><li>$ret = Array();<br /></li><li>$keywords=explode(" ", $keywords);<br /></li><li>$cols = array_keys($colshash);<br /></li><li>foreach($keywords as $keyword){<br /></li><li>foreach($colshash as $col=>$val){<br /></li><li>$ret = array_merge($ret,selectequal($col,$val, $keyword));<br /></li><li>$ret = array_merge($ret,selectlocate($col,$val, $keyword));<br /></li><li>}<br /></li><li><br /></li><li>}<br /></li><li>$ret = cleanarr($ret);<br /></li><li>$ret = array('msg' => "Success", 'count'=>$count,'children' => $ret, 'query'=>"COMPLEX:NOT READABLE");<br /></li><li>echo json_encode($ret);<br /></li><li>mysql_close();<br /></li><li><br /></li><li>?> </li></ol>

Copy after login

Implement a simple mysql weighted Chinese full-text search_PHP tutorial