MySQL 全文检索 ngram Mybatis

创建全文索引(FullText index)

  创建表的同时创建全文索引

    FULLTEXT (name) WITH PARSER ngram

  通过 alter table 的方式来添加

    alter table `das`.`staff_base` add fulltext index staff_base_name(`name`) with parser ngram;

  直接通过create index的方式(未测试)

    CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)

    也可以在创建索引的时候指定索引的长度:

    CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20))

删除全文索引(未测试)

  直接使用 drop index(注意:没有 drop fulltext index 这种用法)

    DROP INDEX full_idx_name ON tommy.girl ;

  使用 alter table的方式

    ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;

使用全文索引

  使用全文索引的格式:  MATCH (columnName) AGAINST ('string')

1. 自然语言模式下检索:

  得到符合条件的个数

SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST ('数据库' IN NATURALLANGUAGE MODE);

  得到匹配的比率

SELECT id, MATCH (title,body) AGAINST ('数据库' IN NATURAL LANGUAGE MODE) AS score FROM articles;

2. 布尔模式下搜索,这个就相对于自然模式搜索来的复杂些:

  匹配既有管理又有数据库的记录

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+数据库 +管理' IN BOOLEAN MODE);

  匹配有数据库,但是没有管理的记录

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+数据库 -管理' IN BOOLEAN MODE);

  匹配MySQL,但是把数据库的相关性降低

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('>数据库 +MySQL' INBOOLEAN MODE);

3. 查询扩展模式,比如要搜索数据库,那么MySQL,oracle,DB2也都将会被搜索到

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('数据库' WITH QUERY EXPANSION);

4. ft_boolean_syntax (+ -><()~*:""&|)使用的例子:

  + : 用在词的前面,表示一定要包含该词,并且必须在开始位置。

    eg: +Apple 匹配:"Apple123" , "tommy, Apple"

  - : 不包含该词,所以不能只用「-yoursql」这样是查不到任何row的,必须搭配其他语法使用。

    eg: MATCH (name) AGAINST ('-lime +oracle') 

    匹配到: 所有不包含lime,但包含oracle的记录 

  空(也就是默认情况),表示可选的,包含该词的顺序较高。 

     例子:

      apple banana    找至少包含上面词中的一个的记录行。或的关系

      +apple +juice    两个词均在被包含。与的关系

      +apple macintosh    包含词 “apple”,但是如果同时包含 “macintosh”,它的排列将更高一些

      +apple -macintosh    包含 “apple” 但不包含 “macintosh”

  > :提高该字的相关性,查询的结果会排在比较靠前的位置。 

  < :降低相关性,查询的结果会排在比较靠后的位置。

     先不使用 >< 可以看到完全匹配的排的比较靠前

select * from tommy.girl where match(girl_name) against('张欣婷' in boolean mode);

    单独使用 > 使用了>的李秀琴马上就排到最前面了

select * from tommy.girl where match(girl_name) against('张欣婷 >李秀琴' in boolean mode);

    单独使用 < 看到没,不是人也排到最前面了,这里使用的可是 < 哦,说好的降低相关性呢,往下看吧。

select * from tommy.girl where match(girl_name) against('张欣婷 <不是人' in boolean mode);

    同时使用>< 到这里终于有答案了,只要使用了 ><的都会往前排,而且>的总是排在<的前面

    1. 只要使用 ><的总比没用的 靠前

    2. 使用  >的一定比 <的排的靠前 (这就符合相关性提高和降低)

    3. 使用同一类的,使用的越早,排的越前。

 select * from tommy.girl where match(girl_name) against('张欣婷 >李秀琴 <练习册 <不是人>是个鬼' in boolean mode);

  ( ):可以通过括号来使用字条件。  

    eg: +aaa +(>bbb <ccc)  

    找到有aaa和bbb和ccc,aaa和bbb,或者aaa和ccc(因为bbb,ccc前面没有+,所以表示可有可无),然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc

  ~ :将其相关性由正转负,表示拥有该字会降低相关性,但不像「-」将之排除,只是排在较后面。 

    eg:   +apple ~macintosh   先匹配apple,但如果同时包含macintosh,就排名会靠后。

  * :通配符,这个只能接在字符串后面。 

    MATCH (girl_name) AGAINST ('+*ABC*')   #错误,不能放前面

    MATCH (girl_name) AGAINST ('+张筱雨*')  #正确

  " " :整体匹配,用双引号将一段句子包起来表示要完全相符,不可拆字。

    eg:  "tommy huang" 可以匹配  tommy huang xxxxx   但是不能匹配  tommy is huang。

 MyBatis 中 使用 全文索引注意:

  前端传入数据格式name:"",name:"lime oracle"

