select 获取多条记录

select 根据参数中指定的分页、过滤、排序，返回多条记录。

Luwak >= 3.0.0, asm.so >= 0.1.4

API

公共参数 metas 对象

名称	类型	描述
token	string	访问令牌，当需要授权才能访问时为必填
signature	string	签名
appKey	string	当需要签名时为必填
timestamp	string, number	时间戳，当校验时间误差范围时为必填

请求参数

名称	类型	描述
method	string	API 名称
params	object	API 参数

method 命名规范是 $entity.select

params 参数对象包含下列成员

filters 指定过滤条件的数组，该数组内的对象包含下列成员
- field 字段名
- operator 操作符
- value 字段值，或者嵌套的过滤条件
sorters 指定排序规则的数组，该数组内的对象包含下列成员
- field 字段名
- order 排序，升序 (asc) 或降序 (desc)
pagination 指定分页对象
- current 当前页，默认 1
- pageSize 每页限制多少条，默认 100

操作符列表

Operator	MySQL	说明
eq	`=`	相等
ne	`!=`	不相等
lt	`<`	小于
gt	`>`	大于
lte	`<=`	小于等于
gte	`>=`	大于等于
in	IN	包含在数组中
nin	NOT IN	不包含在数组中
contains	LIKE '%?%'	包含
ncontains	NOT LIKE '%?%'	不包含
containss	LIKE BINARY '%?%'	包含，区分大小写
ncontainss	NOT LIKE BINARY '%?%'	不包含，区分大小写
between	BETWEEN ? AND ?	在...之间
nbetween	NOT BETWEEN ? AND ?	不介于两者之间
null	IS NULL	空
nnull	IS NOT NULL	非空
startswith	LIKE '?%'	以...开始
nstartswith	NOT LIKE '?%'	不以...开始
startswiths	LIKE BINARY '?%'	以...开始，区分大小写
nstartswiths	NOT LIKE BINARY '?%'	不以...开始，区分大小写
endswith	LIKE '%?'	以...结尾
nendswith	NOT LIKE '%?'	不以...结尾
endswiths	LIKE BINARY '%?'	以...结尾，区分大小写
nendswiths	NOT LIKE BINARY '%?'	不以...结尾，区分大小写
or	OR	逻辑或
and	AND	逻辑与

响应结果

返回一个对象。

{
  "data": [], // 包含多个数据的数组
  "total": 0 // 找到多少条数据
}

示例

场景描述

获取品牌表中主键 id 大于 1 且小于 2 的多条数据。

请求

{
  "jsonrpc": "2.0",
  "method": "smartPanda.mdh.brand.select",
  "params": {
    "filters": [
      {
        "field": "id",
        "operator": "gt",
        "value": 1
      },
      {
        "field": "id",
        "operator": "lt",
        "value": 4
      }
    ],
    "pagination": {
      "current": 1,
      "pageSize": 25
    },
    "sorters": [
      {
        "field": "id",
        "order": "desc"
      }
    ]
  },
  "metas": {
    "token": "44ea16b7-3aa1-41a5-81da-8ee9d2b840c2",
    "signature": "d92fc0904f2ca07c1482335c08cbea9c",
    "appKey": "NSEA4ETIpBlyB2l9",
    "timestamp": 1740605831887
  },
  "id": "5d311b2a-f9ac-4edd-9e54-3530756149e2"
}

响应

{
  "jsonrpc": "2.0",
  "result": {
    "data": [
      {
        "id": 3,
        "name": "Channel"
      },
      {
        "id": 2,
        "name": "Louis Vuitton"
      }
    ],
    "total": 2
  },
  "id": "5d311b2a-f9ac-4edd-9e54-3530756149e2"
}

实体定义

smart_panda 库中 brand 表的定义如下

CREATE TABLE `brand`
(
    `id`   int AUTO_INCREMENT,
    `name` varchar(20),
    PRIMARY KEY (`id`)
);

