查询数据

通过 HTTP RESTful API 对 MySQL 数据库进行查询操作。具体接口详情请参考 HTTP API/MySQL数据库

基础语法

GET https://your-envId.api.tcloudbasegateway.com/v1/rdb/rest/:table
Authorization: Bearer <access_token>
Content-Type: application/json

💡 提示：access_token 请参考 获取AccessToken

基础查询

# 查询 film表 所有数据
curl -X GET 'https://{{host}}/v1/rdb/rest/film' \
-H 'Authorization: Bearer <access_token>'

💡 注意：返回的数据是所有字段数据，默认为/table?select=*

请求返回示例

HTTP/1.1 200 OK
Header:
    content-length: 1037
    content-type: application/json; charset=utf-8
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "陈凯歌",
        "duration": 171,
        "id": 1,
        "release_year": 1993,
        "title": "霸王别姬"
    },
    // ... 共 15 条数据
]

指定字段返回

# 指定 film表 返回字段
curl -X GET 'https://{{host}}/v1/rdb/rest/film?select=title,director,release_year' \
-H 'Authorization: Bearer <access_token>'

请求返回示例

HTTP/1.1 200 OK
Header:
    content-length: 1080
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director": "陈凯歌",
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "director": "姜文",
        "release_year": 2010,
        "title": "让子弹飞"
    }
    // ... 共 15 条数据
]

条件查询

• 具体操作符参考：操作符
• 具体逻辑运算符参考：逻辑运算符

# 查询导演为 Christopher Nolan 的电影
curl -X GET 'https://{{host}}/v1/rdb/rest/film?director=eq.Christopher Nolan' \
-H 'Authorization: Bearer <access_token>'

# 查询 2000 年后上映的电影，电影时长不超过 120 分钟
curl -X GET 'https://{{host}}/v1/rdb/rest/film?release_year=gte.2000&duration=lt.120' \
-H 'Authorization: Bearer <access_token>'

# 查询 2000 年后上映的电影，导演是 Christopher Nolan 或者 姜文
curl -X GET 'https://{{host}}/v1/rdb/rest/film?or=(director.eq.Christopher Nolan, director.eq.姜文)' \
-H 'Authorization: Bearer <access_token>'

查询导演为 Christopher Nolan 的电影

HTTP/1.1 200 OK
Header:
    content-length: 260
    content-type: application/json; charset=utf-8
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 169,
        "id": 13,
        "release_year": 2014,
        "title": "Interstellar"
    },
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 148,
        "id": 14,
        "release_year": 2010,
        "title": "Inception"
    }
]

排序和分页

# 查询 2000 年前的电影，展示播放时长最长的 top3 电影
curl -X GET 'https://{{host}}/v1/rdb/rest/film?order=duration.desc&limit=3' \
-H 'Authorization: Bearer <access_token>'

请求返回示例

HTTP/1.1 200 OK
Header:
    content-length: 266
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director": "James Cameron",
        "duration": 194,
        "release_year": 1997,
        "title": "Titanic"
    },
    {
        "director": "陈凯歌",
        "duration": 171,
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "director": "Frank Darabont",
        "duration": 142,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    }
]

计数查询

# 查询 2000 年后的电影和总数
curl -X GET 'https://{{host}}/v1/rdb/rest/film?select=*&release_year=gte.2000' \
-H 'Authorization: Bearer <access_token>' \
-H 'Prefer: count=exact'

Request

• 指定 Header Prefer: count=exact 时，返回符合条件的数据总数

Response

• content-range: 0-9/10 表示本次查询返回的数据范围，共计 10 条数据，0表示无偏移，9 表示最后一条数据的索引，10 表示总数

请求返回示例

HTTP/1.1 200 OK
Header:
    content-length: 853
    content-range: 0-9/10
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director": "姜文",
        "duration": 132,
        "release_year": 2010,
        "title": "让子弹飞"
    },
    {
        "director": "郭帆",
        "duration": 125,
        "release_year": 2019,
        "title": "流浪地球"
    },
    {
        "director": "吴京",
        "duration": 123,
        "release_year": 2017,
        "title": "战狼2"
    }

    // ...等 共计 10 条数据
]

强制返回对象格式

当前数据返回都是以数组的形式返回，如果需要强制返回对象格式，可以指定 Header Accept: application/vnd.pgrst.object+json

⚠️ 注意：当明确只有一条数据返回时才能使用此方式，可以加上 limit=1 用来限制返回的数据条数

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*&id=eq.1&limit=1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Accept: application/vnd.pgrst.object+json'

请求返回示例

HTTP/1.1 200 OK
Header:
    content-length: 121
    content-type: application/vnd.pgrst.object+json
Body:
{
    "director": "陈凯歌",
    "duration": 171,
    "id": 1,
    "release_year": 1993,
    "title": "霸王别姬",
    "_openid": "1977683311217119233"
}

字面量查询

MySQL 允许创建带有空格等特殊字符的表和字段，此时 url 请求就需要做字面量处理(使用双引号标记)避免和特殊字符、关键字冲突。

假设有如下表结构：

• 表名： my table
• 字段名： full name、email address

curl -i -X 'GET http://{{host}}/v1/rdb/rest/my table?select="full name","email address"' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <access_token>' \

请求返回示例

HTTP/1.1 200 OK
Header:
    content-length: 61
    content-type: application/json; charset=utf-8
Body:
[
    {
        "email address": "john@example.com",
        "full name": "John Doe"
    }
]

操作符

操作符	说明	示例
eq	等于	?id=eq.1
neq	不等于	?id=neq.1
gt	大于	?age=gt.18
gte	大于等于	?age=gte.18
lt	小于	?age=lt.35
lte	小于等于	?age=lte.35
like	模糊匹配	?name=like.%value%
in	包含	?id=in.[1,2,3]
is	为空	?age=is.null
and	逻辑与	?and=(age.gt.18,age.lt.35)
or	逻辑或	?or=(id.eq.1,name.like.%value%)
not	逻辑非	?age=not.eq.18

支持的逻辑运算符

运算符	说明	Mysql 等效操作符	示例
and	逻辑与	and	/table?id=eq.1&age=gt.18 以及 /table?select=*&and=(age.gt.18,age.lt.35)
or	逻辑或	or	/table?select=*&or=(id.eq.1,name.like.%value%)
not	逻辑非	not	/table?select=*&age=not.eq.18&gender=not.is.null&isTrue=not.is.true