How to use Sqlx for batch-insert or dynamic query of mysql

The sqlx insert cannot accept VEC type parameters, so I cannot use the syntax <insert into my_tb (id, name) values ("a", "a"), ("b", "b")>.
If do not use Macros and use sql to splice it into a string, it is easy to generate "" SQL Injection Attack "

I'm working on a little query builder to do this. It's simple, but it works for my current codebase.

https://play.rust-lang.org/?version=stable&mode=debug&edition=2018&gist=ad7391366c8408d8cb0097f1f867808f

1 Like

Edit: this probably won't be too helpful, since you're using MySQL. My apologies.

This is a general limitation of SQL prepared queries - the number of parameters is fixed. Even if you generate the query string dynamically, like your code @picklenerd, it'll still result in suboptimal performance since the database engine would have to recompile each different length of query as a separate query.

@liergou99, what database are you targeting, and roughly how many things do you plan on inserting?

The database matters because there are no standard techniques for doing this. Each database has their own methods for this kind of bulk inserting.

For instance, Postgres allows you to do

INSERT INTO my_tb (id, name)
SELECT id, name
FROM UNNEST($1, $2)

and you can pass in Vec<String> or &[String] for each parameter to supply the values for the first column and for the second column.

How many things you insert is also relevant, since if you're only inserting a small number, using the more complicated techniques is probably overkill. If you don't plan on inserting more than 10 at a time, and it's run infrequently, I would recommend just executing a single insert statement multiple times, or using an interface like @picklenerd's to build up simple prepared queries which are still safe from SQL injection because they use parameters.

Edit: I'm sorry, I missed that the title mentioned MySQL.

While I'm familiar with Transact-SQL and PostgreSQL to an extent, I don't know enough MySQL to know a good way of doing this.

1 Like