mysql实现简单分词搜索

search.php

<?php
//近期外包项目使用代码
//@author gm

function get_fenci($word) {
    $word = preg_replace('/\s+/', ' ', trim($word));
    $word_arr = explode(' ', $word);

    $segments = array();
    foreach ($word_arr as $word) {

        $len = mb_strlen($word);
        $segments[$word] = array();

        for ($step=1; $step<$len+1; $step++) {
            $begin = 0;
            while ($begin < $len) {
                $segment = mb_substr($word, $begin, $step);
                if (mb_strlen($segment) != $step) {
                    $segment = mb_substr($word, -1, $step);
                    break;
                }
                $segments[$word][] = $segment;
                $begin++;
            }
        }
        $segments[$word] = array_reverse($segments[$word]);
    }

    return $segments;

}


function search($keywords, $page=1, $page_size=10) {


        $fenci_arr = get_fenci($keywords);

        $where_sql = '';
        foreach ($fenci_arr as $k => $v) {
            foreach ($v as $segment) {
                if(empty($where_sql)){
                    $where_sql.="m.title LIKE '%{$segment}%'";
                }else{
                    $where_sql.=" OR m.title LIKE '%{$segment}%'";
                }
            }
        }
        $order_sql = '';
        foreach ($fenci_arr as $k => $v) {
            foreach ($v as $k=>$segment) {
                if(empty($order_sql)){
                    $order_sql.="(CASE WHEN m.title LIKE '%".$segment."%' THEN ".(1000-10*$k)." ELSE 0 END)";
                } else {
                    $order_sql.=" + (CASE WHEN m.title LIKE '%".$segment."%'  THEN ".(1000-10*$k)." ELSE 0 END)";
                }
            }
        }
        $order_sql = $order_sql." DESC";


        $offset = ($page-1) * $page_size;

        $sql = 'SELECT `m`.*,`user`.`username`,doccate.name as tidname,doczj.name as zjname,groupcate.name as gidname,`file`.`savepath`,`file`.`savename`,`file`.`ext`, `file`.`size` FROM `es_doccon` `m` INNER JOIN `es_user` `user` ON `m`.`uid`=`user`.`id` LEFT JOIN `es_doccate` `doccate` ON `m`.`tid`=`doccate`.`id` LEFT JOIN `es_groupcate` `groupcate` ON `m`.`gid`=`groupcate`.`id` INNER JOIN `es_file` `file` ON `m`.`fileid`=`file`.`id` LEFT JOIN `es_doczj` `doczj` ON `m`.`zjid`=`doczj`.`id` WHERE `m`.`status` = 1 AND ( '.$where_sql.' ) ORDER BY '.$order_sql.' LIMIT '.$offset.','.$page_size;
        echo $sql;exit();

        $arr = $this->query($sql);


        $sql = 'SELECT count(1) as num FROM `es_doccon` `m` INNER JOIN `es_user` `user` ON `m`.`uid`=`user`.`id` LEFT JOIN `es_doccate` `doccate` ON `m`.`tid`=`doccate`.`id` LEFT JOIN `es_groupcate` `groupcate` ON `m`.`gid`=`groupcate`.`id` INNER JOIN `es_file` `file` ON `m`.`fileid`=`file`.`id` LEFT JOIN `es_doczj` `doczj` ON `m`.`zjid`=`doczj`.`id` WHERE `m`.`status` = 1 AND ( '.$where_sql.' )';
        $res = $this->query($sql);
        $count = $res[0]['num'];

        return array(
            'list' => $arr,
            'count' => $count,
        );
    }


$sql = 'SELECT `m`.*,`user`.`username`,doccate.name as tidname,doczj.name as zjname,groupcate.name as gidname,`file`.`savepath`,`file`.`savename`,`file`.`ext`, `file`.`size` FROM `es_doccon` `m` INNER JOIN `es_user` `user` ON `m`.`uid`=`user`.`id` LEFT JOIN `es_doccate` `doccate` ON `m`.`tid`=`doccate`.`id` LEFT JOIN `es_groupcate` `groupcate` ON `m`.`gid`=`groupcate`.`id` INNER JOIN `es_file` `file` ON `m`.`fileid`=`file`.`id` LEFT JOIN `es_doczj` `doczj` ON `m`.`zjid`=`doczj`.`id` WHERE `m`.`status` = 1 AND ( m.title LIKE '%a%' ) ORDER BY (CASE WHEN m.title LIKE '%a%' THEN 1000 ELSE 0 END) DESC LIMIT 0,10';