MySQL-fulltext-全文搜索-索引-MATCH AGAINST IN boolean MODE

1. fulltext全文搜索介绍

类似es，分词搜索
文档：https://zhuanlan.zhihu.com/p/146361883
https://www.cnblogs.com/guifanbiji/p/6202195.html

2. 增加索引

ALTER TABLE `biz_article`
ADD FULLTEXT INDEX `idx_fulltext`(`title`, `content`, `description`, `keywords`) WITH PARSER ngram;

不能加 use BTREE
要加上 WITH PARSER ngram ，用于支持中文的分词，否则查不到出来

遇到搜索不到“Java”

show variables like 'ft%';

vi /etc/my.cnf
#f ft_stopword_file 去掉 没有测试过

#fulltext分词
ft_min_word_len = 1

sudo service mysqld restart

-- 触发重建索引
REPAIR TABLE biz_article quick;
OPTIMIZE TABLE biz_article;
ALTER TABLE biz_article ENGINE=InnoDB;

-- 有效
ALTER TABLE biz_article ENGINE = MyISAM

3. 精确匹配

3.1 交集（in boolean mode 表示精确）

3.1.1 like写法对应mybatis xml:

<when test="keywordList != null and keywordList.size() > 0">
    AND <foreach collection="keywordList" item="keyword" index="index" open="(" close=")"
                 separator=" AND ">
    (
    a.title LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%') OR
    a.description LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%') OR
    a.keywords LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%') OR
    a.content LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%')
    )
</foreach>
SELECT * from biz_article a where  is_delete = 0 AND (
(
a.title LIKE CONCAT( '%', '水平', '%' )
OR a.description LIKE CONCAT( '%', '水平', '%' )
OR a.keywords LIKE CONCAT( '%', '水平', '%' )
OR a.content LIKE CONCAT( '%', '水平', '%' )
)
AND (
a.title LIKE CONCAT( '%', '都留有空白的容器', '%' )
OR a.description LIKE CONCAT( '%', '都留有空白的容器', '%' )
OR a.keywords LIKE CONCAT( '%', '都留有空白的容器', '%' )
OR a.content LIKE CONCAT( '%', '都留有空白的容器', '%' )
)
) ;

3.1.2 FULLTEXT MATCH AGAINST IN boolean MODE 写法：

SELECT id,title FROM biz_article WHERE MATCH (`title`,`content`,`description`,`keywords`) AGAINST ('"水平"' IN boolean MODE) AND MATCH (`title`,`content`,`description`,`keywords`) AGAINST ('"都留有空白的容器"' IN boolean MODE) AND is_delete=0;

SELECT id,title FROM biz_article WHERE MATCH (`title`,`content`,`description`,`keywords`) AGAINST ('+"水平" +"都留有空白的容器"' IN boolean MODE) AND is_delete=0;

3.2 并集（in boolean mode 表示精确）

3.2.1 like写法对应mybatis xml

<when test="keywordList != null and keywordList.size() > 0">
    AND <foreach collection="keywordList" item="keyword" index="index" open="(" close=")"
                 separator=" OR ">
    (
    a.title LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%') OR
    a.description LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%') OR
    a.keywords LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%') OR
    a.content LIKE CONCAT('%',#{keyword , jdbcType=VARCHAR},'%')
    )
</foreach>
SELECT * from biz_article a where  is_delete = 0 AND (
(
a.title LIKE CONCAT( '%', '水平', '%' )
OR a.description LIKE CONCAT( '%', '水平', '%' )
OR a.keywords LIKE CONCAT( '%', '水平', '%' )
OR a.content LIKE CONCAT( '%', '水平', '%' )
)
OR (
a.title LIKE CONCAT( '%', '都留有空白的容器', '%' )
OR a.description LIKE CONCAT( '%', '都留有空白的容器', '%' )
OR a.keywords LIKE CONCAT( '%', '都留有空白的容器', '%' )
OR a.content LIKE CONCAT( '%', '都留有空白的容器', '%' )
)
) ;

3.2.2 FULLTEXT MATCH AGAINST IN boolean MODE 写法

SELECT id,title FROM biz_article WHERE (MATCH (`title`,`content`,`description`,`keywords`) AGAINST ('"水平"' IN boolean MODE) OR MATCH (`title`,`content`,`description`,`keywords`) AGAINST ('"都留有空白的容器"' IN boolean MODE)) AND is_delete=0;

SELECT id,title FROM biz_article WHERE MATCH (`title`,`content`,`description`,`keywords`) AGAINST ('"水平" "都留有空白的容器"' IN boolean MODE) AND is_delete=0;

4. 代码接入

4.1 处理参数

//'+"水平" +"都留有空白的容器"'
//'"水平" "都留有空白的容器"'
private String fulltextKeyStr;

public String getFulltextKeyStr() {
    List<String> keywordList = getKeywordList();
    if (!CollectionUtils.isEmpty(keywordList)) {
        boolean isAnd = getSearchResultMode().equals(CommonConst.ARTICLE_SEARCH_RESULT_MODE_INTERSECTION);
        String sql = "'";
        for (String kw : keywordList) {
            if (isAnd) {
                sql += " +\"" + kw + "\"";
            } else {
                sql += " \"" + kw + "\"";
            }
        }
        sql += "'";
        return sql;
    }
    //不先用fulltext查询，就返回null
    return null;
}

public List<String> getKeywordList() {
        if(StringUtils.isNotBlank(keywords)){
            List<String> keywordList = ValueUtil.convertSearchWordToList(keywords);
         // setKeywords(null);
            keywordByOne = 0;
            return keywordList;
        }
        return keywordList;
}

public class ValueUtil {
    public static String INVALID_SYMBOL = "[\r\n\t]"; //去掉所有的空额
    public static String BLANK = " ";
    public static String BLANK_2 = BLANK + BLANK;
    public static String COMMA = ",";

    public static String replace(String input, String from, String to) {
        if (StringUtils.isBlank(input) || from == null || from.length() == 0 || to == null || to.length() == 0) {
            return input;
        }

        input = input.replaceAll(from, to).replaceAll(INVALID_SYMBOL, to).trim();
        if (input.contains(from)) {
            return replace(input, from, to);
        }
        return input;
    }

    public static List<String> convertSearchWordToList(String input) {
        if (StringUtils.isBlank(input)) {
            return Collections.emptyList();
        }
        input = replace(input, BLANK_2, BLANK);
        input = replace(input, COMMA, BLANK);

        return Arrays.asList(input.split(BLANK));
    }
}

4.2 MyBatis XML mapping接入

<!-- 改成fulltext全文查询 -->
<if test="fulltextKeyStr != null and fulltextKeyStr != '' ">
    AND MATCH (a.`title`,a.`content`,a.`description`,a.`keywords`) AGAINST (${fulltextKeyStr} IN boolean MODE)
</if>
<if test="fulltextKeyStr == null or fulltextKeyStr == '' ">
......
</if>