数据库配置修改：
|
1. 设置ngram_token_size = 1
2. 设置innodb_ft_server_stopword_table
2.1 创建自定义停止字规则
CREATE TABLE bbavip_stopwords(value VARCHAR(30)) ENGINE = INNODB;
2.2 设置全局使用指定的停止字规则
SET GLOBAL innodb_ft_server_stopword_table = 'bbavip/bbavip_stopwords';
3. 重建fulltext index
4. 规划指定表的fulltext index
4.1
SET GLOBAL innodb_optimize_fulltext_only=ON;
4.2
OPTIMIZE TABLE product_base;
4.3
SET GLOBAL innodb_ft_aux_table = 'fulltextsearch/product_base';
4.4
select * from information_schema.INNODB_FT_INDEX_TABLE where word = 'a';
4.5
SET GLOBAL innodb_optimize_fulltext_only=OFF;
|
1. 合并fullTextSearch分支
2.新增Fulltext index
3. 新增类 继承 Against 私有 静态 抽象类
|
public static class MatchProductBaseInBoolean extends Against {
private static String matchParamDefault = "skuid";
public MatchProductBaseInBoolean(String keyword) {
super(null, keyword, matchParamDefault);
}
public MatchProductBaseInBoolean(String title, String keyword) {
super(title, keyword, matchParamDefault);
}
}
|
3.1 配置 matchParamDeafult 为 要全文索引的列。多个列用","隔开，如(skuid,name,bar_code)
3.2 根据需要实现Agaist构造方法。
3.2.1 title 用来做连表查询时使用。
3.2.2 keyword 作为查询关键词
4. 对外暴露工厂方法
|
public static String buildMatchInBoolean(String matchParamList, String keyWord) {
return new MatchInBoolean(matchParamList, keyWord).toString();
}
public static String buildMatchInBoolean(String tableAlias, String matchParamList, String keyWord) {
return new MatchInBoolean(tableAlias, matchParamList, keyWord).toString();
}
public static String buildMatchInBooleanWithClazz(String keyWord, Class<? extends Against> clazz) {
try {
return clazz.getConstructor(String.class).newInstance(keyWord).toString();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return "";
}
public static String buildMatchInBooleanWithClazz(String tableAlias, String keyWord, Class<? extends Against> clazz) {
try {
return clazz.getConstructor(String.class, String.class).newInstance(tableAlias, keyWord).toString();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return "";
}
|
4.1 buildMatchInBoolean 适用于在嵌套查询中在父查询中使用子查询的全文索引的情况，或无法定义Against衍生类的情况
|
Long storeBaseId = (Long) map.get("storeBaseId");
String keyWord = (String) map.get("keyWord");
StringBuffer sql = new StringBuffer();
sql.append("SELECT c.*, e.mobile FROM
" +
"(
" +
" SELECT a.id, a.first_name, a.last_name,
" +
" a.gender, a.age , a.car_amount, a.partner_base_id,
" +
" b.car_times, b.pay_expenses,
" +
" a.channel ,
" +
" b.arrive_time, b.car_id FROM car_owner AS a INNER JOIN owner_car_relevance as b
" +
" WHERE a.id = b.car_owner_id
" +
" AND
" +
" b.store_base_id = #{storeBaseId}) as c LEFT JOIN user_car_relation as d ON c.car_id = d.car_id
" +
" LEFT JOIN car_owner_user AS e ON d.user_id = e.id
");
if(StringUtils.isNotEmpty(keyWord)){
sql.append(FullTextUtils.buildMatchInBoolean("c", "first_name,last_name", keyWord));
}
sql.append("ORDER BY id DESC
");
return sql.toString();
|
4.2 buildMatchInBooleanWithClazz 适用于可以定义Against衍生类的情况。本质上依旧是buildMatchInBoolean方法的调用，只是通过反射处理。
由于多用于列表查询，异常没有向上抛出，调用方需要确保数据的正确性。
|
if (StringUtils.isNotEmpty(skuid)) {
WHERE(FullTextUtils.buildMatchInBooleanWithClazz(skuid, FullTextUtils.MatchProductBaseInBoolean.class));
}else {
if (StringUtils.isNotEmpty(orderParam)) {
ORDER_BY(orderParam);
} else {
ORDER_BY("id desc");
}
}
|
5. 使用全文索引时尽量避免其他因素的排序，全文索引会有自己的相关性值。添加其他因素的排序时，返回结果集的顺序会被打乱。