实现一个简单的mysql带权重的中文全文搜索

实现一个简单的mysql带权重的中文全文搜索自己在写一个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个关键字）
然后，我们再将每次查询出的数组都合并，这样就得到了一个最终的集合。

php代码如下：

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;
} 

$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;
} 

 
$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();

//rebuildthe 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();

?>