How to query using an IN clause and a `Vec` as parameter in Rust sqlx for MySQL?-CodePudding

Note: this is a similar but NOT duplicate question with How to use sqlx to query mysql IN a slice?. I'm asking for the Rust one.

This is what I try to do.

let v = vec![..];
sqlx::query("SELECT something FROM table WHERE column IN (?)").bind(v)
 ...

Then I got the following error

the trait bound `std::vec::Vec<u64>: sqlx::Encode<'_, _>` is not satisfied

CodePudding user response：

Answer is in first on FAQ https://github.com/launchbadge/sqlx/blob/master/FAQ.md

How can I do a SELECT ... WHERE foo IN (...) query? In 0.6 SQLx will support binding arrays as a comma-separated list for every database, but unfortunately there's no general solution for that currently in SQLx itself. You would need to manually generate the query, at which point it cannot be used with the macros.

CodePudding user response：

The error shows Vec is not an Encode that is required to be as a valid DB value. The Encode doc lists all the Rust types that have implemented the trait. Vec is not one.

You can use the following way to bind the parameters in IN with the values of a vector. Firstly, you need to expand the number of '?' in the IN expression to be the same number of the parameters. Then, you need to call bind to bind the values one by one.

let v = vec![1, 2];

let params = format!("?{}", ", ?".repeat(v.len()-1));
let query_str = format!("SELECT id FROM test_table WHERE id IN ( { } )", params);

let mut query = sqlx::query(&query_str);
for i in v {
    query = query.bind(i);
}

let row = query.fetch_all(&pool).await?;

Please note if the target database is not MySql, you need to use $n, like $1, $2, instead of ?, as the parameter placeholder.