使用sqlx的QueryBuilder构建复杂、动态的查询

本章我们讨论如何优雅方便地使用 sqlx 构建复杂的、动态的 SQL。sqlx 提供了 QueryBuilder 结构体，它可以方便地实现 SQL 的构建。

QueryBuilder 常用方法

• push()：追加 SQL 语句
• push_bind()：追加绑定参数
• build()：构建最终的查询。结果和 query() 类似。
• build_query_as()：构建最终的查询，结果和 query_as() 类似。

*十分重要：和 query()/query_as()不同，QueryBuilder的push()方法的 SQL 语句里，*不需要使用 ? 占位符：

// query_as
sqlx::query_as("SELECT * FROM member WHERE id=? AND is_del=?").bind(id).bind(false);

// QueryBuild
let mut q = QueryBuilder::new("SELECT * FROM member WHERE id="); // 不用占位符！
q.push_bind(id)
.push(" AND is_del=") // 不用占位符！
.push_bind(false);

改造前：

pub async fn list(
    conn: &sqlx::MySqlPool,
    page: u32,
) -> Result<Paginate<Vec<model::member::Member>>> {
    let count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM member")
        .fetch_one(conn)
        .await
        .map_err(Error::from)?;

    let sql = format!(
        "SELECT * FROM member ORDER BY id DESC LIMIT {} OFFSET {}",
        DEFAULT_PAGE_SIZE,
        page * DEFAULT_PAGE_SIZE
    );
    let data = sqlx::query_as(&sql)
        .fetch_all(conn)
        .await
        .map_err(Error::from)?;

    Ok(Paginate::new(count.0 as u32, page, data))
}

pub async fn list(
    conn: &sqlx::MySqlPool,
    page: u32,
) -> Result<Paginate<Vec<model::member::Member>>> {
    let count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM member")
        .fetch_one(conn)
        .await
        .map_err(Error::from)?;

    let mut q = sqlx::QueryBuilder::new("SELECT * FROM member ORDER BY id DESC");
    q.push(" LIMIT ")
        .push_bind(DEFAULT_PAGE_SIZE)
        .push(" OFFSET ")
        .push_bind(page * DEFAULT_PAGE_SIZE);

    let data = q
        .build_query_as()
        .fetch_all(conn)
        .await
        .map_err(Error::from)?;

    Ok(Paginate::new(count.0 as u32, page, data))
}

我们使用 QueryBuilder 代替了之前使用 format! 拼接 SQL 的做法。

使用 QueryBuilder 改造 exists()

改造前：

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)
}

pub async fn exists(conn: &sqlx::MySqlPool, name: &str, id: Option<u32>) -> Result<bool> {
    let mut q = sqlx::QueryBuilder::new("SELECT COUNT(*) FROM member WHERE name=");
    q.push_bind(name);

    if let Some(id) = id {
        q.push(" AND id<>").push_bind(id);
    };

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

    Ok(count.0 > 0)
}

再次提醒：*和 query()/query_as()不同，QueryBuilder的push()方法的 SQL 语句里，*不需要使用 ? 占位符