文档

高级查询语法

更新时间:

为了满足更多的查询检索需求,SELECT查询语句中支持search_query关键字,使用与Lucene(全文检索引擎工具包)查询语法相似。本文介绍通过search_query关键字查询数据的使用示例。

前提条件

语法

SELECT selectors
  FROM table
  WHERE (search_query = 'search_expression')
  [ LIMIT n ]
  [ ORDER BY column_name ] ;

示例

  • OR逻辑查询

    SELECT * FROM search_table WHERE search_query = 'name:陈女士 age:[40 TO 60]';

    与以下语句功能相同。

    SELECT * FROM search_table WHERE name='陈女士' OR (age >= 40 AND age <= 60);

    返回结果:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 28      | 陈女士 | 36  | F      | 深圳市南山区 | a***@example.net | 深圳 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • AND逻辑查询

    SELECT * FROM search_table WHERE search_query = '+name:张先生 +age:[10 TO 40]';

    与以下语句功能相同。

    SELECT * FROM search_table WHERE name='张先生' AND (age >= 10 AND age <= 40);

    返回结果:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 聚合统计

    SELECT count(*) FROM search_table WHERE search_query = 'age:[10 TO 40]';

    返回结果:

    +----------+
    | COUNT(*) |
    +----------+
    | 4        |
    +----------+
  • 分词查询

    SELECT * FROM search_table WHERE search_query='address:北京';

    返回结果:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 结果排序。如果需要按照指定的age字段进行排序,请执行以下语句。

    SELECT * FROM search_table WHERE search_query='{"q":"address:杭州", "sort":"age asc"}';
    说明

    上述语句中qsort为系统关键字,不需要修改。

    返回结果:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 20      | 王先生 | 28  | M      | 杭州市滨江区 | a***@example.net | 杭州 |
    | 6       | 李先生 | 32  | M      | 杭州市余杭区 | a***@example.net | 杭州 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • Elasticsearch兼容语法。

    SELECT * FROM search_table WHERE search_query='{"query": {
        "bool": {
          "must": {
            "match": {
              "address": "北京"
            }
          }
        }
      }
    }';

    与以下语句功能相同。

    SELECT * FROM search_table WHERE address='北京';

    返回结果:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 本页导读 (1)