自己在写一个 web, 希望对数据库做全文检索。但是 google 了解到,由于中文分词的缘故,mysql 只支持英文的全文搜索,想支持中文的,需要各种插件 or 实现一些比较复杂的机制,而买的虚拟主机并不支持这些复杂的东西。仔细想了下,因为自己需求的功能也比较简单,主要是 2 个字段的搜索,且数据量不大,即便增加几个字段,需要多运行几个 select 也不会对速度有太大影响,所以通过一些 work around 实现了需求。
Step 1: 用 locate 进行简单的搜索
Locate 可以判断子串是否在子乱
有两个 column, 一个 name, 一个 description.
所以可以用 LOCATE>0
去判断是否关键字在其中出现了。
其实就是
SELECT * FROM table WHERE LOCATE(key, ‘name’)>0 OR
LOCATE(key, ‘description);
这样,我们就简单实现了对某个 key 在两个域的搜索
Step 2: 搜索多个关键字
通常,搜索都是有多个关键字,所以我们需要对每个关键字,执行下 Step1 的查询。(当然,也可以合成一个,这里偷懒每次只查询 1
个关键字)
然后,我们再将每次查询出的数组都合并,这样就得到了一个最终的集合。
function selectlocate($tarcols,$skey){
$where ="";
$connector = " ";
global $count;
foreach($tarcols as $tarcol ){
$where .= $connector;
$where .= "LOCATE('$skey', $tarcol) != 0 ";
if($connector == " "){
$connector = " OR ";
}
}
$sql = "SELECT * FROM pets_table WHERE $where";
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$ret[] = $item;
}
return $ret;
}
Step 3:匹配的权重
上面 Step2 的结果,其实是无序的。通常,如果我们搜索一个字段:
1. 如果这个字段和关键字完全相同,那么一般来讲,可能这个结果应该是相关度最高的
2.
如果他只是其其中出现了一次,相关度就最低。
3. 如果他出现的次数比在其他 row 中出现的次数高,那么他的相关度就比 2 中的结果高
所以,搜索的时候依据这个顺序考虑权重,
a.
如果完全相等,权重为 1000
b. 如果出现 1 次,权重为 10,出现 n 次
c. 权重为 n*10
每次搜索出来的结果附加上权重 —-》然后合并相同项
—-》并把权重累加
最后按权重排序,即可得到一个有排序的搜索结果。
以下是两种 1 关键字对应 1 个字段(上面的代码是 1 关键字多个字段)查询的代码(不包含合并两个数组的代码,相关的代码在 Step4 中),只需遍历每个关键字和字段,就能完成搜索。
$count = 0;
function selectequal($col,$skey){
$connector = " ";
global $count;
$sql = "SELECT * FROM pets_table WHERE LOWER($col)=LOWER('$skey')";
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$item["weight"] = 1000;
$ret[] = $item;
}
return $ret;
}
function selectlocate($col,$skey){
global $count;
$sql = "SELECT *,(LENGTH(description) - LENGTH(REPLACE(description, '$skey', '')))/LENGTH('$skey') *10 as weight FROM pets_table WHERE LOCATE(LOWER('$skey'),LOWER($col))>0";
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$ret[] = $item;
}
return $ret;
}
Step 4: 字段的权重
在我的需求中,显然 name 这个字段比 description 更重要,所以在匹配时,对 name 字段的结果应该有所倾斜,所以,又可以增加一个对字段的权重系数。
1. 如果是在
name 域的匹配,设系数为 10;
2. 如果是在 description 匹配,设系数为 1;
将 Step 3
每次计算得出的权重,再乘上这个系数,就可以得到一个新的,更有效的权重值。
最后按权重排序,即可得到一个最有相关度排序的搜索结果
其他的细节:
如果一个关键字已经满足了
equal 条件,那么再使用 locate 条件的时候会依然返回一个结果,所以在使用 locate 条件的时候,过滤掉 equal 的情况。
<?php
$count = 0;
function selectequal($col,$val,$skey){
$connector = " ";
global $count;
$sql = "SELECT * FROM pets_table WHERE LOWER($col)=LOWER('$skey')";
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$item["weight"] = 1000*$val;
$ret[] = $item;
}
return $ret;
}
function selectlocate($col,$val,$skey){
global $count;
$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')";
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$ret[] = $item;
}
return $ret;
}
function cleanarr($arr){
global $count;
$tmp = Array();
$tmpall = Array();
foreach($arr as $item){
if(array_key_exists($item['uid'], $tmp)){
$tmp[$item['uid']]+=$item["weight"];
}
else{
$tmp[$item['uid']] = $item["weight"];
$tmpall[$item['uid']] = $item;
}
}
//sort by weight in descending order
arsort($tmp);
$ret = Array();
//rebuild the return arary
$count = 0;
foreach($tmp as $k=>$v){
$count++;
$tmpall[$k]['weight']=$v;
$ret[]=$tmpall[$k];
}
return $ret;
}
require_once("consvr.php");
$colshash = array("name"=>10,"description"=>1);
$ret = Array();
$keywords=explode(" ", $keywords);
$cols = array_keys($colshash);
foreach($keywords as $keyword){
foreach($colshash as $col=>$val){
$ret = array_merge($ret,selectequal($col,$val, $keyword));
$ret = array_merge($ret,selectlocate($col,$val, $keyword));
}
}
$ret = cleanarr($ret);
$ret = array('msg' => "Success", 'count'=>$count,'children' => $ret, 'query'=>"COMPLEX:NOT READABLE");
echo json_encode($ret);
mysql_close();
?>