在不不引入ES情况下，利用MySQL实现模糊匹配
我们的主要目标是设计并实现一个高效且易于管理的公司申请审批流程。在这个流程中，我们将重点关注两种关键角色：商务角色和管理员角色。这两种角色在流程中各自承担着不同的职责和权限，以确保申请的准确性和审批的及时性。
核心流程简要概述为：商务角色申请添加公司时可能仅提供简称，为避免重复，管理员在审批前需核实以往记录，确保不添加重复的公司全称。
针对所描述的业务场景，从技术实现的角度出发，我们需要关注以下几个关键功能点：
1. 分词处理：
利用现有的分词器工具，将输入的文本内容（如公司名称或简称）进行分词处理，以便后续与数据库中的数据进行匹配。
2. 数据匹配：
将分词后的结果与数据库中已有的公司数据进行比对。这通常涉及到字符串搜索和匹配算法，以确保能够准确找到与输入内容相关的公司记录。
3. 匹配度排序：
根据匹配结果的相关性或相似度进行排序。这可能涉及到计算文本相似度的算法，如基于TF-IDF的相似度计算、余弦相似度等，以确保最相关的公司记录能够排在前面。
4. 性能优化：
由于可能涉及大量数据的搜索和匹配，因此还需要考虑如何优化查询性能，如使用索引、缓存等技术手段来减少数据库查询次数，提高整体系统的响应速度。
在整个实现过程中，分词功能是基础，而数据匹配和匹配度排序则是核心。通过合理的技术选型和算法设计，我们可以有效地实现这一业务需求，为用户提供高效、准确的搜索结果。
在为本系统选择模糊匹配技术时，我们考虑了两种方案：一是引入Elasticsearch（ES）作为专门的搜索引擎，二是利用现有的MySQL数据库功能进行实现。鉴于本系统规模较小，引入ES将增加额外的成本，包括硬件资源、数据同步的复杂性以及系统维护的困难度。因此，我们倾向于使用MySQL已有的功能来实现模糊匹配。
MySQL提供了三种主要的模糊搜索方式：
LIKE匹配：这种方式要求模式串与整个目标字段完全匹配或部分匹配（通过通配符%和_），但对于复杂的模糊搜索需求，其能力有限。
REGEXP正则匹配：使用正则表达式进行匹配，可以实现任意模式的搜索，灵活性高。但相较于全文索引，其执行效率可能稍低。
Fulltext全文索引：适用于CHAR, VARCHAR, TEXT类型的列，能够执行高效的模糊搜索。然而，其可定制性相对较差，不支持某些复杂的匹配查询。
针对当前业务场景，我们对上述技术进行了优劣分析：
LIKE匹配：由于其对模式串与目标字段的匹配要求较为严格，无法满足我们复杂的模糊搜索需求，因此不予考虑。
Fulltext全文索引：虽然其搜索效率高，但可定制性不足，不适合本场景中的任意模式匹配需求。
REGEXP正则匹配：尽管其执行效率可能稍低于全文索引，但能够灵活实现任意模式的匹配，且考虑到本系统中的记录数量相对较少，对效率的要求不是非常高，因此可以接受其稍低的执行效率。
综上所述，结合本系统的实际情况和需求，我们决定采用MySQL的REGEXP正则匹配来实现模糊匹配的需求。这种方式既能够满足我们的搜索需求，又能够保持系统的简洁性和维护的方便性。
整个处理流程的核心逻辑包括三个关键步骤：提取公司名称关键信息、分词、以及匹配。
提取公司名称关键信息
此步骤的目的是对输入的公司名称进行清洗，去除不相关的无用信息，从而保留公司名称的核心部分。无用信息通常包括地名、圆括号内的内容，以及诸如“集团”、“股份”、“有限”等后缀。通过编写相应的逻辑或正则表达式，可以有效地从原始输入中提取出公司名称的关键部分。
匹配前处理公司名称
/**
* 匹配前去除公司名称的无意义信息
* @param targetCompanyName
* @return
*/
private String formatCompanyName(String targetCompanyName){
String regex = "(?<province>[^省]+自治区|.*?省|.*?行政区|.*?市)" +
"?(?<city>[^市]+自治州|.*?地区|.*?行政单位|.+盟|市辖区|.*?市|.*?县)" +
"?(?<county>[^(区|市|县|旗|岛)]+区|.*?市|.*?县|.*?旗|.*?岛)" +
"?(?<village>.*)";
Matcher matcher = Pattern.compile(regex).matcher(targetCompanyName);
while(matcher.find()){
String province = matcher.group("province");
log.info("province:{}",province);
if (StringUtils.isNotBlank(province) && targetCompanyName.contains(province)){
targetCompanyName = targetCompanyName.replace(province,"");
}
log.info("处理完省份的公司名称:{}",targetCompanyName);
String city = matcher.group("city");
log.info("city:{}",city);
if (StringUtils.isNotBlank(city) && targetCompanyName.contains(city)){
targetCompanyName = targetCompanyName.replace(city,"");
}
log.info("处理完城市的公司名称:{}",targetCompanyName);
String county = matcher.group("county");
log.info("county:{}",county);
if (StringUtils.isNotBlank(county) && targetCompanyName.contains(county)){
targetCompanyName = targetCompanyName.replace(county,"");
}
log.info("处理完区县级的公司名称:{}",targetCompanyName);
}
String[][] address = AddressUtil.ADDRESS;
for (String [] city: address) {
for (String b : city ) {
if (targetCompanyName.contains(b)){
targetCompanyName = targetCompanyName.replace(b, "");
}
}
}
log.info("处理后的公司名称:{}",targetCompanyName);
return targetCompanyName;
}地名工具类
public class AddressUtil {
public static final String[][] ADDRESS = {
{"北京"},
{"天津"},
{"安徽","安庆","蚌埠","亳州","巢湖","池州","滁州","阜阳","合肥","淮北","淮南","黄山","六安","马鞍山","宿州","铜陵","芜湖","宣城"},
{"澳门"},
{"香港"},
{"福建","福州","龙岩","南平","宁德","莆田","泉州","厦门","漳州"},
{"甘肃","白银","定西","甘南藏族自治州","嘉峪关","金昌","酒泉","兰州","临夏回族自治州","陇南","平凉","庆阳","天水","武威","张掖"},
{"广东","潮州","东莞","佛山","广州","河源","惠州","江门","揭阳","茂名","梅州","清远","汕头","汕尾","韶关","深圳","阳江","云浮","湛江","肇庆","中山","珠海"},
{"广西","百色","北海","崇左","防城港","贵港","桂林","河池","贺州","来宾","柳州","南宁","钦州","梧州","玉林"},
{"贵州","安顺","毕节地区","贵阳","六盘水","黔东南苗族侗族自治州","黔南布依族苗族自治州","黔西南布依族苗族自治州","铜仁地区","遵义"},
{"海南","海口","三亚","直辖县级行政区划"},
{"河北","保定","沧州","承德","邯郸","衡水","廊坊","秦皇岛","石家庄","唐山","邢台","张家口"},
{"河南","安阳","鹤壁","焦作","开封","洛阳","漯河","南阳","平顶山","濮阳","三门峡","商丘","新乡","信阳","许昌","郑州","周口","驻马店"},
{"黑龙江","大庆","大兴安岭地区","哈尔滨","鹤岗","黑河","鸡西","佳木斯","牡丹江","七台河","齐齐哈尔","双鸭山","绥化","伊春"},
{"湖北","鄂州","恩施土家族苗族自治州","黄冈","黄石","荆门","荆州","十堰","随州","武汉","咸宁","襄樊","孝感","宜昌"},
{"湖南","长沙","常德","郴州","衡阳","怀化","娄底","邵阳","湘潭","湘西土家族苗族自治州","益阳","永州","岳阳","张家界","株洲"},
{"吉林","白城","白山","长春","吉林","辽源","四平","松原","通化","延边朝鲜族自治州"},
{"江苏","常州","淮安","连云港","南京","南通","苏州","宿迁","泰州","无锡","徐州","盐城","扬州","镇江"},
{"江西","抚州","赣州","吉安","景德镇","九江","南昌","萍乡","上饶","新余","宜春","鹰潭"},
{"辽宁","鞍山","本溪","朝阳","大连","丹东","抚顺","阜新","葫芦岛","锦州","辽阳","盘锦","沈阳","铁岭","营口"},
{"内蒙古","阿拉善盟","巴彦淖尔","包头","赤峰","鄂尔多斯","呼和浩特","呼伦贝尔","通辽","乌海","乌兰察布","锡林郭勒盟","兴安盟"},
{"宁夏回族","固原","石嘴山","吴忠","银川","中卫"},
{"青海","果洛藏族自治州","海北藏族自治州","海东地区","海南藏族自治州","海西蒙古族藏族自治州","黄南藏族自治州","西宁","玉树藏族自治州"},
{"山东","滨州","德州","东营","菏泽","济南","济宁","莱芜","聊城","临沂","青岛","日照","泰安","威海","潍坊","烟台","枣庄","淄博"},
{"山西","长治","大同","晋城","晋中","临汾","吕梁","朔州","太原","忻州","阳泉","运城"},
{"陕西","安康","宝鸡","汉中","商洛","铜川","渭南","西安","咸阳","延安","榆林"},
{"上海"},
{"四川","阿坝藏族羌族自治州","巴中","成都","达州","德阳","甘孜藏族自治州","广安","广元","乐山","凉山彝族自治州","泸州","眉山","绵阳","内江","南充","攀枝花","遂宁","雅安","宜宾","资阳","自贡"},
{"西藏","阿里地区","昌都地区","拉萨","林芝地区","那曲地区","日喀则地区","山南地区"},
{"新疆维吾尔","阿克苏地区","阿勒泰地区","巴音郭楞蒙古自治州","博尔塔拉蒙古自治州","昌吉回族自治州","哈密地区","和田地区","喀什地区","克拉玛依","克孜勒苏柯尔克孜自治州","塔城地区","吐鲁番地区","乌鲁木齐","伊犁哈萨克自治州","直辖县级行政区划"},
{"云南","保山","楚雄彝族自治州","大理白族自治州","德宏傣族景颇族自治州","迪庆藏族自治州","红河哈尼族彝族自治州","昆明","丽江","临沧","怒江僳僳族自治州","普洱","曲靖","文山壮族苗族自治州","西双版纳傣族自治州","玉溪","昭通"},
{"浙江","杭州","湖州","嘉兴","金华","丽水","宁波","衢州","绍兴","台州","东港","舟山"},
{"重庆"},
{"台湾","台北","高雄","基隆","台中","台南","新竹","嘉义"},
};
}分词相关代码
pom文件：引入IK分词器相关依赖
<!-- ikAnalyzer 中文分词器 --> <dependency> <groupId>com.janeluo</groupId> <artifactId>ikanalyzer</artifactId> <version>2012_u6</version> <exclusions> <exclusion> <groupId>org.apache.lucene</groupId> <artifactId>lucene-core</artifactId> </exclusion> <exclusion> <groupId>org.apache.lucene</groupId> <artifactId>lucene-queryparser</artifactId> </exclusion> <exclusion> <groupId>org.apache.lucene</groupId> <artifactId>lucene-analyzers-common</artifactId> </exclusion> </exclusions> </dependency> <!-- lucene-queryParser 查询分析器模块 --> <dependency> <groupId>org.apache.lucene</groupId> <artifactId>lucene-queryparser</artifactId> <version>7.3.0</version>
IKAnalyzerSupport类：用于配置分词器
@Slf4j
public class IKAnalyzerSupport {
/**
* IK分词
* @param target
* @return
*/
public static List<String> iKSegmenterToList(String target) throws Exception {
if (StringUtils.isEmpty(target)){
return new ArrayList();
}
List<String> result = new ArrayList<>();
StringReader sr = new StringReader(target);
// false:关闭智能分词 (对分词的精度影响较大)
IKSegmenter ik = new IKSegmenter(sr, true);
Lexeme lex;
while((lex=ik.next())!=null) {
String lexemeText = lex.getLexemeText();
result.add(lexemeText);
}
return result;
}
}
ServiceImpl类：进行分词处理
/**
* 对目标公司名称进行分词
* @param targetCompanyName
* @return
*/
private String splitWord(String targetCompanyName){
log.info("对处理后端公司名称进行分词");
List<String> splitWord = new ArrayList<>();
String result = targetCompanyName;
try {
splitWord = iKSegmenterToList(targetCompanyName);
result = splitWord.stream().map(String::valueOf).distinct().collect(Collectors.joining("|")) ;
log.info("分词结果:{}",result);
} catch (Exception e) {
log.error("分词报错:{}",e.getMessage());
}
return result;
}匹配
ServiceImpl类：匹配核心代码
public JsonResult matchCompanyName(CompanyDTO companyDTO, String accessToken, String localIp) {
// 对公司名称进行处理
String sourceCompanyName = companyDTO.getCompanyName();
String targetCompanyName = sourceCompanyName;
log.info("处理前公司名称:{}",targetCompanyName);
// 处理圆括号
targetCompanyName = targetCompanyName.replaceAll("[（]|[）]|[(]|[)]","");
// 处理公司相关关键词
targetCompanyName = targetCompanyName.replaceAll("[(集团|股份|有限|责任|分公司)]", "");
if (!targetCompanyName.contains("银行")){
// 去除行政区域
targetCompanyName = formatCompanyName(targetCompanyName);
}
// 分词
String splitCompanyName = splitWord(targetCompanyName);
// 匹配
List<Company> matchedCompany = companyRepository.queryMatchCompanyName(splitCompanyName,targetCompanyName);
List<String> result = new ArrayList();
for (Company companyInfo : matchedCompany) {
result.add(companyInfo.getCompanyName());
if (companyDTO.getCompanyId().equals(companyInfo.getCompanyId())){
result.remove(companyInfo.getCompanyName());
}
}
return JsonResult.successResult(result);
}Repository类：编写SQL语句
/** * 模糊匹配公司名称 * @param companyNameRegex 分词后的公司名称 * @param companyName 分词前的公司名称 * @return */ @Query(value = "SELECT * FROM company WHERE isDeleted = '0' and companyName REGEXP ?1 ORDER BY length(REPLACE(companyName,?2,''))/length(companyName) ", nativeQuery = true) List<Company> queryMatchCompanyName(String companyNameRegex,String companyName);
为了按照匹配度对公司名称进行排序，我们可以采用一种基于关键词出现次数的计算方法。首先，我们需要确定关键词在companyName字段中出现的次数。这可以通过计算companyName的长度与去除关键词后companyName的长度之差来实现。
MySQL
网站建设
网站开发
小程序开发
阅读排行
-
1. 几行代码就能实现Html大转盘抽奖
大转盘抽奖是网络互动营销的一种常见形式，其通过简单易懂的界面设计，让用户在游戏中体验到乐趣，同时也能增加商家与用户之间的互动。本文将详细介绍如何使用HTML,CSS和JavaScript来实现大转盘抽奖的功能。
查看详情 -
2. 浙江省同区域公司地址变更详细流程
提前准备好所有需要的资料，包含：房屋租赁合同、房产证、营业执照正副本、代理人身份证正反面、承诺书（由于我们公司其中一区域已有注册另外一公司，所以必须需要承诺书）
查看详情 -
3. 微信支付商户申请接入流程
微信支付，是微信向有出售物品/提供服务需求的商家提供推广销售、支付收款、经营分析的整套解决方案，包括多种支付方式，如JSAPI支付、小程序支付、APP支付H5支付等支付方式接入。
查看详情 -
4. 阿里云域名ICP网络备案流程
根据《互联网信息服务管理办法》以及《非经营性互联网信息服务备案管理办法》，国家对非经营性互联网信息服务实行备案制度，对经营性互联网信息服务实行许可制度。
查看详情 -
5. 微信小程序申请注册流程
微信小程序注册流程与微信公众号较为相似，同时微信小程序支持通过已认证的微信公众号进行注册申请，无需进行单独认证即可使用，同一个已认证微信公众号可同时绑定注册多个小程序。
查看详情
