查看分词大小
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
