如何在单条SQL语句中删除行并返回指定剩余行？

问题场景 #

需要执行删除指定帖子的操作，同时返回该作者剩余的所有帖子。分开执行两条SQL语句时功能正常，但合并成复合语句后，明明存在剩余数据却返回0行，且DELETE操作已确认生效。

原独立语句：

-- 删除语句
DELETE FROM posts 
WHERE rowid = ?

-- 查询剩余帖子语句
SELECT posts.rowid, circle, description, timestamp
FROM posts
WHERE author = ?
ORDER BY timestamp DESC

合并后的复合语句：

DELETE FROM posts 
WHERE rowid = ?;
SELECT posts.rowid, circle, description, timestamp
FROM posts
WHERE author = ?
ORDER BY timestamp DESC

测试数据：

row 1: circle="first circle", author="me@example.com", description="first", timestamp=<SQL生成的时间戳> 
row 2: circle="first circle", author="me@example.com", description="second", timestamp=<SQL生成的时间戳> 

问题根源 #

Go标准库database/sql的QueryContext方法默认仅处理第一个结果集。复合语句执行后，第一个结果集是DELETE操作的影响行数（无数据行），第二个才是SELECT的查询结果，但代码中直接读取第一个空结果集，因此返回空数组。

解决方案 #

方案1：手动切换结果集 #

在代码中先跳过DELETE对应的空结果集，再读取SELECT的结果：

func (cdb *CirclesDB) DeletePostAndReturnRemainingByAuthor(id int64, author string) ([]post.UserPost, error) {
    query := `
        DELETE FROM posts
        WHERE rowid = ?;
        SELECT posts.rowid, circle, description, timestamp
        FROM posts
        WHERE author = ?
        ORDER BY timestamp DESC
    `
    rows, err := cdb.query(query, id, author)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    // 跳过DELETE操作的结果集
    if !rows.NextResultSet() {
        if err := rows.Err(); err != nil {
            return nil, err
        }
    }

    var posts []post.UserPost
    for rows.Next() {
        var post post.UserPost
        if err := rows.Scan(&post.ID, &post.Circle, &post.Description, &post.TimeStamp); err != nil {
            return nil, err
        }
        posts = append(posts, post)
    }
    // 检查遍历过程中的错误
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return posts, nil
}

方案2：使用SQLite的WITH原子语句（推荐） #

如果使用SQLite 3.35.0及以上版本，可通过WITH语句实现原子性操作，且无需处理多结果集：

func (cdb *CirclesDB) DeletePostAndReturnRemainingByAuthor(id int64) ([]post.UserPost, error) {
    query := `
        WITH deleted_post AS (
            DELETE FROM posts WHERE rowid = ? RETURNING author
        )
        SELECT p.rowid, p.circle, p.description, p.timestamp
        FROM posts p
        JOIN deleted_post dp ON p.author = dp.author
        ORDER BY p.timestamp DESC
    `
    rows, err := cdb.query(query, id)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var posts []post.UserPost
    for rows.Next() {
        var post post.UserPost
        if err := rows.Scan(&post.ID, &post.Circle, &post.Description, &post.TimeStamp); err != nil {
            return nil, err
        }
        posts = append(posts, post)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return posts, nil
}

该方案通过DELETE ... RETURNING获取被删帖子的作者，再关联查询剩余帖子，既保证操作原子性，又无需额外传入author参数，避免参数不匹配问题。

内容的提问来源于stack exchange，提问作者boreddad420