mysql 全文索引

查看分词大小

SHOW VARIABLES LIKE 'ngram_token_size';

my.ini 中修改分词大小

ngram_token_size=1

创建表字段的全文索引

ALTER TABLE dun_stop_word ADD FULLTEXT INDEX ft_index_title (`title`) WITH PARSER ngram;

ALTER TABLE dun_missens_expression ADD FULLTEXT INDEX ft_index_misstatement (`misstatement`) WITH PARSER ngram;  

使用全文索引查询

SELECT 
    c1.cname AS c1Name,
    c2.cname AS c2Name,
    c3.cname AS c3Name,
    t1.`title`,
    MATCH( t1.`title` ) AGAINST ( '哈哈哈哈123' ) AS score
FROM
    `dun_stop_word` t1 
    left join dun_classify c1 on c1.id = t1.classisfy_first_id
    left join dun_classify c2 on c2.id = t1.classisfy_senc_id
    left join dun_classify c3 on c3.id = t1.classisfy_third_id
WHERE
    t1.`status` = 0 
    AND t1.`classisfy_first_id` = 3 
    AND MATCH ( t1.`title` ) AGAINST ( '哈哈哈哈123' IN NATURAL LANGUAGE MODE );

全文索引 MODE

Boolean MODE (布尔模式)
NATURAL LANGUAGE MODE （自然语言模式）

MySQL函数Locate的使用

Locate(str,sub) > 0，表示sub字符串包含str字符串；
Locate(str,sub) = 0，表示sub字符串不包含str字符串。

SELECT
            c1.cname AS c1Name,
            c2.cname AS c2Name,
            c3.cname AS c3Name,
            t1.`title`,
            MATCH( t1.`title` ) AGAINST ( #{content} ) AS score
        FROM
            `dun_stop_word` t1
            left join dun_classify c1 on c1.id = t1.classisfy_first_id
            left join dun_classify c2 on c2.id = t1.classisfy_senc_id
            left join dun_classify c3 on c3.id = t1.classisfy_third_id
        WHERE
            t1.`status` = 0
          AND t1.`classisfy_first_id` in
            <foreach collection="classifyList" item="id" open="(" close=")" separator=",">
               #{id}
            </foreach>
        AND locate( t1.`title`, #{content} ) > 0