删除操作

准备数据

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 DELETE 'https://{{host}}/v1/rdb/rest/film?id=eq.4' \
-H 'Authorization: Bearer <This is a fake token, please use yours>'

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 DELETE 'https://{{host}}/v1/rdb/rest/film' \
-H 'Authorization: Bearer <This is a fake token, please use yours>'

Response

HTTP/1.1 400 Bad Request

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

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

说明：

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

3. 删除数据后返回

Request

curl -i -X DELETE 'https://{{host}}/v1/rdb/rest/film?select=id,title,director,duration,release_year&id=eq.5' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

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

Body: 
[
    {
        "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 DELETE '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 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

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

Body: 
[
    {
        "director": "Christopher Nolan",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

该请求近似于下列 sql:

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

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