新建操作

准备数据

CREATE TABLE `film` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键标识',
  `title` VARCHAR(100) NOT NULL COMMENT '电影名称',
  `release_year` INT COMMENT '发行年份',
  `director` VARCHAR(50) COMMENT '导演',
  `duration` INT COMMENT '片长(分钟)',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电影表';

INSERT INTO `film` (`id`,`title`,`release_year`,`director`,`duration`,`_openid`) 
VALUES 
(1,'Inception',2010,'Christopher Nolan',148,'1977683311217119233'),
(2,'The Shawshank Redemption',1994,'Frank Darabont',142,'1977683311217119233'),
(3,'The Godfather',1972,'Christopher Nolan',175,'1977683311217119233'),
(4,'The Godfather: Part II',1974,'Christopher Nolan',202,'1977683311217119233'),
(5,'The Godfather: Part III',1990,'Christopher Nolan',194,'1977683311217119233');

1. 更新数据，无返回值

Request

curl -i -X PATCH 'https://{{host}}/v1/rdb/rest/film?id=eq.1' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
--data '
  {
    "duration": 202
  }
'

Response

HTTP/1.1 204 No Content

Header:
    content-range: */1
    preference-applied: return=minimal

Body: (Empty)

返回说明：

• content-range: */1，表示受影响行数，此时表示更新了 1 条数据
• preference-applied: return=minimal，表示执行写数据时，采用了 return=minimal 的返回策略（写操作默认行为，即不产生返回体）

2. 更新数据时无条件，报错

Request

curl -i -X PATCH 'https://{{host}}/v1/rdb/rest/film' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
--data '
  {
    "duration": 202
  }
'

Response

HTTP/1.1 400 Bad Request

Header:
    content-length: 113
    content-type: application/json; charset=utf-8

Body: 
{
    "code": "BadApiRequest",
    "details": "",
    "hint": "",
    "message": "UPDATE requires a WHERE clause"
}

说明：

• 无条件更新属于不安全的操作，当请求发生时，系统会做保护，拒绝此类请求

3. 更新数据后返回

Request

curl -i -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=id,title,director,duration,release_year&id=in.(3,4,5)' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation' \
--data '
  {
    "director": "Francis Ford Coppola"
  }
'

Response

HTTP/1.1 200 OK

Header:
    content-length: 387
    content-range: */3
    content-type: application/json; charset=utf-8
    preference-applied: return=representation

Body: 
[
    {
        "director": "Francis Ford Coppola",
        "duration": 175,
        "id": 3,
        "release_year": 1972,
        "title": "The Godfather"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 202,
        "id": 4,
        "release_year": 1974,
        "title": "The Godfather: Part II"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

• 指定 Header Prefer: return=representation时，默认返回 更新后 的所有字段数据 select=*

说明：更新后返回数据，实际上是更新+查询，因此会产生 两次 db 请求。两次请求在同一事务内，因此不会产生脏读，但如果查询失败会导致更新也一起回滚，请注意。

4. 更新时指定复杂条件+排序+分页

Request

curl -i -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=*
  &id=in.(3,4,5)&and=(duration.gte.150, duration.lt.200)
  &or=(director.eq.Christopher Nolan, director.eq.Frank Darabont)
  &limit=1
  &order=id.desc' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation' \
--data '
  {
    "director": "Francis Ford Coppola"
  }
'

Response

HTTP/1.1 200 OK

Header:
    content-length: 142
    content-range: */1
    content-type: application/json; charset=utf-8
    preference-applied: return=representation

Body: 
[
    {
        "_openid": "1977683311217119233",
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

该请求近似于下列 sql:

UPDATE
  `film`
SET
  `director` = ?
WHERE
  (
    `id` IN (?, ?, ?) 
    AND (`director` = ? OR `director` = ?)
    AND (`duration` >= ? AND `duration` < ?)
  )
ORDER BY id DESC
LIMIT 1

args: ["Francis Ford", 3, 4, 5, "Coppola Christopher", "Nolan Frank Darabont", 150 200]

特殊场景

当执行更新操作且返回数据时，系统会根据主键来获取更新后的数据，但需要注意甄别以下场景：

场景一：单字段主键表

如果表的主键是单字段，那么在更新时，主键既作为更新条件又作为更新值时，更新会成功，但查询返回可能不符合预期！比如：

// 将 id=1 的电影信息主键改为 2

curl -i -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=title,release_year,duration&id=1' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation' \
--data '
  {
    "id": 2
  }
'

说明：此时可以正常更新数据，返回头的 Content-Range 会表名受影响行数 1，但是由于 id 既作为主键又作为更新值，所以查询结果会为空！

场景二：联合主键表

同单字段主键一样，如果联合主键其中任意一个字段既作为条件又作为更新值时，更新会成功，但查询返回可能不符合预期！比如：

// 表 user_activities 的联合主键为 (activity_date, activity_type)
// 将 user_activities 表中 activity_type 为 login 的记录的 activity_type 更新为 logout

curl -i -X PATCH 'https://{{host}}/v1/rdb/rest/user_activities?activity_type=eq.login' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation' \
--data '
  {
    "activity_type": "logout"
  }
'

说明：此时可以正常更新数据，返回头的 Content-Range 会表名受影响行数 1，但是由于 activity_type 是联合主键的一部分，且作为更新值，所以查询结果会为空！

场景三：无主键表

如果表没有主键，那么在更新时，不能指定请求头 Prefer: return=representation, 不支持无主键表更新且返回数据，会报错提示： table <table_name> has no primary key, cannot use update-with-return, suggest remove query

当移除 Prefer: return=representation 后，无主键表可以正常更新