mysql 一对多关系同步到es,刚好es需要的是一个字段对应逗号分割的值。这样就解决了mysql多表关联查询很慢的弊端。但是es中给如何把逗号分割的字符串做为查询条件呢，答案如下：先看一个简单的例子：
添加一个逗号分词
PUT info-flow-test4
{
"settings": {
"analysis": {
"analyzer": {
"my_anal": {
"type": "pattern",
"pattern": ","
}
}
}
},
"mappings": {
"properties": {
"id": {
"type": "keyword"
},
"content_id": {
"type": "long"
},
"content_type": {
"type": "integer"
},
"tags": {
"type": "text",
"analyzer": "my_anal"
}
}
}
}添加一个文档
POST /info-flow-test4/_doc
{
"tags": "1,2,33"
}
POST /info-flow-test4/_search
{
"query": {
"match": {
"tags": "2"
}
}
}
返回结果：
{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 0.2876821,
"hits" : [
{
"_index" : "info-flow-test4",
"_type" : "_doc",
"_id" : "MUFNDX8BciPKIrWg9Qc6",
"_score" : 0.2876821,
"_source" : {
"tags" : "1,2,33"
}
}
]
}
}
以上就是逗号分割的字段如何在es中检索出来。
项目中实战：通过canaladapter 同步一对多的关系到es, canaladapter配置文件如下：
dataSourceKey: defaultDS
destination: canalserver
outerAdapterKey: essync
groupId: g1
esMapping:
_index: wx_cp_user_contact_rel_all
_id: id
# upsert: true
# pk: id
sql: "select
CONCAT(a.corp_id, '_', a.follow_user_id, '_', a.external_userid) as id,
a.external_userid,
a.follow_user_id,
a.follow_user_remark,
a.follow_user_description ,
date_format( a.follow_user_createtime, '%Y-%m-%d %H:%I:%S') as follow_user_createtime,
a.follow_user_tags_group_name,
a.follow_user_tags_tag_name ,
a.follow_user_tags_type ,
a.follow_user_remark_corp_name ,
a.address,
a.corp_id ,
a.follow_user_remark_mobiles ,
a.follow_user_state ,
a.status ,
a. birthday,
date_format( a.link_time, '%Y-%m-%d %H:%I:%S') as link_time,
date_format( a.create_time, '%Y-%m-%d %H:%I:%S') as create_time,
a.follow_user_add_way,
b.name,
b.avatar,
b.type,
b.gender,
b.unionid,
b.position,
b.corp_name ,
b.corp_full_name,
b.external_profile,
b.phone,
d.broadband_umber,
e.mobile,
f.tags
from
wx_cp_user_contact_rel a
left join wx_cp_user_contact b on a.external_userid = b.external_userid and a.corp_id = b.corp_id
left join wx_cp_user_contact_ext_rel d on a.external_userid = d.external_userid and a.follow_user_id = d.user_id and a.corp_id = d.corp_id
left join wx_cp_user e on e.user_id = a.follow_user_id and e.corp_id = a.corp_id
left join (select ec_user_id ,user_id , GROUP_CONCAT(tag_id) as tags,corp_id from wx_cp_user_contact_tag_rel group by ec_user_id,user_id,corp_id) f on a.external_userid = f.ec_user_id and a.follow_user_id = f.user_id and a.corp_id = f.corp_id
"
# objFields:
# _labels: array:;
etlCondition: "where a.corp_id ={}"
commitBatch: 3000
es索引建立：文章来源：https://www.toymoban.com/news/detail-587505.html
PUT wx_cp_user_contact_rel_all/
{
"settings": {
"analysis": {
"analyzer": {
"my_anal": {
"type": "pattern",
"pattern": ","
}
}
}
},
"mappings" : {
"properties" : {
"address" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"avatar" : {
"type" : "keyword"
},
"birthday" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"broadband_umber" : {
"type" : "keyword"
},
"corp_full_name" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"corp_id" : {
"type" : "keyword"
},
"corp_name" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"create_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"develop_phone" : {
"type" : "keyword"
},
"external_profile" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"external_userid" : {
"type" : "keyword"
},
"follow_user_add_way" : {
"type" : "keyword"
},
"follow_user_createtime" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"follow_user_description" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"follow_user_id" : {
"type" : "keyword"
},
"follow_user_remark" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"follow_user_remark_corp_name" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"follow_user_remark_iop_mobiles" : {
"type" : "keyword"
},
"follow_user_remark_mobiles" : {
"type" : "keyword"
},
"follow_user_state" : {
"type" : "keyword"
},
"follow_user_tags_group_name" : {
"type" : "keyword"
},
"follow_user_tags_tag_name" : {
"type" : "keyword"
},
"follow_user_tags_type" : {
"type" : "keyword"
},
"gender" : {
"type" : "integer"
},
"is_has_tag" : {
"type" : "integer"
},
"job_number" : {
"type" : "keyword"
},
"link_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"mobile_update_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"name" : {
"type" : "keyword"
},
"phone" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"position" : {
"type" : "keyword"
},
"status" : {
"type" : "short"
},
"type" : {
"type" : "integer"
},
"unionid" : {
"type" : "keyword"
},
"unionid_mobile" : {
"type" : "keyword"
},
"update_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"user_code" : {
"type" : "keyword"
},
"mobile" : {
"type" : "keyword"
},
"tags": {
"type": "text",
"analyzer": "my_anal"
}
}
}
}项目中 员工给客户大的标签，是一对多的关系，但是为了在es中能对标签做完检索条件。不得已才有此方案，重点关注上次 tags 字段（完结）文章来源地址https://www.toymoban.com/news/detail-587505.html
到了这里，关于canaladapter 同步mysql(一对多关系)到es，es 添加逗号分词查询的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
