mysql - 如何在 sqlx 中构建和提交多查询事务？

Question

在 sqlx有一个 Transaction type 允许您在一个事务中运行多个查询。
我试图弄清楚如何做到这一点，遗憾的是没有记录，尽管有自动生成的 API 文档。
我的第一次尝试:

async fn insert_user() {
    let pool: sqlx::Pool<sqlx::MySql> =
        futures::executor::block_on(crate::db::open_mariadb_pool()).unwrap();
    use sqlx::Acquire;
    let mut conn = pool.acquire().await.unwrap();
    let tx = conn.begin().await.unwrap();
    let insert_query = sqlx::query("INSERT INTO user (email, email_verification_secret, email_verified, password_hash, hourly_rate)
VALUES (?, ?, ?, ?, ?);"
    )
        .bind("text@example.com")
        .bind(false)
        .bind(123)
        .bind("pwhash")
        .bind(20);
    let get_row_query = sqlx::query::<sqlx::MySql>("SELECT * FROM user WHERE id = LAST_INSERT_ID();");
    insert_query.execute(tx);
    get_row_query.execute(tx);
    tx.commit();
}

产生以下错误:

error[E0277]: the trait bound `Transaction<'_, MySql>: Executor<'_>` is not satisfied
  --> src/controller_user.rs:86:26
   |
86 |     insert_query.execute(tx);
   |                          ^^ the trait `Executor<'_>` is not implemented for `Transaction<'_, MySql>`
   |
   = help: the following implementations were found:
             <&'t mut Transaction<'c, MySql> as Executor<'t>>

error[E0277]: the trait bound `Transaction<'_, MySql>: Executor<'_>` is not satisfied
  --> src/controller_user.rs:87:27
   |
87 |     get_row_query.execute(tx);
   |                           ^^ the trait `Executor<'_>` is not implemented for `Transaction<'_, MySql>`
   |
   = help: the following implementations were found:
             <&'t mut Transaction<'c, MySql> as Executor<'t>>

我真的不知道从哪里开始考虑这个 - 但我没能从自动生成的 API 文档中找到答案。

Answer 1

在函数 tests/mssql/mssql.rs 中的测试 it_can_work_with_transactions 中有一个示例用法。
用法似乎是:

let mut tx = conn.begin().await?;

sqlx::query("INSERT INTO _sqlx_users_1922 (id) VALUES (@p1)")
    .bind(10_i32)
    .execute(&mut tx)
    .await?;

tx.commit().await?;

您正在执行的操作与此代码之间唯一明显的区别是传递可变引用，而不是将值作为参数传递给 execute 。
考虑到这一点，如果我们仔细查看您的错误消息，那就是它所说的。用 ... 替换一些位后只留下它读取的要点

... the trait `Executor<...>` is not implemented for `Transaction<...>`
   |
... the following implementations were found:
             &mut Transaction<...> as Executor<...>