package com.das.mapper.service;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.util.HashMap;
import java.util.Map;

/**
 * @Author liangmy
 * @Date 2018/2/26
 */
public class ServiceBaseProvider {

    public String getServiceBaseList(Map<String, Object> map){
        StringBuffer name = new StringBuffer();
        for(String str : (null == map.get("name") ? " " : map.get("name").toString().trim() + " ").split(" ")){
            name.append("+" + str + " ");
        }
        if(name.length() > 2) {
            name.deleteCharAt(name.length() - 1);
        }
        String level = null == map.get("level") ? "" : map.get("level").toString();
        return new SQL(){
            {
                SELECT("id");
                FROM("service_base");
                if(!StringUtils.isEmpty(name)) {
                    WHERE("MATCH(name) AGAINST('" + name.toString() + "' IN BOOLEAN MODE)");
                }
                if(!StringUtils.isEmpty(level)){
                    AND().WHERE("JSON_CONTAINS(level,'" + level + "')");
                }
            }
        }.toString();
    }
}

   前端传入数据格式name:"",name:"lime 1026", name:"1026" :  mobile:"",mobile:"lime 1026", mobile:"1026"

package com.das.mapper.staff;

import com.das.common.type.StaffBaseStatusEnum;
import com.das.common.type.StaffRecordTypeEnum;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

/**
 * @Author liangmy
 * @Date 2018/2/9
 */
public class StaffProvider {

    public String getIdList(Map<String, Object> map){
        Long storeBaseId = (Long) map.get("storeBaseId");
        Long postBaseId = (Long) map.get("postBaseId");
        StaffBaseStatusEnum status = (StaffBaseStatusEnum) map.get("status");
        String name = (String) map.get("name");
        String mobile = null == map.get("mobile") ? "" : "\"" + map.get("mobile") + "\"";
        return new SQL(){
            {
                SELECT("`staff_base`.`id`");
                FROM(" `das`.`staff_base`");
                if(null != storeBaseId && storeBaseId > 0){
                    WHERE("store_base_id = #{storeBaseId}");
                }
                if(null != postBaseId && postBaseId > 0){
                    AND().WHERE("post_base_id = #{postBaseId}");
                }
                if(null != status){
                    AND().WHERE("status = #{status}");
                }
                if(StringUtils.isNotEmpty(name)){
                    AND().WHERE("MATCH(name) AGAINST('" + name + "' IN BOOLEAN MODE)");
                }
                if(StringUtils.isNotEmpty(mobile)){
                    AND().WHERE("MATCH(mobile) AGAINST('" + mobile + "' IN BOOLEAN MODE)");
                }
            }
        }.toString();
    }
}

   前端传入数据格式carNum:"",carNum:"1026"

package com.das.mapper.order;

import com.das.common.type.ReceiveBaseDurationEnum;
import com.das.common.type.VipTypeEnum;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;


/**
 * @Author liangmy
 * @Date 2018/2/28
 */
public class ReceiveBaseProvider {

    public String getReceiveBaseIdList(Map<String, Object> map) {
        ReceiveBaseDurationEnum duration = (ReceiveBaseDurationEnum) map.get("duration");
        VipTypeEnum type = (VipTypeEnum) map.get("type");
        String carNum = null == map.get("carNum") ? "" : "\"" + map.get("carNum") + "\"";
        return new SQL() {
            {
                SELECT("id");
                FROM("receive_base");
                if (null != duration) {
                    switch (duration){
                        case TODAY:
                            WHERE("TO_DAYS(mgt_create) = TO_DAYS(NOW())");
                            break;
                        case WEEK:
                            WHERE("DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(mgt_create)");
                            break;
                        case MONTH:
                            WHERE("DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(mgt_create)");
                            break;
                        case QUARTER:
                            WHERE("DATE_SUB(CURDATE(), INTERVAL 60 DAY) <= date(mgt_create)");
                            break;
                    }
                }
                if (null != type){
                    AND().WHERE("JSON_CONTAINS(vip_base_type_item,'" + type + "')");
                }
                if(!StringUtils.isEmpty(carNum)){
                    AND().WHERE("MATCH(car_num) AGAINST('" + carNum + "' in boolean mode)");
                }
                ORDER_BY("mgt_create");
            }
        }.toString();
    }
}

 

 

啦啦啦

MySQL使用全文索引(fulltext index)

InnoDB全文索引:N-gram Parser【转】

posted @ 2018-03-01 10:37  limeOracle  阅读(2588)  评论(1编辑  收藏  举报