使用Rust Diesel向SQLite批量插入并返回自增ID时类型检查失败

尝试使用最新版SQLite实现以下流程：插入ID列为空的行以自动生成自增ID，并通过INSERT语句的RETURNING子句返回这些自动生成的ID。SQLite 3.35+已支持RETURNING子句，Diesel也可通过returning_clauses_for_sqlite_3_35特性启用该支持，理论上该流程可行。

编写的代码如下：

Cargo.toml #

[dependencies]
diesel = { version = "2.1.3", features = ["sqlite", "returning_clauses_for_sqlite_3_35"] }

main.rs #

use diesel::prelude::*;
use diesel::sql_query;

table! {
    test_table (internal_id) {
        internal_id -> BigInt,
        content -> Text,
    }
}

#[derive(Debug, PartialEq, Clone, Identifiable, Selectable, Queryable, Insertable)]
#[diesel(primary_key(internal_id))]
#[diesel(table_name = test_table)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
pub struct TestTableRow {
    #[diesel(deserialize_as = i64)]
    pub internal_id: Option<i64>,
    pub content: String,
}

fn main() {
    let rows = vec![
        TestTableRow { internal_id: None, content: "Hello!".to_owned() },
        TestTableRow { internal_id: None, content: "World!".to_owned() },
    ];

    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    sql_query(r"
        CREATE TABLE test_table(
            internal_id INTEGER PRIMARY KEY AUTOINCREMENT,
            content     TEXT NOT NULL
        ) STRICT;
    ").execute(&mut conn).unwrap();
    let internal_ids: Vec<i64> = diesel::insert_into(test_table::table)
        .values(&rows)
        .returning(test_table::columns::internal_id)
        .get_results(&mut conn)
        .unwrap();

    assert_eq!(internal_ids, vec![1, 2]);
}

但这段代码无法通过类型检查，错误信息如下：

error[E0277]: the trait bound `BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<columns::internal_id, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<columns::content, expression::bound::Bound<diesel::sql_types::Text, &String>>>, test_table::table>>, test_table::table, (), false>: QueryFragment<Sqlite, sqlite::backend::SqliteBatchInsert>` is not satisfied
    --> src/main.rs:37:22
     |
37   |         .get_results(&mut conn)
     |          ----------- ^^^^^^^^^ the trait `QueryFragment<Sqlite, sqlite::backend::SqliteBatchInsert>` is not implemented for `BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<columns::internal_id, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<columns::content, expression::bound::Bound<diesel::sql_types::Text, &String>>>, test_table::table>>, test_table::table, (), false>`
     |          |
     |          required by a bound introduced by this call
     |
     = help: the following other types implement trait `QueryFragment<DB, SP>`:
               <BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<V, Tab>>, Tab, QId, HAS_STATIC_QUERY_ID> as QueryFragment<DB, PostgresLikeBatchInsertSupport>>
               <BatchInsert<V, Tab, QId, HAS_STATIC_QUERY_ID> as QueryFragment<DB>>
     = note: required for `BatchInsert<Vec<ValuesClause<(DefaultableColumnInsertValue<...>, ...), ...>>, ..., ..., false>` to implement `QueryFragment<Sqlite>`
     = note: the full type name has been written to '/Users/fs/code/rust-diesel-issue/target/debug/deps/rust_diesel_issue-c1c37efb3df7ba71.long-type-1487086329238728488.txt'
     = note: 1 redundant requirement hidden
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ...), ...>>, ..., ..., false>, ..., ...>` to implement `QueryFragment<Sqlite>`
     = note: the full type name has been written to '/Users/fs/code/rust-diesel-issue/target/debug/deps/rust_diesel_issue-c1c37efb3df7ba71.long-type-8243152461613863375.txt'
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ...), ...>>, ..., ..., false>, ..., ...>` to implement `LoadQuery<'_, diesel::SqliteConnection, _>`
     = note: the full type name has been written to '/Users/fs/code/rust-diesel-issue/target/debug/deps/rust_diesel_issue-c1c37efb3df7ba71.long-type-8243152461613863375.txt'
note: required by a bound in `get_results`
    --> /Users/fs/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.1.4/src/query_dsl/mod.rs:1739:15
     |
1737 |     fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
     |        ----------- required by a bound in this associated function
1738 |     where
1739 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::get_results`

仅插入单行数据时，代码可正常编译运行：

let internal_ids: Vec<i64> = diesel::insert_into(test_table::table)
    .values(&rows[0])                               // <--- 此处修改！
    .returning(test_table::columns::internal_id)
    .get_results(&mut conn)
    .unwrap();

问题原因 #

Diesel的BatchInsert组件目前仅为PostgreSQL等兼容Postgres批量插入语法的数据库实现了QueryFragment<DB, PostgresLikeBatchInsertSupport> trait，对SQLite的批量插入+RETURNING子句组合没有提供支持，因此批量插入时会触发类型检查错误。而单行插入不受此限制，因为Diesel对SQLite的单行INSERT+RETURNING支持是完整的。

解决方案 #

有两种可行的解决方式：

方式一：循环插入单行

对每个待插入的行单独执行插入操作，收集返回的自增ID。这种方式实现简单，无需额外处理参数，适合数据量不大的场景：

let mut internal_ids = Vec::new();
for row in rows {
    let id: i64 = diesel::insert_into(test_table::table)
        .values(row)
        .returning(test_table::columns::internal_id)
        .get_result(&mut conn)
        .unwrap();
    internal_ids.push(id);
}

方式二：使用原始SQL执行批量插入

直接构造支持RETURNING子句的批量INSERT原始SQL，通过Diesel的sql_query执行并绑定参数，既保证性能又避免SQL注入风险：

// 构造批量插入的占位符
let placeholders = rows.iter()
    .map(|_| "(NULL, ?)")
    .collect::<Vec<_>>()
    .join(", ");
// 拼接完整SQL语句
let sql = format!(
    "INSERT INTO test_table (internal_id, content) VALUES {} RETURNING internal_id",
    placeholders
);
// 提取需要绑定的content参数
let contents: Vec<&String> = rows.iter().map(|r| &r.content).collect();
// 执行查询并获取返回的ID
let internal_ids: Vec<i64> = sql_query(&sql)
    .bind::<Text, _>(contents)
    .get_results(&mut conn)
    .unwrap();

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