Mysql 全文搜索，建立索引，搜索，以及Yii2中进行全文搜索fulltext search

Fecify 自建saas商城，mysql做产品搜索，使用mysql的全文索引来进行产品数据搜索，下面分享一下

一：mysql

1.创建索引：表fecpx_shop_product的两个字段title, body_html创建全文索引

CREATE FULLTEXT INDEX ft_index_title_and_body_html ON fecpx_shop_product (title, body_html) WITH PARSER ngram

如果您是mysql5.6那么:

CREATE FULLTEXT INDEX ft_index_title_and_body_html ON fecpx_shop_product (title, body_html)

注意：第二种，不支持中日韩文切词，需要第一种方式创建索引，第一种需要mysql-5.7

2.进行查询

指定模式：select * from fecpx_shop_product where MATCH(title,body_html) AGAINST ('Description' IN NATURAL LANGUAGE MODE)

不指定模式（默认使用自然语言模式）：select * from fecpx_shop_product where MATCH(title,body_html) AGAINST ('Description')

二.yii2执行sql，进行分页处理

/**
     * 搜索商品
     * @author Terry
     */
    public function getWapSearchProducts($searchText, $pageNum=1, $numPerPage=2)
    {
        $status = ProductConstant::STATUS_ENABLE;
        $isNotDeleted = BasicConstant::IS_NOT_DELETEED;
        $connection = ProductModel::getDb();  // 返回的是一个db组件，这是从一个ActiveRecord得到db组件（ProductModel是一个Yii2 ActiveRecord），也可以通过 Yii::$app->db 得到db组件
        $pBegin = ($pageNum-1) * $numPerPage;
        $sql = "select id, handle, title, body_html,price, compare_at_price from fecpx_shop_product where MATCH(title,body_html) AGAINST (:query_search IN NATURAL LANGUAGE MODE) and status=:status and is_deleted=:is_deleted limit :p_begin , :p_number";

        $data = $connection->createCommand($sql)
            ->bindParam(":query_search", $searchText)
            ->bindParam(":status", $status)
            ->bindParam(":is_deleted",$isNotDeleted)
            ->bindParam(":p_begin",$pBegin)
            ->bindParam(":p_number",$numPerPage)

            ->queryAll();

        $sql = "select count(*) as count  from fecpx_shop_product where MATCH(title,body_html) AGAINST (:query_search IN NATURAL LANGUAGE MODE) and status=:status and is_deleted=:is_deleted ";

        $countData = $connection->createCommand($sql)
            ->bindParam(":query_search", $searchText)
            ->bindParam(":status", $status)
            ->bindParam(":is_deleted",$isNotDeleted)

            ->queryAll();


        $count = $countData[0]['count'] ?? 0;

        return [
            'coll' => $data,
            'count' => $count,  // 总个数
            'numPerPage' => $numPerPage,  // 每页的个数
            'pageNum' => $pageNum,  // 当前页
            'pageCount' => $numPerPage > 0 ? ceil($count / $numPerPage) : 0, // 总页数
        ];
    }