elasticsearch搜索映射Mysql

2019独角兽企业重金招聘Python工程师标准>>>

这里总结一下es完全映射mysql的搜索语句，希望能够帮到学习es的同学们。

实例

这里我们假设mysql表有7个字段: birthday, name, sex, height, address, province, city. 

实例1: mysql and + or

sql:

select * from table where birthday = '2018-05-17' and name = 'xx' 
 and (sex = 1 or height = 183 or address like 'chengdu%') 

es的搜索语句有两种方式

方式1: must + should

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "birthday": "2018-05-17"
          }
        },
        {
          "term": {
            "name": "xx"
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "sex": 1
                }
              },
              {
                "term": {
                  "height": 1
                }
              },
              {
                "wildcard": {
                  "address": {
                    "wildcard": "chengdu*"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

方式2: 用filter + should

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "birthday": "2018-05-17"
          }
        },
        {
          "term": {
            "name": "xx"
          }
        }
      ],
      "should": [
        {
          "term": {
            "sex": 1
          }
        },
        {
          "term": {
            "height": 1
          }
        },
        {
          "wildcard": {
            "address": {
              "wildcard": "chengdu*"
            }
          }
        }
      ]
    }
  }
}

 

实例2: mysql 子查询 + or

sql:

select * from 
 (select * from table where birthday > '2018-05-17' and birthday < '2018-05-17' and name = 'xx') 
where sex = 1 or height = 183

es的搜索语句是:

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "birthday": {
              "from": "2018-05-17",
              "include_lower": true,
              "include_upper": true,
              "to": null
            }
          }
        },
        {
          "range": {
            "birthday": {
              "from": null,
              "include_lower": true,
              "include_upper": true,
              "to": "2018-05-24"
            }
          }
        },
        {
          "match": {
            "name ": {
              "query": "xx"  // 这里不应该对应mysql的=, term应该是=. match用到的是es里面的分词,但mysql找不到与之匹配的.
            }
          }
        }
      ],
      "should": [
        {
          "term": {
            "sex": 1
          }
        },
        {
          "term": {
            "height ": 183
          }
        }
      ]
    }
  }
}

 

实例3: and 与 or 嵌套

sql:

select * from table where birthday = '2018-05-17' 
 and ( (sex = 1 and height = 183) or (province = 'chengdu' or city = 'chengdu'))

es搜索语句:

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "birthday": "2018-05-17"
          }
        }
      ],
      "must": {
        "bool": {
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "sex": 1
                    }
                  },
                  {
                    "term": {
                      "height": 183
                    }
                  }
                ]
              }
            },
            {
              "multi_match": {  // 可以用should代替
                "fields": [
                  "province",
                  "city"
                ],
                "query": "chengdu"
              }
            }
          ]
        }
      }
    }
  }
}

 

这三种情况，应该能应对绝大多数的条件查询场景。

值得注意的filter和query, 由于filter不包括评分查询, 所以更快,更稳定，结果容易缓存，但结果可能不准确。 query是评分查询（scoring queries）不仅仅要找出 匹配的文档，还要计算每个匹配文档的相关性，计算相关性使得它们比不评分查询费力的多。同时，查询结果并不缓存。

转载于:https://my.oschina.net/u/3707537/blog/1817896