sqlx实现增删改

587
2023/06/04 14:31:10

本章将讨论使用 sqlx 进行增删改(INSERT/DELETE/UPDATE)操作。

如第2章所讲,通过 query()execute(),可以执行非 SELECT 操作,并且它还可以返回最后插入的ID和受影响的行数。

判断是否存在

开始之前,我们需要定义一个函数,用于判断增加或修改的会员是否存在。在数据库中,我们将 name 字段定义成了 unique,所以只需要判断是否存在相同 name 的记录就行了。

// src/db/member.rs

pub async fn exists(conn: &sqlx::MySqlPool, name: &str, id: Option<u32>) -> Result<bool> {
    let sql = "SELECT COUNT(*) FROM member WHERE name=?";
    let with_id_sql: String;

    let q = match id {
        Some(id) => {
            with_id_sql = format!("{} AND id<>?", sql);
            sqlx::query_as(&with_id_sql).bind(&name).bind(id)
        }
        None => sqlx::query_as(sql).bind(&name),
    };

    let count: (i64,) = q.fetch_one(conn).await.map_err(Error::from)?;

    Ok(count.0 > 0)
}

原理很简单,就是通过 SELECT COUNT(*) 来统计指定 name 的记录数,如果这个记录大于0,说明该会员已经存在。

这里有个很奇怪的 id: Option<u32>,它是做什么用的?

  • 对于增加记录来说,只要判断是否存在指定 name的记录就行了
  • 对于修改记录来说,除了要判断是否存在指定 name 的记录,还要判断这个记录是否是它自己。如果不加这个条件,即使不做任何数据的修改,都无法进行操作,exists() 永远返回 true

增加

// src/db/member.rs

pub async fn add(conn: &sqlx::MySqlPool, m: &model::member::Member) -> Result<u32> {
    if exists(conn, &m.name, None).await? {
        return Err(Error::exists("同名的会员已存在"));
    }

    let id = sqlx::query(
        "INSERT INTO `member` (name, dateline, balance, types,is_del) VALUES(?,?,?,?,?)",
    )
    .bind(&m.name)
    .bind(&m.dateline)
    .bind(&m.balance)
    .bind(&m.types)
    .bind(&m.is_del)
    .execute(conn)
    .await
    .map_err(Error::from)?
    .last_insert_id();

    Ok(id as u32)
}

结合 query()execute(),我们执行了 INSERT 语句,并返回了它最后插入的ID。为什么返回的时候要转成 u32Ok(id as u32))呢?

  • 为了兼容 BIGINT UNSIGNEDlast_insert_id() 返回的是 u64
  • 虽然你可以直接使用 u64 ,但我们的表的主键是 INT UNSIGNED,对应的是 u32。为了统一性,我们还是将它转成了 u32

修改

// src/db/member.rs

pub async fn edit(conn: &sqlx::MySqlPool, m: &model::member::Member) -> Result<u64> {
    if exists(conn, &m.name, Some(m.id)).await? {
        return Err(Error::exists("同名的会员已存在"));
    }

    let aff = sqlx::query("UPDATE `member` SET name=?,balance=?,types=? WHERE id=?")
        .bind(&m.name)
        .bind(&m.balance)
        .bind(&m.types)
        .bind(&m.id)
        .execute(conn)
        .await
        .map_err(Error::from)?
        .rows_affected();

    Ok(aff)
}

结合 query()execute(),我们执行了 UPDATE 语句,并返回了受影响的行数。和 INSERT 返回的最后插入ID不同,受影响行数无须转成 u32

逻辑删除

物理删除

// src/db/member.rs

pub async fn real_del(conn: &sqlx::MySqlPool, id: u32) -> Result<u64> {
    let aff = sqlx::query("DELETE FROM member WHERE id=?")
        .bind(id)
        .execute(conn)
        .await
        .map_err(Error::from)?
        .rows_affected();
    Ok(aff)
}

本章代码位于04/增删改分支