entity 名称是 smartPanda.mdh.brand

实际执行的 SQL

Prepare   SELECT * FROM smart_panda.brand WHERE id > ? AND id < ? ORDER BY id DESC LIMIT 25 OFFSET 0
Execute   SELECT * FROM smart_panda.brand WHERE id > 1 AND id < 4 ORDER BY id DESC LIMIT 25 OFFSET 0

Prepare   SELECT COUNT(*) FROM smart_panda.brand WHERE id > ? AND id < ?
Execute   SELECT COUNT(*) FROM smart_panda.brand WHERE id > 1 AND id < 4

示例2

场景2描述

查询条件

入职日期在 "1990-01-01" 至 "1990-12-31"之间，或者在 "1985-01-01" 到 "1985-12-31" 之间
男性

排序规则

按入职日期由近到远排序

分页

分页，每页 5 条，取第 2 页

请求2

{
  "jsonrpc": "2.0",
  "method": "hrm.public.employees.search",
  "params": {
    "filters": [
      {
        "operator": "and",
        "value": [
          {
            "operator": "or",
            "value": [
              {
                "field": "hireDate",
                "operator": "between",
                "value": ["1990-01-01", "1990-12-31"]
              },
              {
                "field": "hireDate",
                "operator": "between",
                "value": ["1985-01-01", "1985-12-31"]
              }
            ]
          },
          {
            "field": "gender",
            "operator": "eq",
            "value": "M"
          }
        ]
      }
    ],
    "sorters": [
      {
        "field": "hireDate",
        "order": "desc"
      }
    ],
    "pagination": {
      "current": 2,
      "pageSize": 5
    }
  },
  "id": "client-unique-request-id"
}

响应2

{
  "jsonrpc": "2.0",
  "result": {
    "data": [
      {
        "birthDate": "1962-11-19",
        "empNo": 10086,
        "firstName": "Somnath",
        "gender": "M",
        "hireDate": "1990-02-16",
        "lastName": "Foote"
      },
      {
        "birthDate": "1954-09-16",
        "empNo": 10096,
        "firstName": "Jayson",
        "gender": "M",
        "hireDate": "1990-01-14",
        "lastName": "Mandell"
      },
      {
        "birthDate": "1963-09-09",
        "empNo": 10082,
        "firstName": "Parviz",
        "gender": "M",
        "hireDate": "1990-01-03",
        "lastName": "Lortz"
      },
      {
        "birthDate": "1956-12-13",
        "empNo": 10029,
        "firstName": "Otmar",
        "gender": "M",
        "hireDate": "1985-11-20",
        "lastName": "Herbst"
      },
      {
        "birthDate": "1959-04-07",
        "empNo": 10064,
        "firstName": "Udi",
        "gender": "M",
        "hireDate": "1985-11-20",
        "lastName": "Jansch"
      }
    ],
    "total": 20
  },
  "id": "client-unique-request-id"
}

2实体定义

hrm 库中 employees 表的定义如下

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

entity 名称是 hrm.public.employees

2实际执行的 SQL

SELECT * FROM hrm.employees WHERE ((hire_date BETWEEN '1990-01-01' AND '1990-12-31' OR hire_date BETWEEN '1985-01-01' AND '1985-12-31') AND gender = 'M') ORDER BY hire_date DESC LIMIT 5 OFFSET 5

SELECT COUNT(*) as total FROM hrm.employees WHERE ((hire_date BETWEEN '1990-01-01' AND '1990-12-31' OR hire_date BETWEEN '1985-01-01' AND '1985-12-31') AND gender = 'M') LIMIT 1

API​

公共参数 metas 对象​

请求参数​

响应结果​

示例​

场景描述​

请求​

响应​

实体定义​

实际执行的 SQL​

示例2​

场景2描述​

请求2​

响应2​

2实体定义​

2实际执行的 SQL​

API